Вы должны знать, как использовать ВПР, ИНДЕКС, ... в Excel

Функции ВПР, ИНДЕКС, ПОИСКПОЗ, ... - довольно сложные функции, но они эффективны при интеллектуальном анализе данных в Excel . В этой статье я подробно расскажу, как эффективно использовать ВПР и связанные с ней функции.

1. Функция ВПР.

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

Формула : = ВПР (искомое_значение, массив_таблиц, индекс_столбца, [диапазон_просмотр])

В котором :

+ Lookup_value : значение для поиска.

+ Table_array : два или более столбца данных.

+ Col_index_num : количество столбцов для извлечения данных.

+ Range_lookup : определяет, является ли поиск полностью точным (FALSE) или только относительно точным (TRUE или опущен).

Пример : мы используем функцию ВПР для определения бонуса каждого сотрудника. Значение, которое нужно искать, - это позиция ячейки C2; Обнаруживаемый массив - это массив F1: G3. При вводе формулы = ВПР (C2, $ F $ 1: $ G $ 3,2,0) (1) результатом будет STAFF 2000 и ROOMER 5000 (2).

2. Функция ИНДЕКС

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

Формула : = ИНДЕКС (массив; номер_строки; [номер_столбца])

Из них : Массив (обязательно): диапазон ячеек или константа массива.

Пример : когда нам нужно получить значение первой строки, второго столбца в таблице, мы используем функцию ИНДЕКС . Массив - это массив, который нам нужно определить, это B2: C5; Row_num - это строка 1; Column_num - это столбец 2. При вводе формулы ИНДЕКС (B2: C5,1,2,1) (1) результатом будет «НАЧАЛЬНИК ПОМЕЩЕНИЯ» (2).

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

Использование : ищет конкретное значение в диапазоне ячеек и показывает относительное положение этого значения.

Формула : = ПОИСКПОЗ (искомое_значение, искомое_массив, [совпадение_тип])

В котором :

+ lookup_value (обязательно) : значение, которое вы хотите сопоставить в массиве поиска. Изменить значение поиска. Число может быть значением (числом, текстом или логическим значением) или ссылкой ячейки на число, текст или логическое значение.

+ lookup_array (обязательно) : диапазон ячеек для поиска.

+ match_type (необязательно) : число -1, 0 или 1. Аргумент типа соответствия указывает, как Excel сопоставляет значение поиска со значениями в массиве поиска. Значение по умолчанию для этого аргумента - 1.

Например : когда нам нужно знать, в каком положении находится CLOTH, мы используем функцию MATCH . При вводе формулы = ПОИСКПОЗ (FF2 & «*», «B2: B7», 0) (1), результат будет 3 (2).

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

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

Формула : = ДВССЫЛ (ref_text; [a1])

В котором :

+ Ref_text (обязательно) : ссылка на ячейку, содержащую ссылочный стиль A1, ссылочный стиль R1C1 или ссылку на ячейку в виде текстовой строки.

+ Если текст ссылки не является допустимой ссылка на ячейке, КОСВЕННЫЕ возвращает #REF! Значение ошибки . .

+ Если ref_text ссылается на другую книгу (внешнюю ссылку), эта книга должна быть открыта. Если источник учебное пособие не является открытым, КОСВЕННЫЕ возвращает #REF! Значение ошибки . .

Например : когда вам нужно только рассчитать комиссию и вы хотите перетащить, опустить или переместить поле результата в другую позицию, не меняя денежную формулу, мы используем КОСВЕННО . При вводе формулы

= INDIRECT («B2»; TRUE) * INDIRECT («C2»; TRUE) (1) даст результат 700 * 100 = 70000 (2).

4. Функция СМЕЩЕНИЕ

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

Формула : = СМЕЩЕНИЕ (ссылка, строки, столбцы, [высота], [ширина])

В котором :

+ Ссылка (обязательно) : эталонная область, на которой вы хотите основать эталонное расстояние. Контрольный диапазон должен относиться к ячейке или диапазону соседних ячеек; в противном случае СМЕЩЕНИЕ возвращает значение ошибки # ЗНАЧ !. .

+ Строки (обязательно) : количество строк вверх или вниз, на которые должна ссылаться левая верхняя ячейка. Строки могут быть положительными (что означает, что они находятся ниже начальной ссылки) или отрицательными (что означает, что они находятся выше начальной ссылки).

+ Cols (обязательно) : количество столбцов слева или справа, на которые должна ссылаться ячейка в верхнем левом углу результата. Столбцы могут быть положительными или отрицательными.

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

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

Пример : мы используем СМЕЩЕНИЕ для локализации значений, а затем используем функцию СУММ для вычисления суммы. Мы говорим о ячейке A1; по сравнению с A1, количество строк в контрольном диапазоне меньше 1 ячейки, поэтому количество строк равно 1; по сравнению с A1, левый столбец равен 1 ячейке, поэтому столбец равен 1; Высота берем 3 линии; Ширину получаем 2 столбца. Таким образом, при сложении всех значений в регионе получается 2025 год.

5. Функция TRANSPOSE

Использование : преобразует горизонтальный диапазон ячеек в вертикальный и наоборот, т. Е. Преобразует строки в столбцы, а столбцы в строки.

Формула : = TRANSPOSE (массив)

Делать

+ Шаг 1 : выберите пустые ячейки.

+ Шаг 2 : введите = TRANSPOSE (B2: C4).

+ Шаг 3 : введите диапазон исходных ячеек.

+ Шаг 4 : Наконец, нажмите CTRL + SHIFT + ENTER.

Пример : когда нам нужно преобразовать весь прайс-лист продукта из вертикального в горизонтальный, мы используем функцию TRANSPOSE с массивом значений от B2 до C4. При вводе формулы = TRANSPOSE (B2: C4) (1) даст результат, как показано (2).

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

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

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

В котором :

Link_location может относиться к месту в документе, например, к определенной ячейке или именованному диапазону на листе или в книге Excel, или к закладке в документе Microsoft Word.

Путь может указывать на файл, хранящийся на жестком диске. Путь также может быть путем универсального соглашения об именах (UNC) на сервере (в Microsoft Excel для Windows).

Пример : длинные и запутанные ссылки будут раздражать пользователя. Поэтому используйте ГИПЕРССЫЛКУ, чтобы упростить использование интерфейса. Если ввести формулу = ГИПЕРССЫЛКА (A1, «СВЯЗАТЬСЯ НА GOOGLE») (1), появится СВЯЗЬ GOOGLE (2).

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