Как пользоваться функцией просмотр в excel

Синтаксис

=ВПР(элемент_который_нужно_найти;адрес_таблицы_где_находится_искомое_значение;порядковый_номер_столбца;интервальный_просмотр)

Элемент – может быть числовым (адрес ячейки) или текстовым («текст»).

Адрес таблицы – диапазон ячеек, где примерно находится значение.

Номер столбца – принимает целое число из диапазона от 1 до n, из него будет извлечен результат.

Интервальный просмотр – приблизительное (ближайшее) соответствие критерию обозначается как 1 (истина), а точное соответствие – 0 (ложь). Данный логический аргумент указывать необязательно, если таблица отсортирована от минимального к максимальному значению. Если таблица не отсортирована и аргумент опущен, это равносильно истине.

В русифицированной версии Excel аргументы вводятся через знак «;», в англоязычной – через запятую.

Как пишут формулы в Excel

Знак равенства в начале записи в ячейке листа – это признак того, что в данной ячейке листа размещаются не данные, а формула для нахождения (определения, вычисления, расчета и т.п.) данных.

В данном случае нужно в ячейку B5 записать одну из самых простых формул Excel, которая выглядит следующим образом: «=B3» (без кавычек). Что это значит? «Равно» — это признак формулы. А сама формула состоит из единственного обозначения единственной ячейки листа – это обозначение ячейки B3.

Формулы экселя пишут либо прямо в выделенной ячейке листа, как будто туда вводятся данные. Но, повторюсь, формула начинается со знака равенства – это ее единственное отличие от обычных данных. Либо можно формулу писать в специальном окошечке для ввода формул (обозначено цифрой 1 на рис. 5).

Пример формулы Excel

Теперь предлагаю вписать формулу «=B3» в окошко для ввода формул (рис. 5):

Рис. 5. Ввод формулы «=B3» в окошко для ввода формул табличного редактора Microsoft Excel до момента нажатия на клавишу (до исполнения формулы).

Как только написана формула, ячейка B3 таблицы B2:F3 окрасилась в синий цвет, а в формуле обозначение ячейки B3 тоже окрасилось в такой же цвет: так нам эксель дает «подсказки» в процессе составления формулы. Удобно, не правда ли?

Кстати, можно вручную не вводить «B3» (без кавычек), а в процессе ввода формулы поместить курсор на ячейку B3 и кликнуть один раз левой кнопкой мышки. Эксель «понимает» такой клик точно так, как «поместить обозначение указанной ячейки в формулу». Можно попробовать кликнуть вместо ручного ввода.

Что получим в результате «вычисления» по указанной формуле? С помощью данной формулы Excel найдет данные в ячейке B3 и поместит их в ячейку B5. Как эксель об этом «догадается»? Да очень просто. В формуле указана ячейка B3 – это факт. А сама формула стоит (записана) в ячейке B5, значит, результат работы формулы нужно записать в клетку B5. Вот таким нехитрым способом удается данные из ячейки B3 воспроизвести в ячейке B5: теперь и в B3, и в B5 записано «Петрова» (рис. 6).

Рис. 6. После вычисления по формуле «=B3», расположенной в ячейке B5, в ячейке B5 появилась фамилия «Петрова», перенесенная туда из ячейки B3.

Зачем нужен предварительный просмотр документа

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

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

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?

Делается это в несколько шагов:

1. Открыть первую со старой информацией.
2. Добавить дополнительный столбик для новых данных «Новая стоимость».
3. Выделить первое пустое поле в созданном столбце (С2).
4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
5. Найти категорию Excel «Ссылки и массивы».
6. Выбрать ВПР.
7. Задать «Аргументы».

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

Функция просмотр excel

Функция ПРОСМОТР пришла в Excel из электронной таблицы Lotus 1-2-3 из соображений приемственности, а туда перекочевала, поговаривают, вообще из VisiCalc (1979). ПРОСМОТР имеет 2 варианта синтаксиса:

Форма массива: =ПРОСМОТР( искомое_значение ; массив )

искомое_значение — константа или ссылка на ячейку, значение которой вы собираетесь искать

вектор_поиска — одномерный массив (строка или столбец), в котором осуществляется поиск значения

вектор_результата — одномерный массив (строка или столбец), из которого будут возвращаться данные с той же позиции, на которой они были найдены в векторе поиска. Это необязательный параметр.

массив — двумерный массив (диапазон), в первом столбце которого осуществляется поиск, и, из последнего столбца которого возвращаются данные)

Некоторые замечания о ПРОСМОТР

у ПРОСМОТРа есть интересное свойство, если искать значение заведомо бОльшее,чем есть в таблице, то будет возвращено последнее имееющееся число, при этом значения ошибки и пустые ячейки игнорируются. Данным способом можно искать последнюю заполненную ячейку: =ПРОСМОТР(9E+307;A1:A10) — последняя заполненная ячейка с числом =ПРОСМОТР(2;1/(A1:A100);A1:A10) — последняя не нулевая ячейка При этом сортированы или нет исходные данные не имеет значения.

Все это относится и к ВПР и ПОИСКПОЗ, если не указывать интервальный просмотр (или он равен 1/ИСТИНА)

Excel LOOKUP function

The Microsoft Excel LOOKUP function finds certain value in a one column or one row range, and return the corresponding value from another (one row or one column) range.

Tips: To lookup values, it is more recommended to use the VLOOKUP/HLOOKUP function and the new XLOOKUP function, because the LOOKUP function has more limitations in the calculation. Use the VLOOKUP/HLOOKUP function or the XLOOKUP function, depending on the Excel version you are using.

• VLOOKUP function: It performs a vertical lookup. Find things in a table or a range by row. It is available in Excel 2007 to 2021, and Excel for Microsoft 365.
• HLOOKUP function: It performs a horizontal lookup. Find things in the top row of a table or an array of values by column. It is available in Excel 2007 to 2021, and Excel for Microsoft 365.
• XLOOKUP function: The XLOOKUP function is the new lookup function that solves a lot of the issues that VLOOKUP and HLOOKUP had. It finds things in a table or a range in any direction (up, down, left, right), which is more easier to use and works faster than other lookup functions. It is only available in Excel for Microsoft 365.

Arguments

Lookup_value (required): The value you will search for. It can be a number, a text or a reference to a cell containing the search value.

Lookup_vector (required): A single row or single column range to be searched. The value in this argument can be numbers, text or logical values.Note: Values in this range must be sorted in ascending order, otherwise, LOOKUP might not return the correct value..

result_vector (optional): The LOOKUP function searches for the value in the look_up vector, and returns the result from the same column or row position in the result_vector. It is a one-row or one-column data which has the same size with the lookup_vector.

Function Notes:

1. If the lookup number is smaller than all values in the lookup range, it will return a #N/A error value. 2. LOOKUP works based on approximate match, if the lookup value can’t be found exactly, it will match the next smallest value. 3. If there are multiple matched look_up values, it will match the last value. 4. The LOOKUP function is not case-sensitive.

Examples

Example 1: Use LOOKUP function to find a value with one criterion

As the below screenshot shown, you need to find Peach in the table range, and return the corresponding result in the Mon column, you can achieve it as follows.

Select a blank cell, copy the below formula into it and press the Enter key.=LOOKUP(H4,B3:E8,D3:D8)

Notes:

• H4 is the reference cell containing the search value “Peach”; B3:E8 is the lookup range containing the search value and the result value; D3:D8 is the result value range.
• You can replace the H4 with «Peach» or B5 directly as you need.

Example 2: LOOKUP function search for a value with multiple criteria

As below screenshot shown, there are duplicate names in different departments. For finding the position of a specific person (says Fred Schuler), you need to match the Name and Department criteria at the same time. Please do as follows.

1. Select blank cell to place the result, copy the below formula into it and press the Enter key.=LOOKUP(1,0/((B2:B8=F4)*(C2:C8=G4)),D2:D8)

Notes:

• In the formula, B2:B8 and C2:C8 are the column ranges containing the first and second look up values; F4 and G4 are the references to the cells containing the two criteria; D2:D8 is the result value range. Please change them based on your table range.
• This formula can also help.=LOOKUP(1,0/((B2:B8&C2:C8=F4&G4)),D2:D8)

Именованные диапазоны облегчают понимание расчетов и еще больше упрощают работу.

Абсолютные ссылки выглядят довольно некрасиво и не очень понятно и наглядно. Поэтому можно сделать ваши расчёты намного чище и проще для понимания, заменив абсолютные ссылки именованными диапазонами. И никакие возможные изменения на вашем листе Excel не смогут их «испортить».

Копировать и переносить их также можно без проблем.

В приведенном выше примере с данными о сотрудниках вы можете назвать входную ячейку B2 «фамилия», а затем выделить все ячейки с информацией и назвать диапазон B5:F100 как «ДанныеСлужащего». Затем перепишите свою формулу в C2 следующим образом:

Сравните сами — насколько понятнее стал расчет из совета №12 по сравнению с №11.

Примеры векторов

Чтобы лучше разобраться в работе функции ПРОСМОТР, вы можете сами опробовать рассмотренные примеры на практике. В первом примере у вас должна получиться электронная таблица, которая выглядит примерно так:

1. Скопируйте данные из таблицы ниже и вставьте их в новый лист Excel.

   Скопируйте эти данные в столбец A	Скопируйте эти данные в столбец B
   Частота 4,14	Цвет красный
   4,19	оранжевый
   5,17	желтый
   5,77	зеленый
   6,39	синий

2. Теперь скопируйте формулы ПРОСМОТРА из приведенной ниже таблицы в столбец D своего листа.

   Скопируйте эту формулу в столбец D	Ниже описано, что эта формула означает	Предполагаемый результат
   Формула
   =ПРОСМОТР(4,19; A2:A6; B2:B6)	Поиск значения 4,19 в столбце A и возврат значения из столбца B, находящегося в той же строке.	оранжевый
   =ПРОСМОТР(5,75; A2:A6; B2:B6)	Поиск значения 5,75 в столбце A, соответствующего ближайшему наименьшему значению (5,17), и возврат значения из столбца B, находящегося в той же строке.	желтый
   =ПРОСМОТР(7,66; A2:A6; B2:B6)	Поиск значения 7,66 в столбце A, соответствующего ближайшему наименьшему значению (6,39), и возврат значения из столбца B, находящегося в той же строке.	синий
   =ПРОСМОТР(0; A2:A6; B2:B6)	Поиск значения 0 в столбце A и возврат значения ошибки, так как 0 меньше наименьшего значения (4,14) в столбце A.	#Н/Д

3. Чтобы эти формулы выводили результат, может потребоваться выделить их на листе Excel и нажать клавишу F2, а затем — ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

   

   

   

   

   

   

   

   

   

   

Навигация по листу данных в Excel

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

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

Другой способ — использование клавиш сочетаний Ctrl+Стрелки. Эти комбинации позволяют перемещаться на одну ячейку вправо, влево, вверх или вниз. Если при этом зажать Shift, выделение продолжится до нужной ячейки.

Также можно использовать команду «Переход» (GoTo), которая находится в меню «Редактирование». В этом случае необходимо выбрать нужный диапазон и Excel перенесет вас на нужную ячейку.

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

В отдельных случаях может потребоваться выполнить быстрый поиск нужной информации. Для этого можно воспользоваться командой «Найти» или «Найти и заменить» в меню «Редактирование».

СЧЁТЕСЛИМН

СЧЁТЕСЛИМН очень похожа на функцию СУММЕСЛИМН, только в отличии от нее, она не суммируется значения, а только считает количество ячеек, которые соответствуют определенным условиям. Как и в случае с СУММЕСЛИМН, у СЧЁТЕСЛИМН есть упрощенная форма СЧЁТЕСЛИ, который считает количество ячеек только по одному критерию, но лучше используйте более общий вариант.

— Диапазон условия 1 — Диапазон ячеек, которые проверяются на соответствие определенному условию.- Условие 1 — Условие, которое определяет какие ячейки надо учитывать при подсчете.Обратите внимания, что диапазонов условий и соответственно условий может быть несколько.

В примере выше, мы считаем сколько в таблице ячеек, в которых фамилия — Петров, а город — Москва. В формуле СЧЁТЕСЛИМН(A2:A13;E2;B2:B13;F2) диапазон A2:A13  — диапазон фамилий, которые мы проверяем, Е2 — та фамилия, которую мы ищем в диапазоне; B2:B13 — диапазон городов и соответственно F2 — город, который мы учитываем при подсчете ячеек. Получившееся число 3 — это количество строк в таблице, где фамилия равна Иванов, а город равен Москва.

Заносим данные в таблицу Excel

Чтобы перейти к поиску данных в таблице Excel, сначала нужно заполнить таблицу данными. Возьмем за основу таблицу B2:F3, показанную на рис. 2, и занесем в нее данные – это список гипотетических учеников гипотетического класса, скажем 8А класса.

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

Рис. 3. Таблица B2:F3 в экселе с заполненными ячейками B2, C2, D2, E2, F2, B3, C3, D3, E3, F3.

В таблице, показанной на рис. 3, на самом деле есть еще данные в ячейках B1, C1, D1, E1, F1. При этом таблица обозначена как B2:F3.

Почему же ячейка B1 не была назначена в качестве самой верхней левой ячейки таблицы? Да потому, что данные в первой строке таблицы не информативные. Там размещены заголовки столбцов таблицы. Данные же на учеников находятся в ячейках B2:F3, отсюда и такое обозначение таблицы.

Некоторые аспекты применения формулы в реальной жизни

Классическая иллюстрация к диапазонному поиску — задача определения скидки по размеру заказа.

Поиск текстовых строк

Безусловно, ВПР ищет не только числа, но и текст

При этом надо принимать во внимание, что регистр символов формула не различает. Если использовать символы подстановки, то можно организовать нечёткий поиск

Есть два символа подстановки: «?» — заменяет один любой символ в текстовой строке, «*» — заменяет любое количество любых символов.

Борьба с пробелами

Часто поднимается вопрос, как решить проблему лишних пробелов при поиске. Если справочную таблицу ещё можно вычистить от них, то первый параметр формулы ВПР не всегда зависит от вас. Поэтому если риск засорения ячеек лишними пробелами присутствует, то можно применять для очистки функции СЖПРОБЕЛЫ (TRIM).

Разный формат данных

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

=ВПР(−−D7; Продукты!$A$2:$C$5; 3; 0) — если D7 содержит текст, а таблица — числа;

=ВПР(D7 & «»); Продукты!$A$2:$C$5; 3; 0) — и наоборот.

Кстати, перевести текст в число можно сразу несколькими способами, выбирайте:

• Двойное отрицание —D7.
• Умножение на единицу D7*1.
• Сложение с нулём D7+0.
• Возведение в первую степень D7^1.

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

Как подавить выдачу #Н/Д

Это очень удобно делать при помощи функции ЕСЛИОШИБКА (IFERROR).

Например: =ЕСЛИОШИБКА( ВПР(D7; Продукты!$A$2:$C$5; 3; 0); «»).

Если ВПР вернёт код ошибки #Н/Д, то ЕСЛИОШИБКА его перехватит и подставит параметр 2 (в данном случае пустая строка), а если ошибки не произошло, то эта функция сделает вид, что её вообще нет, а есть только ВПР, вернувший нормальный результат.

Часто забывают ссылку массива сделать абсолютной, и при протягивании массив «плывёт». Помните, что вместо A2:C5 следует использовать $A$2:$C$5.

Хорошей идеей является размещение справочного массива на отдельном листе рабочей книги. Не путается под ногами, да и сохраннее будет.

Ещё более хорошей идеей будет объявление этого массива в виде именованного диапазона.

Многие пользователи при указании массива используют конструкцию вида A:C, указывая столбцы целиком. Этот подход имеет право на существование, так как вы избавлены от необходимости отслеживать тот факт, что ваш массив включает все необходимые строки. Если вы добавите строки на лист с первоначальным массивом, то диапазон, указанный как A:C, не придётся корректировать. Безусловно, эта синтаксическая конструкция заставляет Excel проводить несколько большую работу, чем при точном указании диапазона, но данными накладными расходами можно пренебречь. Речь идёт о сотых долях секунды.

Ну и на грани гениальности — оформить массив в виде умной таблицы.

Использование функции СТОЛБЕЦ для указания колонки извлечения

Если таблица, в которую вы извлекаете данные при помощи ВПР, имеет ту же самую структуру, что и справочная таблица, но просто содержит меньшее количество строк, то в ВПР можно использовать функцию СТОЛБЕЦ() для автоматического расчёта номеров извлекаемых столбцов

При этом все ВПР-формулы будут одинаковыми (с поправкой на первый параметр, который меняется автоматически)! Обратите внимание, что у первого параметра координата столбца абсолютная

Создание составного ключа через &»|»&

Если возникает необходимость искать по нескольким столбцам одновременно, то необходимо делать составной ключ для поиска. Если бы возвращаемое значение было не текстовым (как тут в случае с полем «Код»), а числовым, то для этого подошла бы более удобная формула СУММЕСЛИМН (SUMIFS) и составной ключ столбца не потребовался бы вовсе.

Поиск позиции в массивах с текстовыми значениями

Произведем поиск значения в сортированном списке текстовых значений (диапазон
А8:А15
). Список может содержать повторы. Искомое значение содержится в
А19
.

Формулы для вывода соответствующих значений
Адреса
и
ИНН
(форма массива)
=ПРОСМОТР(A19;A8:B15)
и
=ПРОСМОТР(A19;A8:С15)

Формулы для вывода соответствующих значений
Адреса
и
ИНН
(форма вектора)
=ПРОСМОТР(A19;A8:A15;B8:B15)
и
=ПРОСМОТР(A19;A8:A15;С8:С15)

В
файле примера
можно найти применение формул при наличии повторов в столбце поиска.

Как видно из картинки выше, в случае повторов в столбце поиска, функция
ПРОСМОТР()
выводит то значение, которое расположено ниже.

Вывод
: функция
ПРОСМОТР()
не имеет преимуществ по сравнению с функцией
ВПР()
.

Вторая версия функции ПРОСМОТР в Excel

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

Задача состоит в том, чтобы найти процент возврата, учитывая взаимосвязь между процентом и суммой кредита, а также рассчитать сумму возврата. Определим желаемые значения для клиента с фамилией Иванов. Для этого в ячейку С2 введите следующую формулу:

=ОТКРЫТЬ(B2,$A$12:$A$17,$B$12:$B$17)

Описание значений:

1. B2 — сумма кредита, который взял клиент.
2. $A$12:$A$17 – ряд сумм, в которых ищется соответствующее или ближайшее значение к искомому.
3. $B$12:$B$17 — это диапазон соответствующих процентов.

Примечание. Символ «$» используется для «исправления» ссылок на ячейки.

Результат расчета:

То есть Иванов получил кредит под 6% годовых.

Для определения суммы возврата введите формулу:

=В2+В2*С2

Описание значений:

1. Б2 — сумма (тело) кредита, взятого Ивановым;
2. B2*C2 — сумма процентов за пользование в денежном выражении.

Результат расчета:

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

Пример 3. В офисе 5 сотрудников разного возраста. Необходимо найти возраст Виталия.

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

Чтобы определить возраст самого молодого сотрудника, введите в ячейку E3 формулу:

=ПРОСМОТР(D3;A2:A6;B2:B6)

Описание значений:

1. D3 – фамилия сотрудника, возраст которого необходимо определить;
2. A2:A6 — вектор поиска имен;
3. B2:B6 — соответствующий вектор возраста.

Результат расчета:

Это означает, что сотруднику Виталия 43 года.

В этом примере мы видели две версии функции ПРОСМОТР с 2 и 3 аргументами для заполнения входящими данными.

Можно использовать только один критерий.

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

Это означает, что вы не можете легко сделать такие вещи, как поиск сотрудника с фамилией «Петров» в «Бухгалтерии» или поиск сотрудника на основе имени и фамилии, если они записаны в отдельных столбиках.

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

Об этом и других способах борьбы с этим ограничением читайте на нашем сайте в специальных инструкциях. Ссылки вы можете найти в конце этого материала.

Интервальный просмотр в функции ВПР

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

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

Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.

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

=ВПР(B2;$E$2:$F$5;2;1)

и скопируем вниз.

На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.

Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.

Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».

Функции ссылки и поиска

ВПР и ГПР

Excel предлагает ряд функций для поиска и извлечения данных. Самый известный из всех это ВПР (VLOOKUP):

ГПР (HLOOKUP) работает как ВПР (VLOOKUP), но рассчитывает данные, расположенные горизонтально:

ИНДЕКС и ПОИСКПОЗ

Для более сложных поисков ИНДЕКС (INDEX) и ПОИСКПОЗ (MATCH) предлагают больше гибкости и мощности:

И функция ИНДЕКС (INDEX), и функция ПОИСКПОЗ (MATCH) являются мощными функциями, которые встречаются во всех видах формул.

ПРОСМОТР

Настройки функции ПРОСМОТР (LOOKUP) «по умолчанию» делают ее очень полезной при решении определенных проблем. ПРОСМОТР (LOOKUP) предполагает, что значения отсортированы в порядке возрастания и всегда выполняет приблизительное совпадение. Когда ПРОСМОТР (LOOKUP) не может найти соответствие, оно будет соответствовать следующему наименьшему значению. В приведенном ниже примере мы используем ПРОСМОТР (LOOKUP), чтобы найти последнюю запись в столбце:

СТРОКА и СТОЛБЕЦ

Вы можете использовать функцию СТРОКА (ROW) и функцию СТОЛБЕЦ (COLUMN), чтобы найти номера строк и столбцов на листе

Обратите внимание, что СТРОКА (ROW) и СТОЛБЕЦ (COLUMN) возвращают значения для текущей ячейки, если ссылка не указана:

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

ЧСТРОК и ЧИСЛСТОЛБ

Функция ЧСТРОК (ROWS) и функция ЧИСЛСТОЛБ (COLUMNS) возвращают количество строк в ссылке. В приведенном ниже примере мы подсчитываем строки и столбцы в таблице Excel с именем «Таблица1».

ГИПЕРССЫЛКА

Вы можете использовать функцию ГИПЕРССЫЛКА (HYPERLINK), чтобы создать ссылку с формулой. ГИПЕРССЫЛКА (HYPERLINK) позволяет создавать как внешние, так и внутренние ссылки:

ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ

Функция ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) полезна для извлечения информации из существующих сводных таблиц.

ВЫБОР

Функция ВЫБОР (CHOOSE) всегда удобна, когда вам нужно сделать выбор на основе числа:

ТРАНСП

Функция ТРАНСП (TRANSPOSE) дает вам легкий способ перенести вертикальные данные в горизонтальные и наоборот.

СМЕЩ

Функция СМЕЩ (OFFSET) полезна для всех видов динамических диапазонов. Из начального местоположения он позволяет вам указать смещения строк и столбцов, а также окончательный размер строк и столбцов. Результатом является диапазон, который может динамически реагировать на изменяющиеся условия и входные данные. Вы можете передать этот диапазон другим функциям, как показано на экране ниже, где СМЕЩ (OFFSET) создает диапазон, который передается функции СУММ (SUM):

ДВССЫЛ

Функция ДВССЫЛ (INDIRECT) позволяет создавать ссылки в виде текста. Поначалу эту концепцию немного сложно понять, но она может быть очень полезна. Ниже мы используем ДВССЫЛ (INDIRECT) для получения значений из ячейки A1 в 5 различных листах. Каждая ссылка динамическая. Если имя листа изменится, ссылка будет обновлена.

Функция INDIRECT также используется для «блокировки» ссылок, чтобы они не изменялись при добавлении или удалении строк или столбцов.

Вторая версия функции ПРОСМОТР в Excel

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

Задача состоит в поиске процента возврата с учетом зависимости между процентом и суммой кредита, а также вычисление суммы возврата. Определим искомые величины для клиента с фамилией Иванов. Для этого в ячейке C2 введем следующую формулу:

=ПРОСМОТР(B2;$A$12:$A$17;$B$12:$B$17)

Описание значений:

1. B2 – сумма взятого клиентом кредита.
2. $A$12:$A$17 – массив сумм, среди которых производится поиск эквивалентного или ближайшего значения к искомому.
3. $B$12:$B$17 – массив соответствующих процентов.

Примечание: знак «$» использован для «фиксации» ссылок на ячейки.

Результат вычислений:

То есть, Иванову был выдан кредит под 6% годовых.

Для определения суммы возврата введем формулу:

=B2+B2*C2

Описание значений:

1. B2 – сумма (тело) кредита, взятого Ивановым;
2. B2*C2 – сумма процентов за использование в денежном эквиваленте.

Результат вычислений:

То есть, клиент Иванов обязан вернуть 127,2 денежных единиц. Подобным методом производится расчет задолженности для остальных клиентов.

Пример 3. В офисе работают 5 сотрудников различного возраста. Необходимо найти возраст Виталия.

Внесем исходные данные в таблицу:

Для определения возраста самого младшего сотрудника введем формулу в ячейке E3:

=ПРОСМОТР(D3;A2:A6;B2:B6)

Описание значений:

1. D3 – имя сотрудника, возраст которого необходимо определить;
2. A2:A6 – просматриваемый вектор имен;
3. B2:B6 – вектор соответствующих возрастов.

Результат вычислений:

Значит, возраст сотрудника Виталия составляет 43 года.

Функции Excel — простые примеры для начинающего

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

Сразу хотелось бы отметить, что все примеры будем рассматривать в Microsoft office 2010.

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

Вторая версия функции ПРОСМОТР в Excel

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

Задача состоит в поиске процента возврата с учетом зависимости между процентом и суммой кредита, а также вычисление суммы возврата. Определим искомые величины для клиента с фамилией Иванов. Для этого в ячейке C2 введем следующую формулу:

1. B2 – сумма взятого клиентом кредита.
2. $A$12:$A$17 – массив сумм, среди которых производится поиск эквивалентного или ближайшего значения к искомому.
3. $B$12:$B$17 – массив соответствующих процентов.

Примечание: знак «$» использован для «фиксации» ссылок на ячейки.

То есть, Иванову был выдан кредит под 6% годовых.

Для определения суммы возврата введем формулу:

1. B2 – сумма (тело) кредита, взятого Ивановым;
2. B2*C2 – сумма процентов за использование в денежном эквиваленте.

То есть, клиент Иванов обязан вернуть 127,2 денежных единиц. Подобным методом производится расчет задолженности для остальных клиентов.

Пример 3. В офисе работают 5 сотрудников различного возраста. Необходимо найти возраст Виталия.

Внесем исходные данные в таблицу:

Для определения возраста самого младшего сотрудника введем формулу в ячейке E3:

1. D3 – имя сотрудника, возраст которого необходимо определить;
2. A2:A6 – просматриваемый вектор имен;
3. B2:B6 – вектор соответствующих возрастов.

Значит, возраст сотрудника Виталия составляет 43 года.

В данном примере мы ознакомились с двумя версиями функции ПРОСМОТР на 2 и 3 аргумента для заполнения входящими данными.

Microsoft Excel Функция LOOKUP находит определенное значение в одном столбце или одном диапазоне строк и возвращает соответствующее значение из другого диапазона (одна строка или один столбец).

аргументы

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

Lookup_vector (обязательно): одна строка или один диапазон столбцов для поиска. Значением этого аргумента могут быть числа, текст или логические значения. Значения в этом диапазоне необходимо отсортировать по возрастанию..

result_vector (необязательно): функция ПРОСМОТР ищет значение в векторе look_up и возвращает результат из того же столбца или позиции строки в result_vector. Это данные из одной строки или одного столбца, которые имеют тот же размер, что и lookup_vector.

Примеры

Пример 1: Используйте функцию ПРОСМОТР, чтобы найти значение с одним критерием

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

Выберите пустую ячейку, скопируйте в нее формулу ниже и нажмите Enter ключ. =LOOKUP(H4,B3:E8,D3:D8)

Заметки:

• H4 — это ссылочная ячейка, содержащая поисковое значение «персик»; B3: E8 диапазон поиска, содержащий значение поиска и значение результата; D3: D8 — диапазон значений результата.
• Вы можете заменить H4 на «Peach» или B5 напрямую, если вам нужно.

Пример 2: функция ПРОСМОТР ищет значение с несколькими критериями

Как показано на скриншоте ниже, в разных отделах есть повторяющиеся имена. Для определения должности конкретного человека (говорит Фред Шулер) вам необходимо одновременно сопоставить критерии «Имя» и «Отдел». Пожалуйста, сделайте следующее.

1. Выберите пустую ячейку, чтобы поместить результат, скопируйте в нее формулу ниже и нажмите Enter ключ. =LOOKUP(1,0/((B2:B8=F4)*(C2:C8=G4)),D2:D8)

Заметки:

• В формуле B2: B8 и C2: C8 — диапазоны столбцов, содержащие первое и второе поисковые значения; F4 и G4 ссылки на ячейки, содержащие два критерия; D2: D8 — диапазон значений результата. Пожалуйста, измените их в зависимости от диапазона вашего стола.
• Эта формула также может помочь.=LOOKUP(1,0/((B2:B8&C2:C8=F4&G4)),D2:D8)

Kutools for Excel — поможет вам выделиться из толпы

Хотите быстро и безупречно выполнять свою повседневную работу? Kutools for Excel предлагает мощные расширенные функции 300 (объединение книг, сумма по цвету, разделение содержимого ячеек, дата преобразования и так далее . ) и экономия 80% времени для вас.

• Как разделить слипшийся текст в эксель

    

• Как сделать тех карту в ворде

    

• Как убрать интервал в ворде 2003

    

• 1с обновление регламентные отчеты 3 квартал 2008г

    

• 1с хранилище конфигурации как пользоваться