Закрепить диапазон в формуле в excel

Копирование и вставка диапазона

Это тоже одна из достаточно частых операций, которую пользователи Excel осуществляют с диапазонами.

Чтобы это сделать, нужно выделить диапазон, сделать по нему правый клик мыши и нажать «Копировать». Также можно воспользоваться комбинацией клавиш Ctrl + C.

13

Также можно найти специальную кнопку на вкладке «Главная» в группе «Буфер обмена». 

Следующий этап – вставить нужную информацию в другое место. Для этого необходимо найти ячейку, которая будет служить левым верхним углом диапазона, после чего аналогично вызвать контекстное меню, но при этом найти пункт «Вставить». Также можно воспользоваться стандартной комбинацией Ctrl + V, которая работает абсолютно в любых программах.

14

Задача

Имеется таблица продаж по месяцам некоторых товаров (см. Файл примера ):

Необходимо найти сумму продаж товаров в определенном месяце. Пользователь должен иметь возможность выбрать нужный ему месяц и получить итоговую сумму продаж. Выбор месяца пользователь должен осуществлять с помощью Выпадающего списка .

Для решения задачи нам потребуется сформировать два динамических диапазона : один для Выпадающего списка , содержащего месяцы; другой для диапазона суммирования.

Для формирования динамических диапазонов будем использовать функцию СМЕЩ() , которая возвращает ссылку на диапазон в зависимости от значения заданных аргументов. Можно задавать высоту и ширину диапазона, а также смещение по строкам и столбцам.

Создадим динамический диапазон для Выпадающего списка , содержащего месяцы. С одной стороны нужно учитывать тот факт, что пользователь может добавлять продажи за следующие после апреля месяцы (май, июнь…), с другой стороны Выпадающий список не должен содержать пустые строки. Динамический диапазон как раз и служит для решения такой задачи.

Для создания динамического диапазона:

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Месяц </em>;
• в поле Область выберите лист Книга </em>;
• в поле Диапазон введите формулу =СМЕЩ(лист1!$B$5;;;1;СЧЁТЗ(лист1!$B$5:$I$5))
• нажмите ОК.

Теперь подробнее. Любой диапазон в EXCEL задается координатами верхней левой и нижней правой ячейки диапазона. Исходной ячейкой, от которой отсчитывается положение нашего динамического диапазона, является ячейка B5 . Если не заданы аргументы функции СМЕЩ() смещ_по_строкам, смещ_по_столбцам (как в нашем случае), то эта ячейка является левой верхней ячейкой диапазона. Нижняя правая ячейка диапазона определяется аргументами высота и ширина . В нашем случае значение высоты =1, а значение ширины диапазона равно результату вычисления формулы СЧЁТЗ(лист1!$B$5:$I$5) , т.е. 4 (в строке 5 присутствуют 4 месяца с января по апрель ). Итак, адрес нижней правой ячейки нашего динамического диапазона определен – это E 5 .

При заполнении таблицы данными о продажах за май , июнь и т.д., формула СЧЁТЗ(лист1!$B$5:$I$5) будет возвращать число заполненных ячеек (количество названий месяцев) и соответственно определять новую ширину динамического диапазона, который в свою очередь будет формировать Выпадающий список .

ВНИМАНИЕ! При использовании функции СЧЕТЗ() необходимо убедиться в отсутствии пустых ячеек! Т.е. нужно заполнять перечень месяцев без пропусков

Теперь создадим еще один динамический диапазон для суммирования продаж.

Для создания динамического диапазона :

• на вкладке Формулы в группе Определенные имена выберите команду Присвоить имя </em>;
• в поле Имя введите: Продажи_за_месяц </em>;
• в поле Диапазон введите формулу = СМЕЩ(лист1!$A$6;;ПОИСКПОЗ(лист1!$C$1;лист1!$B$5:$I$5;0);12)
• нажмите ОК.

