Обновлено: 21.11.2024
Совет. Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.
Предположим, что у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. На самом деле это довольно легко сделать с помощью функции поиска.
Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ — одни из самых полезных функций в Excel.
Примечание. Мастер поиска больше не доступен в Excel.
Вот пример использования функции ВПР.
=ВПР(B2,C2:E7,3,ИСТИНА)
В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в этом диапазоне ячеек, который содержит искомое значение.
Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.
В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).< /p>
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. В противном случае вам пришлось бы вводить одно из значений в столбцах C или D, чтобы вообще получить результат.
Когда вы хорошо разбираетесь в функции ВПР, функция ГПР так же проста в использовании. Вы вводите те же аргументы, но поиск выполняется в строках, а не в столбцах.
Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР
Существуют определенные ограничения при использовании функции ВПР: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.
Попробуйте
Если вы хотите поэкспериментировать с функциями поиска, прежде чем применять их к своим собственным данным, вот несколько примеров данных.
Пример VLOOKUP в работе
Скопируйте следующие данные в пустую электронную таблицу.
Совет. Прежде чем вставлять данные в Excel, задайте для столбцов с A по C ширину 250 пикселей и нажмите "Обтекание текстом" (вкладка "Главная", группа "Выравнивание").
Функция АДРЕС Excel возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. АДРЕС может возвращать адрес в относительном, смешанном или абсолютном формате и может использоваться для создания ссылки на ячейку внутри формулы.
- номер_строки – номер строки, который будет использоваться в адресе ячейки.
- col_num — номер столбца, который будет использоваться в адресе ячейки.
- abs_num – [необязательно] Тип адреса (т. е. абсолютный, относительный). По умолчанию абсолютное значение.
- a1 – [необязательно] Эталонный стиль, A1 и R1C1. По умолчанию используется стиль A1.
- лист – [необязательно] имя используемого рабочего листа. По умолчанию используется текущий лист.
Функция АДРЕС возвращает адрес ячейки на основе заданного номера строки и столбца. Например, =АДРЕС(1,1) возвращает $A$1. ADDRESS может возвращать относительную, смешанную или абсолютную ссылку и может использоваться для создания ссылки на ячейку внутри формулы. Важно понимать, что ADDRESS возвращает ссылку в виде текстового значения. Если вы хотите использовать этот текст внутри ссылки на формулу, вам нужно будет привести текст к правильной ссылке с помощью функции ДВССЫЛ. Если вы хотите указать номер строки и столбца и получить обратно значение по этому адресу, используйте функцию ИНДЕКС.
Функция АДРЕС принимает пять аргументов: строка, столбец, abs_num, a1 и лист_текст. Строка и столбец являются обязательными, другие аргументы необязательны. Аргумент abs_num определяет, является ли возвращаемый адрес относительным, смешанным или абсолютным, со значением по умолчанию 1 для абсолютного. Аргумент a1 — это логическое значение, которое переключает ссылки на стили A1 и R1C1 со значением по умолчанию TRUE для ссылок на стили A1. Наконец, аргумент sheet_text предназначен для хранения имени листа, которое будет добавлено к адресу.
Параметры АБС
В таблице ниже показаны параметры, доступные для аргумента abs_num для возврата относительного, смешанного или абсолютного адреса.
| abs_num | Результат |
|---|
| 1 (или опущен) | Абсолютный ($A$1) |
| 2 | Абсолютная строка, относительный столбец (A$1) |
< tr>3 | Относительная строка, абсолютный столбец ($A1) | | 4 | Относительная (A1) |
Примеры
Используйте ADDRESS, чтобы создать адрес из заданного номера строки и столбца. Например:
Вы можете использовать функцию АДРЕС в Excel, чтобы получить адрес конкретной ячейки на листе, предоставив функции номер строки и столбца. Я собираюсь показать, как вы можете использовать функцию АДРЕС в формуле, чтобы получить номер ячейки определенного текста на вашем листе Excel.
Я сказал: номер ячейки определенного текста. См. это изображение ниже.
У меня есть список птиц с названием и типом (а также научное название каждой птицы). Теперь я хочу получить номера ячеек или адреса ячеек, в которых есть определенный вид птиц, скажем, Воробей или Голубь .
Вот формула!
=ЕСЛИ(C4 ="воробей", АДРЕС (СТРОКА(C4),СТОЛБОН(C4),4), "")
В приведенной выше формуле я использовал функцию АДРЕС() внутри функции ЕСЛИ(). Вы можете использовать ADDRESS() внутри других функций.
Удерживая ячейку, перетащите формулу в другие строки.
Функция ЕСЛИ() имеет три параметра. Теперь здесь я проверяю, имеет ли конкретная ячейка (например, C4) значение sparrow , если true, то покажите АДРЕС ячейки, если false, то ничего не покажите.
Функция ADDRESS() имеет три параметра. Пожалуйста, смотрите полный синтаксис ниже. Первый параметр — это номер строки; второй — номер столбца, а третий — тип возвращаемого значения (4).
Если условие возвращает значение true, в столбце E будет показан номер ячейки.
Измените тип птицы в формуле и посмотрите результат.
Синтаксис функции ADDRESS
АДРЕС (номер_строки, номер_столбца, [абс_номер], [a1], [лист])
Первые два параметра важны и обязательны. Остальные три параметра являются необязательными.
Примечание. Вы можете просто ввести =АДРЕС( внутри ячейки на листе, чтобы увидеть синтаксис.
row_num : это числовое значение в виде номера строки. Это необходимо.
column_num : это числовое значение, указывающее номер используемого столбца. Это также необходимо.
abs_num : тип адреса, который возвращает функция. Это числовое значение от 1 до 4. Хотя я использую этот параметр в своей формуле (выше), это необязательный параметр.
абс означает абсолютный. Если вы опустите это значение в функции ADDRESS, то тип возвращаемого значения по умолчанию будет установлен на absolute или 1 .
Четыре значения и возвращаемые типы:
Попробуйте следующее: в приведенной выше формуле я использовал значение 4 и перенастроил ячейки C7, C10 и т. д. Измените число 4 на 1, 2 или 3 и посмотрите результат.
a1 : эталонный стиль, то есть A1 или R1C1. Значения должны быть либо TRUE, либо FALSE. Стиль A1 или TRUE по умолчанию.
лист : имя используемого рабочего листа. Если вы его не укажете, функция будет считать рабочим лист по умолчанию или текущий.
Теперь давайте заполним все параметры функции ADDRESS() и посмотрим на результат.
Определить номер столбца очень просто — просто посмотрите на букву над столбцом, и он у вас будет.
Но иногда вам нужна функция, которая сообщит вам номер столбца.
Может быть, вы хотите, чтобы он отображался для удобства?
Или использовать его в другой функции?
Какой бы ни была причина, вам нужна функция COLUMN!
Вот как это работает 🙂
*Это руководство предназначено для Excel 2019/Microsoft 365 (для Windows). Есть другая версия? Нет проблем, вы можете выполнить те же действия.
Оглавление
Что делает функция СТОЛБЦ?
Прежде чем мы рассмотрим некоторые варианты использования COLUMN, давайте поговорим о том, что именно он делает. Это очень просто.
Вот синтаксис:
Синтаксис функции СТОЛБЦ
=СТОЛБЦ([ссылка])
Там только один аргумент, ссылка. И это необязательно. Вы можете запустить функцию без него.
Без ссылки функция возвращает столбец ячейки, содержащей формулу.
ссылка — это любая ссылка на ячейку. Когда вы включаете эту ссылку, функция возвращает номер столбца указанной ячейки.
COLUMN работает так же просто, как и звучит: вы сообщаете ему, какая ячейка вас интересует, и он дает вам номер столбца.
Попробуйте это в любой электронной таблице, и вы увидите, что это дает очень предсказуемые результаты.
Получите БЕСПЛАТНЫЙ файл с упражнениями
Чтобы помочь вам освоить функцию СТОЛБЦ, мы создали пример электронной таблицы с некоторыми данными.
Загрузите его бесплатно ниже и следуйте инструкциям!
Загрузите БЕСПЛАТНЫЙ файл упражнения
Как использовать функцию СТОЛБЦ
Откройте пример электронной таблицы, и мы посмотрим, как именно использовать функцию СТОЛБЦ.
Сначала щелкните любую ячейку и введите следующую формулу:
=СТОЛБЦ()
Затем нажмите Enter.
Теперь в ячейке будет отображаться собственный номер столбца.
Поскольку я поместил функцию в ячейку B6, она вернула 2 — B — второй столбец.
Каспер Лангманн, соучредитель Spreadsheeto
Теперь попробуем использовать ссылку.
В той же ячейке введите следующую формулу:
=СТОЛБЦ(GG110)
Нажмите Enter.
Несмотря на то, что вы находитесь в той же ячейке, здесь вы получите другой результат: 189.
Независимо от того, где вы ввели формулу, Excel будет смотреть на столбец GG, поскольку вы указали его в ячейке ссылки.
Увеличение вычислений с помощью COLUMN
Как и функцию СТРОКА, СТОЛБЦ можно использовать для быстрого увеличения вычислений.
Взгляните на пример электронной таблицы, и вы поймете, что я имею в виду. В столбце А указана цель продаж.
Теперь добавим эту цель в столбцы месяцев, чтобы цель на январь составила 10 000 долларов США, на февраль – 20 000 долларов США, на март – 30 000 долларов США и т. д.
Сначала щелкните ячейку C2.
Затем введите эту формулу:
=$A$2*(СТОЛБЦ()-2)
С помощью этой формулы мы умножим цель продаж на номер столбца указанной ячейки минус два.
Поэтому мы умножим A2 на единицу в строке C, на два в строке D и так далее по строке.
Нажмите Enter.
В первом случае все просто: вы получаете тот же номер.
Теперь используйте маркер заполнения, чтобы перетащить формулу на несколько ячеек вправо.
Как видите, каждый месяц теперь имеет первоначальную цель продаж, умноженную на номер месяца.
Если ваши числа выглядят странно, перепроверьте и убедитесь, что вы добавили знак доллара перед номером строки и столбца исходной ссылки ($A$2).
Эта абсолютная ссылка имеет решающее значение для того, чтобы эта формула работала.
Каспер Лангманн, соучредитель Spreadsheeto
Тот же метод можно использовать для повторения любых вычислений по столбцам.
Имейте в виду, что здесь мы используем это с простым умножением, но вы можете комбинировать СТОЛБЦ с любой другой функцией, если вам это нужно.
Каспер Лангманн, соучредитель Spreadsheeto
Читайте также:
