Как использовать функции поиска в excel?

файлы примеров

1. Функция АДРЕС

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

Синтаксис

АДРЕС(номер_строки,номер_столбца,номер_абс,A1,текст_листа)

Аргументы

  • row_num: число, указывающее номер строки.
  • номер_столбца: число, указывающее номер столбца.
  • [abs_num]: Тип ссылки.
  • [A1]: эталонный стиль.
  • [sheet_text]: текстовое значение в качестве имени листа.

Комментарии

  • По умолчанию функция АДРЕС возвращает в результате абсолютную ссылку.

Пример

В приведенном ниже примере мы использовали разные аргументы для получения всех типов результатов.

Excel-адрес-функция-пример-1

Со ссылочным стилем R1C1 :

  • Относительная ссылка.
  • Относительная ссылка на строку и абсолютная ссылка на столбец.
  • Абсолютная ссылка на строку и относительная ссылка на столбец.
  • Абсолютная ссылка.

С эталонным стилем A1:

  • Относительная ссылка.
  • Относительная ссылка на строку и абсолютная ссылка на столбец.
  • Абсолютная ссылка на строку и относительная ссылка на столбец.
  • Абсолютная ссылка.

2. Функция ЗОНЫ

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

синтаксис

ПОЛЯ (ссылка)

Аргументы

  • ссылка: ссылка на ячейку или диапазон ячеек.

Комментарии

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

Пример

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

Excel-Зоны-Пример-1

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

Excel-Зона-функция-пример-2 (1)

3. ВЫБЕРИТЕ функцию

Функция ВЫБОР возвращает значение из списка значений на основе указанного номера позиции. Проще говоря, он ищет значение в списке на основе его позиции и возвращает его в результате.

Синтаксис

ВЫБРАТЬ(числовой_индекс,значение1,значение2,…)

Аргументы

  • index_num: номер, используемый для указания положения значения в списке.
  • значение1: диапазон ячеек или входное значение, из которого можно выбирать.
  • [значение2]: диапазон ячеек или входное значение, из которого можно выбирать.

Комментарии

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

Пример

В приведенном ниже примере мы использовали функцию ВЫБОР с раскрывающимся списком для расчета четырех разных величин (сумма, среднее значение, максимум и сочетание). Поэтому мы использовали формулу ниже для расчета четырех вещей:

=ВЫБЕРИТЕ(ВПР(K2,Q1:R4,2,FALSE),СУММА(O2:O9),СРЗНАЧ(O2:O9),МАКС(O2:O9),МИН(O2:O9))

Excel-выбрать-функцию-пример-1

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

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

И вместо значений мы использовали четыре формулы для 4 разных расчетов.

4. Функция СТОЛБЕЦ

Функция СТОЛБЕЦ возвращает номер столбца для данной ссылки на ячейку. Как вы знаете, каждая ссылка на ячейку состоит из номера столбца и номера строки. Поэтому он берет номер столбца и возвращает его в результате.

Синтаксис

СТОЛБЕЦ([ссылка])

Аргументы

  • ссылка: ссылка на ячейку, для которой вы хотите получить номер столбца.

Комментарии

  • Вы не можете ссылаться на несколько ссылок.
  • Если вы имеете в виду массив, функция столбца также вернет номера столбцов в массиве.
  • Если вы имеете в виду диапазон из нескольких ячеек, он вернет номер столбца самой левой ячейки. Например, если вы обратитесь к диапазону A1:C10, он вернет номер столбца ячейки A1.
  • Если вы пропустите указание ссылки, она вернет номер столбца текущей ячейки.

Пример

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

Excel-столбец-функция-пример-1

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

Excel-столбец-функция-пример-2

5. Функция СТОЛБЦЫ

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

Синтаксис

КОЛОННЫ(таблица)

Аргументы

  • Таблица: таблица или диапазон ячеек, из которых вы хотите получить количество столбцов.

Комментарии

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

Пример

В приведенном ниже примере мы использовали COLUMN, чтобы получить количество столбцов в диапазоне A1:F1.

Excel-столбцы-функция-пример-1

6. Функция ФОРМУЛАТЕКСТ

Функция FORMULATEXT возвращает формулу для ячейки, на которую имеется ссылка. А если в указанной ячейке нет формулы, значения или пробела, возвращается #Н/Д.

Синтаксис

ТЕКСТФОРМУЛА (ссылка)

Аргументы

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