Функция ПОИСКПОЗ() ищет в строке 5 (перечень месяцев) выбранный пользователем месяц (ячейка С1 с выпадающим списком) и возвращает соответствующий номер позиции в диапазоне поиска (названия месяцев должны быть уникальны, т.е. этот пример не годится для нескольких лет). На это число столбцов смещается левый верхний угол нашего динамического диапазона (от ячейки А6 ), высота диапазона не меняется и всегда равна 12 (при желании ее также можно сделать также динамической – зависящей от количества товаров в диапазоне).

И наконец, записав в ячейке С2 формулу = СУММ(Продажи_за_месяц) получим сумму продаж в выбранном месяце.

Например, в мае.

Или, например, в апреле.

Примечание: Вместо формулы с функцией СМЕЩ() для подсчета заполненных месяцев можно использовать формулу с функцией ИНДЕКС() : = $B$5:ИНДЕКС(B5:I5;СЧЁТЗ($B$5:$I$5))

Формула подсчитывает количество элементов в строке 5 (функция СЧЁТЗ() ) и определяет ссылку на последний элемент в строке (функция ИНДЕКС() ), тем самым возвращает ссылку на диапазон B5:E5 .

Основные операции с ячейками

Выделение ячеек в один диапазон

Группировка информации в Excel осуществляется по специальному диапазону. В этом случае выделяется сразу несколько ячеек, а также, соответственно, строк и колонок. Если выделить их, отображается целая область, а в строке адреса приводится обобщенная информация по всем выделенным ячейкам.

Объединение ячеек

После того, как ячейки были выделены, теперь их можно объединять. Рекомендуется перед тем, как это делать, скопировать выделенный диапазон путем нажатия комбинации клавиш Ctrl+C и перенести в другое место с помощью клавиш Ctrl+V. Таким образом можно сохранить резервную копию данных. Это обязательно надо делать, поскольку при объединении ячеек вся содержащаяся в них информация стирается. И чтобы ее восстановить, необходимо иметь ее копию.

Далее необходимо нажать на кнопку, показанную на скриншоте. Там перечислено несколько способов объединения ячеек. Вам нужно выбрать тот, который больше всего подходит по ситуации.

Поиску требуемой кнопки. В навигационном меню нужно на вкладке «Главная» найти кнопку, которая была отмечена на предыдущем скриншоте, и отобразить выпадающий список. Мы выбрали пункт «Объединить и поместить в центре». Если эта кнопка неактивна, то нужно выйти из режима редактирования. Это можно сделать путем нажатия клавиши «Ввод». 

Если нужно скорректировать положение текста в получившейся большой ячейке, это можно сделать с помощью свойств выравнивания, которые находятся на вкладке «Главная».

Разделение ячеек

Это довольно простая процедура, которая в чем-то повторяет предыдущий пункт:

1. Выбор ячейки, которая раньше была создана в результате объединения нескольких других ячеек. Разделение других не представляется возможным. 
2. После того, как будет выделен объединенный блок, клавиша объединения загорится. После того, как по ней кликнуть, все ячейки будут разделены. Каждая из них получит свой собственный адрес. Пересчет строк и столбцов произойдет автоматически. 

Поиск ячейки

Очень легко не заметить важную информацию, когда приходится работать с большими объемами данных. Чтобы решить эту проблему, можно воспользоваться поиском. Причем искать можно не только слова, но и формулы, объединенные блоки и все, что угодно. Для этого необходимо выполнить следующие действия:

1. Убедиться, что открыта вкладка «Главная». Там есть область «Редактирование», где можно найти клавишу «Найти и выделить».
2. После этого откроется диалоговое окно с полем ввода, в который можно ввести то значение, которое надо. Также там есть возможность указать дополнительные параметры. Например, если нужно найти объединенные ячейки, необходимо нажать на «Параметры» – «Формат» – «Выравнивание», и поставить флажок возле поиска объединенных ячеек.
3. В специальном окошке будет выводиться необходимая информация.

Также есть функция «Найти все», чтобы осуществить поиск всех объединенных ячеек.

Извлечение уникальных значений с помощью Duplicate Remover.

В заключительной части этого руководства я покажу вам интересное решение для поиска и извлечения различных и уникальных значений в таблицах Excel. Это решение сочетает в себе универсальность формул Excel и простоту расширенного фильтра. Кроме того, здесь есть несколько уникальных функций:

• Найти и извлечь уникальные или различные значения на основе записей в одном или нескольких столбцах.
• Найти, выделить и скопировать уникальные значения в любое другое место в той же или другой книге Excel.

А теперь давайте посмотрим, как работает инструмент Duplicate Remover.

Предположим, у вас есть большая таблица, созданная путем объединения данных из нескольких других таблиц. Очевидно, что она содержит много повторяющихся строк, и ваша задача состоит в том, чтобы извлечь уникальные строки, которые появляются в таблице только один раз, или различные строки, включая уникальные и первые повторяющиеся вхождения. В любом случае, с надстройкой Duplicate Remover работа выполняется за несколько шагов.

1. Выберите любую ячейку в исходной таблице и нажмите кнопку DuplicateRemover на вкладке AblebitsData в группе Dedupe.

Мастер Duplicate Remover запустится и выберет всю таблицу. Итак, просто нажмите « Далее», чтобы перейти к следующему шагу.

1. Выберите тип значения, который вы хотите найти, и нажмите Далее :
   • Уникальные
   • Уникальные + 1е вхождения (различные)

В этом примере мы хотим извлечь различные строки, которые появляются в исходной таблице хотя бы один раз, поэтому мы выбираем опцию Unique + 1st occurences:

1. Выберите один или несколько столбцов для проверки уникальных значений.

В этом примере мы хотим убрать все повторяющиеся значения на основе значений в 2 столбцах ( заказчик и товар), поэтому мы выбираем только нужные нам столбцы.

В нашем случае таблица имеет заголовок, поэтому отмечаем птичкой пункт My table has headers.

Думаю, нам не нужны пустые строки, которые могут случайно встретиться при объединении данных из разных таблиц. Поэтому отмечаем такжеSkip empty cells.

Если вдруг в наших записях случайно появились лишние пробелы, то, думаю, стоит их игнорировать. Поэтому отмечаем также Ignore extra spaces.

Также наш поиск буден нечувствителен к регистру, то есть не будем при сравнении данных различать прописные и строчные буквы. Поэтому  не трогаем опцию Case-sensitive match.

1. Выберите действие, которое нужно выполнить с найденными значениями. Вам доступны следующие варианты:
   • Выделить цветом.
   • Выбрать и выделить.
   • Отметить в столбце статуса.
   • Копировать в другое место.

Чтобы не менять исходные данные, выберите «Копировать в другое место» (Copy to another location), а затем укажите, где именно вы хотите видеть новую таблицу – на этом же листе (выберите параметр «Custom Location» и укажите верхнюю ячейку целевого диапазона), на новом листе (New worksheet) или в новой книге (New workbook).

В этом примере давайте выберем новый лист:

1. Нажмите кнопку « Готово» , и все готово!

В итоге у нас осталось всего 20 записей.

Понравился этот быстрый и простой способ получить список уникальных значений или записей в Excel? Если да, то я рекомендую вам загрузить полнофункциональную ознакомительную версию Ultimate Suite и попробовать в работе Duplicate Remover.

В Ultimate Suite for Excel также включено много других полезных инструментов, которые помогут вам сэкономить много времени. Мы о них также будем подробно рассказывать в других материалах на сайте.

Как выделить все ячейки, на которые ссылается формула в Excel?

В этой статье говорится о выделении всех ячеек, на которые ссылается формула в Excel. Пожалуйста, следуйте двум нижеприведенным методам, чтобы это сделать.

Выделите все ячейки, на которые ссылается формула, с помощью сочетания клавиш

Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!

Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!

В этом разделе будет рекомендовано использовать горячую клавишу для выбора всех ячеек, на которые ссылается формула, а затем вручную заполнить их цветом.

1. Предположим, что E1 — это ячейка формулы, и вам нужно выделить все ячейки, на которые она ссылается. Выберите ячейку формулы E1, затем нажмите Ctrl + (открытая квадратная скобка) одновременно.

2. Теперь выделены ячейки, на которые ссылается эта формула. Укажите цвет заливки, чтобы выделить их. Смотрите скриншот:

Выделите все ячейки, на которые ссылается формула, с кодом VBA

Вы также можете запустить код VBA, чтобы выделить все ячейки, на которые ссылалась формула в Excel.

1. Нажмите другой + F11 , чтобы открыть Microsoft Visual Basic для приложений окно.

2. в Microsoft Visual Basic для приложений окно, пожалуйста, нажмите Вставить > Модуль. Затем скопируйте и вставьте приведенный ниже код VBA в окно модуля.

Код VBA: выделите все ячейки, на которые ссылается формула в Excel

Sub HighlightCellsReferenced()
    Dim rowCnt As Integer
    Dim i As Integer, j As Integer, strleng As Integer
    Dim strTxt As String, strFml As String
    Dim columnStr, cellsAddress As String
    Dim xRg As Range, yRg As Range
    On Error Resume Next
    Set xRg = Application.InputBox(Prompt:="Please select formula cell(s)...", _
    Title:="Kutools For Excel", Type:=8)
    
    strTxt = ""
    Application.ScreenUpdating = False
    For Each yRg In xRg
        If yRg.Value <> "" Then
            strFml = yRg.Formula + " "
            strFml = Replace(strFml, "(", " ")
            strFml = Replace(strFml, ")", " ")
            strFml = Replace(strFml, "-", " ")
            strFml = Replace(strFml, "+", " ")
            strFml = Replace(strFml, "*", " ")
            strFml = Replace(strFml, "/", " ")
            strFml = Replace(strFml, "=", " ")
            strFml = Replace(strFml, ",", " ")
            strFml = Replace(strFml, ":", " ")
              
            For j = 1 To Len(strFml)
                If Mid(strFml, j, 1) <> " " Then
                    cellsAddress = cellsAddress + Mid(strFml, j, 1)
                Else
                    On Error Resume Next
                    Range(cellsAddress).Interior.ColorIndex = 3
                    cellsAddress = ""
                End If
            Next
        End If
    Next yRg
    Application.ScreenUpdating = True
End Sub

3. нажмите F5 ключ для запуска кода. В всплывающем Kutools for Excel В диалоговом окне выберите ячейку формулы или несколько ячеек формулы, на основе которых необходимо выделить ячейки, на которые ссылаются, а затем щелкните значок OK кнопка. Смотрите скриншот:

Теперь все ячейки, на которые есть ссылки, основанные на выбранных ячейках формулы, сразу же выделяются красным цветом, как показано ниже.

Надстройка для работы с диапазонами в Excel

Кнопки надстройки расположены на отдельной вкладке ленты Excel, каждая кнопка вызывает пользовательскую форму (диалоговое окно), при помощи которой процедурам VBA задаются начальные условия. Надстройка позволяет осуществлять различные действия с диапазонами ячеек, такие как: копирование диапазонов из определенных листов различных рабочих книг и вставка их на итоговый лист, сравнение двух диапазонов, вставка заданного диапазона в определенные листы различных рабочих книг, производить математические действия между значениями ячеек диапазона и заданным числом, изменение размеров диапазона, применение текстовых функций к текстовым значениям ячеек диапазона, копирование и вставка диапазонов ячеек со всех листов книги на итоговый лист.

Выделение отдельных ячеек или диапазонов

Вы можете быстро найти и выделить определенные ячейки или диапазоны, введя их имена или ссылки на ячейки в поле имя , которое расположено слева от строки формул.

Для выбора именованных и неименованных ячеек или диапазонов можно также использовать команду Перейти к ( F5 или CTRL + G).

Важно: Для выбора именованных ячеек и диапазонов необходимо сначала определить их. Дополнительные сведения см

в статье Определение и использование имен в формулах.

Чтобы выбрать именованную ячейку или диапазон, щелкните стрелку рядом с полем имя , чтобы отобразить список именованных ячеек или диапазонов, а затем выберите нужное имя.

Чтобы выделить несколько именованных диапазонов или ссылок на ячейки, щелкните стрелку рядом с полем Имя и выберите имя первого диапазона или первой ссылки на ячейку из тех, которые нужно выделить. Затем, удерживая нажатой клавишу CTRL , щелкните имена других ячеек или диапазонов в поле имя .

Чтобы выделить неименованную ссылку на ячейку или диапазон, введите ссылку на ячейку или диапазон ячеек, который нужно выделить, а затем нажмите клавишу Ввод. Например, введите B3, чтобы выделить эту ячейку, или введите B1: B3, чтобы выделить диапазон ячеек.

Примечание: Вы не можете удалить или изменить имена, определенные для ячеек или диапазонов в поле имя . Имена можно удалять и изменять только в диспетчере имен (вкладка ” формулы “, Группа ” определенные имена “). Дополнительные сведения см. в статье Определение и использование имен в формулах.

Нажмите клавишу F5 или CTRL + G , чтобы открыть диалоговое окно “перейти “.

В списке Перейти щелкните имя ячейки или диапазона, который вы хотите выделить, или введите ссылку на ячейку в поле ссылка , а затем нажмите кнопку ОК.

Например, в поле ссылка введите B3 , чтобы выделить эту ячейку, или введите B1: B3 , чтобы выделить диапазон ячеек. Чтобы выделить несколько ячеек или диапазонов, введите их в поле ссылки , разделенные запятыми. Если вы ссылаетесь на диапазон с сбросом, созданный с помощью динамической формулы массива, вы можете добавить оператор Range. Например, если в ячейках A1: A4 есть массив, вы можете выбрать его, введя a1 # в поле ссылка , а затем нажать кнопку ОК.

Совет: Чтобы быстро найти и выделить все ячейки с данными определенного типа (например, с формулами) или только ячейки, соответствующие определенным условиям (например, видимые ячейки или последнюю ячейку листа, содержащую данные или форматирование), в контекстном меню выберите пункт выделить , а затем выберите нужный параметр.

Перейдите в раздел формулы > определенные имена > Диспетчер имен.

Выберите имя, которое вы хотите изменить или удалить.

Выберите команду изменить или Удалить.

Удерживая нажатой клавишу CTRL , щелкните каждую ячейку или диапазон ячеек, которые вы хотите включить, в левой части экрана. Если вы выберете этот параметр, вы можете щелкнуть ненужную ячейку, чтобы отменить ее выделение.

Если вы выбрали данные для карт Bing, убедитесь, что они находятся в названиях городов, названий стран и т. д. В противном случае Bing не будет сопоставляться.

Чтобы выделить ячейки, вы можете ввести ссылку в диалоговом окне Выбор данных или развернуть стрелку в диалоговом окне и выделить ячейки, щелкнув их. Дополнительные параметры выбора нужных ячеек описаны в предыдущем разделе.

Если вы выберете данные для преобразования в евро, убедитесь, что они являются данными денежных единиц.

Чтобы выделить ячейки, введите ссылку в поле исходные данные или щелкните стрелку рядом с полем и выделите ячейки, щелкнув их. Дополнительные параметры выбора нужных ячеек описаны в предыдущем разделе.

Вы можете выделить смежные ячейки в Excel в Интернете, щелкнув ячейку и перетащив ее, чтобы увеличить диапазон. Однако вы не можете выделить отдельные ячейки или диапазон, если они не находятся рядом друг с другом. Если у вас есть классическое приложение Excel, вы можете открыть книгу в Excel и выделить несмежные ячейки, щелкнув их, удерживая нажатой клавишу CTRL . Дополнительные сведения можно найти в разделе выделение конкретных ячеек или диапазонов в Excel.

Как выделить цветом ячейку в Excel по условию?

Теперь оранжевым цветом выделим те суммы магазинов, которые в текущем году меньше чем в прошлом и с отрицательной прибылью. Создадим второе правило для этого же диапазона D2:D12:

1. Не снимая выделения с диапазона D2:D12 снова выберите инструмент «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило».
2. Так же в появившемся окне «Создание правила форматирования» выберите опцию «Использовать формулу для определения форматированных ячеек».
3. В поле ввода введите формулу:
4. Нажмите на кнопку «Формат» и в появившемся окне «Формат ячеек» на вкладке «Заливка» выберите оранжевый цвет. На всех открытых окнах жмем ОК.

Мы видим, что получили не совсем ожидаемый результат, так как созданное новое правило всегда имеет высший приоритет по сравнению со старыми правилами условного форматирования в Excel. Необходимо снизить приоритет для нового правила. Чтобы проанализировать данную особенность наглядно и настроить соответствующим образом необходимо выбрать инструмент: ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Управление правилами».

Выберите новое оранжевое правило в появившемся окне «Диспетчер правил условного форматирования» и нажмите на кнопку «Вниз» (CTRL+стрелка вниз), как показано на рисунке:

Как видите последовательность правил очень важна если их много присвоено для одного и того же диапазона ячеек:

На первый взгляд может показаться что несколько правил могут форматировать одну и туже ячейку одновременно. В принципе это так, но при определенном условии, что все правила будут использовать разные типы форматирования. Например, правило 1 – изменяет шрифт, 2 – меняет заливку, 3 – добавляет границу, 4 – узор и т.д. Но если после выполнения любого правила, когда его условие выполнено, было проверено следующее правило для данной ячейки, тогда следует в окне диспетчера отметить галочкой в колонке «Остановить если истина»:

И наконец добавим третье правило для выделения цветом ячеек сумм магазинов, где положительная прибыль и больше чем в прошлом году. Введите новую формулу:

0;D2>C2)’ >

Этим ячейкам будет присвоен зеленый цвет и жмем везде ОК.

Примечание. В формуле можно использовать любые ссылки для текущего листа. В версии Excel 2010 можно ссылаться и на другие листы. А в Excel 2007 к другим листам можно обращаться только через имена диапазонов. Мы рекомендуем во всех версиях Excel ссылаться на другие листы через имена, так как это позволяет избежать множество ошибок при создании пользовательских правил для условного форматирования.

Заполнение пустых ячеек значениями из соседних ячеек

Как известно, для полноценной работы с данными (фильтрации, сортировки, подведения итогов и т.д.) нужен непрерывный список, т.е. таблица без разрывов (пустых строк и ячеек – по возможности). На практике же часто мы имеем как раз таблицы с пропущенными пустыми ячейками – например после копирования результатов сводных таблиц или выгрузок в Excel из внешних программ. Таким образом, возникает необходимость заполнить пустые ячейки таблицы значениями из верхних ячеек, то бишь.

из

сделать

В общем случае, может возникнуть необходимость делать такое заполнение не только вниз, но и вверх, вправо и т.д. Давайте рассмотрим несколько способов реализовать такое.

Способ 1. Без макросов

Выделяем диапазон ячеек в первом столбце, который надо заполнить (в нашем примере, это A1:A12).

Нажимаем клавишу F5 и затем кнопку Выделить (Special) и в появившемся окне выбираем Выделить пустые ячейки (Blanks) :

Не снимая выделения, вводим в первую ячейку знак “равно” и щелкаем по предыдущей ячейке или жмём стрелку вверх (т.е. создаем ссылку на предыдущую ячейку, другими словами):

И, наконец, чтобы ввести эту формулу сразу во все выделенные (пустые) ячейки нажимаем Ctrl + Enter вместо обычного Enter . И все! Просто и красиво.