Комментарии

  • Если вы ссылаетесь на другую книгу, эта книга должна быть открыта, иначе формула не будет отображаться.
  • Если вы имеете в виду диапазон, состоящий из более чем одной ячейки, он вернет формулу для верхней левой ячейки данного диапазона.
  • Он вернет значение ошибки «#Н/Д», если ячейка, которую вы используете в качестве ссылки, не содержит формул, содержит формулу длиной более 8192 символов, ячейка защищена или внешняя книга не открыта.
  • Если вы ссылаетесь на две ячейки в циклической ссылке, будут возвращены результаты из обеих.

Пример

В приведенном ниже примере мы использовали текст формулы с разными типами ссылок. Когда вы ссылаетесь на ячейку, в которой нет формулы, она возвращает значение ошибки «#Н/Д».

Excel-формулатекст-функция-пример-1

7. Функция ПОИСК

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

Синтаксис

HLOOKUP(искомое_значение, table_array, row_index_num, [range_lookup])

Аргументы

  • искомое_значение: значение, которое вы хотите найти.
  • table_array: таблица данных или массив, из которого вы хотите найти значение.
  • row_index_num: числовое значение, представляющее количество строк вниз от верхней строки, из которой вы хотите получить значение. Например, если вы укажете 2 и искомое значение находится в A10 в таблице данных, оно вернет значение в ячейке B10.
  • [range_lookup]: логическое значение, определяющее тип поиска. Если вы хотите выполнить поиск с точным соответствием, используйте FALSE, а если вы хотите выполнить поиск с неточным совпадением, используйте TRUE (по умолчанию).

Комментарии

  • Вы можете использовать подстановочные знаки.
  • Вы можете выполнить точное и нечеткое совпадение.
  • При выполнении нечеткого сопоставления обязательно сортируйте данные в порядке возрастания слева направо, и если данные не в порядке возрастания, они вернут неточный результат.
  • Если range_lookup имеет значение true или опущено, он выполнит неточное совпадение, но вернет точное совпадение, если искомое значение существует в диапазоне поиска.
  • Если range_lookup имеет значение true или опущено, а искомое значение не находится в диапазоне поиска, будет возвращено ближайшее значение, меньшее искомого значения.
  • Если range_lookup имеет значение false, нет необходимости сортировать диапазон данных.

Пример

В приведенном ниже примере мы использовали функцию HLOOKUP с ПОИСКПОЗ для создания динамической формулы, а затем использовали раскрывающийся список для изменения значения поиска ячейки.

Excel-hlookup-функция-пример-1

Имя поля из ячейки C7 используется в качестве значения поиска. Диапазон B1: F5 в качестве массива таблицы, а для row_index_num мы использовали функцию сопоставления, чтобы получить номер строки.

Excel-hlookup-функция-пример-2

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

8. Функция ГИПЕРССЫЛКА

Функция ГИПЕРССЫЛКА возвращает строку с прикрепленной к ней гиперссылкой. Проще говоря, функция ГИПЕРССЫЛКА, как и опция ГИПЕРССЫЛКА в Excel, помогает вам создать гиперссылку.

Синтаксис

ГИПЕРССЫЛКА(расположение_ссылки,[дружественное_имя])

Аргументы

  • Link_Location: местоположение, для которого вы хотите добавить ГИПЕРССЫЛКУ. Его можно разделить еще на два термина.
    1. ссылка: это может быть адрес ячейки или диапазона ячеек на том же листе или в любом другом листе или книге. Мы также можем связать закладку из документа Word.
    2. Местоположение: это может быть ссылка на жесткий диск, сервер, использующий путь UNC, или любой URL-адрес из Интернета или интрасети. (В Excel онлайн вы можете использовать только веб-адрес для функции ГИПЕРССЫЛКА). Вы можете вставить ссылку на функцию, вставив ее в виде текста с кавычками или указав ячейку, содержащую ссылку, в виде текста. Обязательно используйте «HTTPS://» перед веб-адресом.
  • [Friendly_name]: это необязательная часть этой функции. Он действует как лицевая сторона ссылки подключения.
    1. Вы можете использовать любой тип текста, числа или и то, и другое.
    2. Вы также имеете в виду ячейку, содержащую дружественное_имя.
    3. Если вы проигнорируете это, функция будет использовать для отображения адрес ссылки.
    4. Если дружественное_имя возвращает ошибку, функция отобразит ошибку.

Комментарии

  • Свяжите файл, сохраненный по веб-адресу. Вы можете использовать файл, сохраненный по веб-адресу. Это помогает нам эффективно делиться файлом.
  • Связать файл, сохраненный на жестком диске. Вы также можете использовать эту функцию при работе в автономном режиме. Вы можете связать файл, хранящийся на вашем жестком диске, и получить к нему доступ через один лист Excel, без необходимости заходить в каждую папку, чтобы открыть их.
  • Ссылка на файл документа Word: это также замечательная функция ГИПЕРССЫЛКИ. Вы можете связать файл документа Word или определенное место в файле документа Word с помощью закладки.
  • Свяжите файл без использования понятного имени: если вы хотите отобразить для пользователя фактическую ссылку на файл или местоположение. В этой ситуации вам просто нужно игнорировать объявление понятного имени в функции HYPERLINK.