В качестве завершающего мазка я советовал бы заменить все созданные формулы на значения, ибо при сортировке или добавлении/удалении строк корректность формул может быть нарушена. Выделите все ячейки в первом столбце, скопируйте и тут же вставьте обратно с помощью Специальной вставки (Paste Special) в контекстом меню, выбрав параметр Значения (Values) . Так будет совсем хорошо.

Способ 2. Заполнение пустых ячеек макросом

Если подобную операцию вам приходится делать часто, то имеем смысл сделать для неё отдельный макрос, чтобы не повторять всю вышеперечисленную цепочку действий вручную. Для этого жмём Alt + F11 или кнопку Visual Basic на вкладке Разработчик (Developer) , чтобы открыть редактор VBA, затем вставляем туда новый пустой модуль через меню Insert – Module и копируем или вводим туда вот такой короткий код:

Как легко можно сообразить, этот макрос проходит в цикле по всем выделенным ячейкам и, если они не пустые, заполняет их значениями из предыдущей ячейки.

Для удобства, можно назначить этому макросу сочетание клавиш или даже поместить его в Личную Книгу Макросов (Personal Macro Workbook), чтобы этот макрос был доступен при работе в любом вашем файле Excel.

Способ 3. Power Query

Power Query – это очень мощная бесплатная надстройка для Excel от Microsoft, которая может делать с данными почти всё, что угодно – в том числе, легко может решить и нашу задачу по заполнению пустых ячеек в таблице. У этого способа два основных преимущества:

• Если данных много, то ручной способ с формулами или макросы могут заметно тормозить. Power Query сделает всё гораздо шустрее.
• При изменении исходных данных достаточно будет просто обновить запрос Power Query. В случае использования первых двух способов – всё делать заново.

Для загрузки нашего диапазона с данными в Power Query ему нужно либо дать имя (через вкладку Формулы – Диспетчер имен), либо превратить в “умную” таблицу командой Главная – Форматировать как таблицу (Home – Format as Table ) или сочетанием клавиш Ctrl + T :

После этого на вкладке Данные (Data) нажмем на кнопку Из таблицы / диапазона (From Table/Range) . Если у вас Excel 2010-2013 и Power Query установлена как отдельная надстройка, то вкладка будет называться, соответственно, Power Query.

В открывшемся редакторе запросов выделим столбец (или несколько столбцов, удерживая Ctrl ) и на вкладке Преобразование выберем команду Заполнить – Заполнить вниз (Transform – Fill – Fill Down) :

Вот и всё Осталось готовую таблицу выгрузить обратно на лист Excel командой Главная – Закрыть и загрузить – Закрыть и загрузить в. (Home – Close&Load – Close&Load to. )

В дальнейшем, при изменении исходной таблицы, можно просто обновлять запрос правой кнопкой мыши или на вкладке Данные – Обновить всё (Data – Refresh All) .

Выделение содержимого ячеек в Excel

В этом курсе:

Примечание: Мы стараемся как можно оперативнее обеспечивать вас актуальными справочными материалами на вашем языке. Эта страница переведена автоматически, поэтому ее текст может содержать неточности и грамматические ошибки

Для нас важно, чтобы эта статья была вам полезна. Просим вас уделить пару секунд и сообщить, помогла ли она вам, с помощью кнопок внизу страницы

Для удобства также приводим ссылку на оригинал (на английском языке).

В Excel можно выделять содержимое ячеек, строк или столбцов.

Примечание: Если лист защищен, возможность выделения ячеек и их содержимого может быть недоступна.

Быстрое выделение ячеек по цвету или форматированию в Excel

Kutools for Excel

• Обзор
• Бесплатная загрузка
• Учебники по функциям
• Что нового?

Иногда вам может потребоваться выбрать все ячейки на основе форматирования других ячеек в Excel. Выбрав ячейки с одинаковым форматированием, вы можете изменить их как группу. Например, к выделенным ячейкам можно применить различное форматирование.