9. КОСВНАЯ функция

Функция ДВССЫЛ возвращает допустимую ссылку из текстовой строки, которая представляет ссылку на ячейку. Проще говоря, вы можете ссылаться на диапазон ячеек, используя адрес ячейки в качестве текстового значения.

Синтаксис

ДВССЫЛ(ref_text, [a1])

Аргументы

  • ref_text: текст, представляющий адрес ячейки, адрес диапазона ячеек, именованный диапазон или имя таблицы. Например, A1, B10:B20 или MyRange.
  • [a1]: число или логическое значение, представляющее тип ссылки на ячейку, указанный в ref_text. Например, если вы хотите использовать стиль ссылки A1, используйте TRUE или 1, а если вы хотите использовать стиль ссылки R1C1, используйте FALSE или 0 для стиля ссылки R1C. А если вы не укажете тип ссылки на ячейку, по умолчанию будет использоваться стиль A1.

Комментарии

  • Когда вы ссылаетесь на другую книгу, эту книгу следует открыть.
  • Если вы вставите строку или столбец в диапазон, на который вы ссылаетесь, ДВССЫЛ не обновит эту ссылку.
  • Если вы хотите вставить текст непосредственно в функцию, вы должны заключить его в двойные кавычки или также можете сослаться на ячейку, содержащую текст, который вы хотите использовать в качестве ссылки.

Пример

1. Ссылка на другой лист

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

Excel-косвенная-функция-пример-1

В ячейке C2 мы использовали следующую формулу для объединения текста.

=ДВССЫЛ(«‘»&A2&»‘!»&B2)

Эта комбинация создает текст, который используется функцией ДВССЫЛ для ссылки на ячейку A1 на листе 1, и самое приятное то, что при изменении имени листа или адреса ячейки ссылка автоматически изменится.

Ячейка A1 в «Лист1» имеет значение «Да», поэтому косвенная функция возвращает значение «Да».

2. Ссылка на другую книгу.

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

Excel-косвенная-функция-пример-2

В приведенном выше примере мы использовали следующую формулу, чтобы получить значение ячейки A1 книги «Книга1».

=ДВССЫЛ(“[“&A2&”]”&B2&”!”&C2)

Поскольку у нас есть имя книги в ячейке «A2», имя листа в ячейке «B2» и имя ячейки в ячейке «C2». Мы объединяем их, чтобы использовать в качестве входного текста в косвенной функции.

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

3. Использование именованных диапазонов

Да, вы также можете обратиться к именованному диапазону, используя косвенную функцию. Это просто. После того как вы создали именованный диапазон, вы должны ввести этот именованный диапазон в виде текста в формате ДВССЫЛ.

Excel-косвенная-функция-пример-3

В приведенном выше примере у нас есть раскрывающийся список в ячейке E1, содержащий список именованных диапазонов, а в ячейке E2 мы использовали это имя. Поскольку диапазон B2:B5 называется «Количество», а диапазон C2:C5 — «Количество».

Когда вы выбираете количество из раскрывающегося списка, косвенная функция мгновенно обращается к именованному диапазону. И когда вы выберете сумму из раскрывающегося меню, вы получите сумму диапазона ячеек C2:C5.

11. Функция ПОИСК

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

Синтаксис

ПОИСК(значение,диапазон_поиска, [диапазон_результата])

Существует два типа функций ПОИСК.

  • векторная форма
  • Форма таблицы

Аргументы

  • значение: значение, которое вы хотите найти в столбце или строке.
  • диапазон_поиска: столбец или строка, в которой вы хотите найти значение.
  • [диапазон_результата]: столбец или строка, из которой вы хотите вернуть значение. Это необязательный аргумент.

Комментарии

  • Вместо использования формы массива лучше использовать ВПР или ГПР.

12. Функция ПОИСКПОЗ

Функция ПОИСКПОЗ возвращает порядковый номер значения массива. Проще говоря, функция ПОИСКПОЗ ищет значение в списке и возвращает номер позиции этого значения в списке.

Синтаксис

ПОИСКПОЗ (искомое_значение, искомый_массив, [тип_соответствия])