Встроенная команда Excel выбирает только те ячейки, которые полностью соответствуют форматированию ячейки, но сАвтора Выбрать ячейки с форматом утилита, вы можете:

Нажмите Kutools >> Выберите >> Выбрать ячейки с форматом. Смотрите скриншоты:

Выделить все ячейки с одинаковым / похожим форматированием ячейки

Предположим, у вас есть диапазон данных на листе, как показано на скриншоте ниже, и вы хотите выбрать все ячейки с одинаковым форматированием ячейки, вы можете сделать это следующим образом.

1. Примените эту утилиту, нажав Kutools > Выберите > Выбрать ячейки с форматомИ в Выбрать ячейки с форматом диалоговое окно сделайте следующие параметры:

A : Выберите диапазон, для которого нужно выбрать форматирование, аналогичное указанной ячейке, нажав  кнопку. B: Нажмите Выбрать формат из ячейки кнопку, чтобы выбрать ячейку, для которой вы хотите использовать ее форматирование. C : Все атрибуты базовой ячейки отображаются в списке ниже. Если вы хотите выбрать все ячейки с таким же форматированием, что и базовая ячейка, проверьте все параметры атрибутов. D Если диапазон содержит пустые ячейки и вы не хотите их выделять, проверьте это  Игнорировать пустые ячейки опцию.

2. Затем нажмите OK кнопка, и появится окно подсказки, чтобы напомнить вам, сколько ячеек будет выбрано.

3. Если вы хотите выделить ячейки, нажмите Да , вы увидите, что все ячейки с тем же форматированием, что и ваша конкретная ячейка, были выбраны, как показано на скриншотах ниже. Если вы хотите снова выполнить поиск, нажмите Нет.

Выбрать все ячейки по цвету ячейки

Предположим, у вас есть диапазон, показанный на скриншоте ниже, и вы хотите выбрать все ячейки, используя цвет ячейки, вы можете быстро сделать это следующим образом.

1. Примените эту утилиту, нажав Kutools > Выберите > Выбрать ячейки с форматомИ в Выбрать ячейки с форматом диалоговое окно сделайте следующие параметры:

A : Выберите диапазон, в котором вы хотите выделить ячейки того же цвета, что и указанная ячейка, нажав  кнопку. B : Нажмите Выбрать формат из ячейки чтобы выбрать ячейку с желаемым цветом фона. C : Все атрибуты базовой ячейки отображаются в списке ниже. Нам нужно только проверить цвет ячейки, чтобы выбрать все ячейки в этой демонстрации. D : Если диапазон содержит пустые ячейки и вы не хотите их выделять, проверьте это Игнорировать пустые ячейки опцию.

2. Затем нажмите OK кнопка, и появится окно подсказки, чтобы напомнить вам, сколько ячеек будет выбрано.

3. Если вы хотите выделить ячейки, нажмите Да , вы увидите, что все ячейки того же цвета, что и ваша конкретная ячейка, были выбраны, как показано на скриншотах ниже. Если вы хотите снова выполнить поиск, нажмите Нет.

См. Демонстрацию функции выбора ячеек с форматированием (видео 1)

Видео 1.Выбрать ячейки с форматом демонстрация функции.

Рекомендуемые инструменты для повышения производительностиСледующие ниже инструменты могут значительно сэкономить ваше время и деньги. Какой из них вам подходит?Office Tab: Использование удобных вкладок в вашем офисе, как и в случае Chrome, Firefox и New Internet Explorer.Kutools for Excel: Более 300 дополнительных функций для Excel 2019, 2016, 2013, 2010, 2007 и Office 365.Classic Menu for Office: Верните знакомые меню в Office 2007, 2010, 2013, 2016, 2019 и 365, как если бы это были Office 2000 и 2003.

Kutools for Excel

Описанная выше функциональность — лишь одна из 300 мощных функций Kutools for Excel.

Разработано для Excel (Office) 2019, 2016, 2013, 2010, 2007 и Office 365. Бесплатная загрузка и использование в течение 60 дней.