Аргументы

  • искомое_значение : значение, позицию которого вы хотите получить из списка значений.
  • искомый_массив : диапазон ячеек или массив содержит значения.
  • [match_type] : число (-1, 0 и 1), указывающее, как Excel ищет значение в списке значений.
    1. Если вы используете 1, он вернет наибольшее значение, равное или меньшее значения поиска. Значения в списке необходимо отсортировать по возрастанию.
    2. Если вы используете -1, он вернет наименьшее значение, равное или превышающее искомое значение. Значения в списке необходимо отсортировать по возрастанию.
    3. Если вы используете 0, он вернет точное совпадение со списком.

Комментарии

  • Вы можете использовать подстановочные знаки .
  • Если в списке нет соответствующего значения, if вернет #N/A.
  • Функция сопоставления не чувствительна к регистру.

Пример

В приведенном ниже примере мы использовали 1 в качестве типа соответствия и ищем значение 5.

Excel-матч-функция-пример-1

Как я уже упоминал, если вы используете 1 в типе соответствия, он возвращает наибольшее значение, равное или меньшее искомого значения. Во всем списке есть 3 значения меньше 5 и 4 – самое высокое.

Вот почему в результате он вернул 3, что соответствует положению значения 4.

13. Функция переключения

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

Синтаксис

СМЕЩ(ссылка, строки, столбцы, [высота], [ширина])

Аргументы

  • ссылка : ссылка, от которой вы хотите начать смещение. Это может быть ячейка или диапазон соседних ячеек.
  • rows : количество строк, которые сообщают OFFSET о перемещении вверх или вниз от ссылки. Чтобы пойти вниз, вам нужно положительное число, а чтобы подняться, вам нужно отрицательное число.
  • cols : количество столбцов сообщает OFFSET о необходимости перемещения влево или вправо от ссылки. Чтобы пойти направо, вам нужно положительное число, а чтобы пойти налево, вам нужно отрицательное число.
  • [высота] : число, указывающее, какие строки включать в ссылку.
  • [ширина]: число, указывающее, какие столбцы включать в ссылку.

Комментарии

  • СМЕЩ — это «изменчивая» функция, она пересчитывает каждый раз, когда в электронной таблице происходят изменения.
  • Он отображает #REF! значение ошибки, если смещение находится за пределами края листа.
  • Если высота или ширина опущены, используются эталонные высота и ширина.

Пример

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

Excel-смещение-функция-пример-1

14. Функция ЛИНИЯ

Функция ROW возвращает номер строки указанной ячейки. Проще говоря, с помощью функции ROW вы можете получить номер строки ячейки, и если вы не ссылаетесь ни на какую ячейку, она возвращает номер строки ячейки, в которую вы ее вставляете.

Синтаксис

СТРОКА([ссылка])

Аргументы

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

Комментарии

  • Он будет включать все типы листов (лист диаграммы, рабочий лист или лист макроса).
  • Вы можете ссылаться на фолио, даже если они видимы, скрыты или очень скрыты.
  • Если вы не укажете какое-либо значение в функции, она даст вам номер листа, к которому вы применили функцию.
  • Если вы укажете неверное имя листа, будет возвращено значение #N/A.
  • Если вы укажете неверную ссылку на лист, будет возвращен #REF!.

Пример

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

Excel-линия-функция-пример-1

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

Excel-линия-функция-пример-2

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

Excel-линия-функция-пример-3

15. Функция ЛИНИИ

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

Синтаксис

ЛИНИИ (таблица)

Аргументы

  • массив: ссылка на ячейку или массив для проверки количества строк.

Комментарии

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

Пример

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

Пример функции строк Excel

16. Функция ТРАНСПОНИРОВАНИЕ

Функция ТРАНСП меняет ориентацию диапазона. Проще говоря, с помощью этой функции вы можете изменять данные из строки в столбец и из столбца в строку.

Синтаксис

ТРАНСПОН (таблица)

Аргументы

  • массив: Массив или диапазон, который вы хотите транспонировать.

Комментарии

  • Вам необходимо применить ТРАНСПОЗ как функцию массива, используя то же количество ячеек, что и в исходном диапазоне, нажав Ctrl+Shift+Enter.
  • Если вы выберете ячейки, меньшие, чем исходный диапазон, данные будут транспонированы только для этих ячеек.

Пример

Здесь нам нужно транспонировать данные из диапазона B2:D4 в диапазон от G2 до I4:

Excel-транспонировать-функцию-пример-1

Для этого нам сначала нужно перейти к ячейке G2 и выбрать диапазон ячеек до I4.

Excel-транспонировать-функцию-пример-2

Затем введите (=TRANSPOSE(B2:D4)) в ячейку G2 и нажмите Ctrl+Shift+Enter.

Excel-транспонировать-функцию-пример-3

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

Excel-транспонирование-функции-пример-4

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *