Как сделать функцию просмотр в excel?

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

Применение оператора ПРОСМОТР

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

Способ 1: векторная форма

Данный способ наиболее часто применим среди пользователей при использовании оператора ПРОСМОТР.

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



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



3. Открывается окно Мастера функций. В перечне ищем элемент «ПРОСМОТР» выделяем его и кликаем по кнопке «OK».



4. Далее открывается дополнительное окно. У других операторов оно редко встречается. Тут нужно выбрать одну из форм обработки данных, о которых шёл разговор выше: векторную или форму массива. Так как мы сейчас рассматриваем именно векторный вид, то выбираем первый вариант. Жмем на кнопку «OK».



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

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



6. В поле «Просматриваемый вектор» указываем диапазон ячеек, а в нашем случае тот столбец, где находятся наименования, одно из которых будем записывать в ячейке «Искомое значение». Вносить координаты в это поле также легче всего путем выделения области на листе.



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



8. После того, как все данные введены, жмем на кнопку «OK».



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

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

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

Урок: Мастер функций в Excel

Способ 2: форма массива

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

1. После того, как выбрана ячейка, куда будет выводиться результат, запущен Мастер функций и сделан переход к оператору ПРОСМОТР, открывается окно для выбора формы оператора. В данном случае выбираем вид оператора для массива, то есть, вторую позицию в перечне. Жмем «OK».
2. Открывается окно аргументов. Как видим, данный подтип функции имеет всего два аргумента – «Искомое значение» и «Массив». Соответственно её синтаксис следующий:

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

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

3. А вот в поле «Массив» нужно указать координаты всего массива, в котором находится как просматриваемый диапазон, так и диапазон результатов. При этом, просматриваемый диапазон обязательно должен быть крайней левой колонкой массива, иначе формула будет работать некорректно.
4. После того, как указанные данные введены, жмем на кнопку «OK».
5. Теперь, как и в прошлый раз, для того, чтобы использовать данную функцию, в ячейку для искомого значения вводим одно из наименований просматриваемого диапазона.

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

Внимание! Нужно заметить, что вид формулы ПРОСМОТР для массива является устаревшим. В новых версиях Excel он присутствует, но оставлен только в целях совместимости с документами, сделанными в предыдущих версиях.

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

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

Как видим, оператор ПРОСМОТР является отличным помощником при поиске данных по искомому значению. Особенно эта возможность полезна в длинных таблицах. Также следует заметить, что существуют две формы этой функции – векторная и для массивов. Последняя из них является уже устаревшей. Хотя некоторыми пользователями она применяется до сих пор.

Мы рады, что смогли помочь Вам в решении проблемы.

Опишите, что у вас не получилось.
Наши специалисты постараются ответить максимально быстро.

Помогла ли вам эта статья?

ДА НЕТ

Источник: https://lumpics.ru/function-view-in-excel/

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

В этом уроке мы познакомимся с функцией ПРОСМОТР, которая позволяет извлекать нужную информацию из электронных таблиц Excel.

На самом деле, Excel располагает несколькими функциями по поиску информации в книге, и каждая из них имеет свои преимущества и недостатки.

Далее Вы узнаете в каких случаях следует применять именно функцию ПРОСМОТР, рассмотрите несколько примеров, а также познакомитесь с ее вариантами записи.

ВАРИАНТЫ ЗАПИСИ ФУНКЦИИ ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

ФОРМА МАССИВА

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

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

ВЕКТОРНАЯ ФОРМА

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

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

ПРИМЕР 1

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

В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР. Формула будет выглядеть следующим образом:

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор.

Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter, убеждаемся, что все верно.

ПРИМЕР 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

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

ФУНКЦИИ ИНДЕКС И ПОИСКПОЗ В EXCEL НА ПРОСТЫХ ПРИМЕРАХ

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

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

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

Более подробно о функциях ВПР и ПРОСМОТР.

ФУНКЦИЯ ПОИСКПОЗ В EXCEL

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

Например, на рисунке ниже формула вернет число 5, поскольку имя «Дарья» находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

· — функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.

· 1 или вовсе опущено — функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.

· -1 — функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

• В одиночку функция ПОИСКПОЗ, как правило, не представляет особой ценности, поэтому в Excel ее очень часто используют вместе с функцией ИНДЕКС.
• ФУНКЦИЯ ИНДЕКС В EXCEL

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

Если массив содержит только одну строку или один столбец, т.е. является вектором, то второй аргумент функцииИНДЕКС указывает номер значения в этом векторе. При этом третий аргумент указывать необязательно.

• Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):
• Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):
• СОВМЕСТНОЕ ИСПОЛЬЗОВАНИЕ ПОИСКПОЗ И ИНДЕКС В EXCEL

Если Вы уже работали с функциями ВПР, ГПР и ПРОСМОТР в Excel, то должны знать, что они осуществляют поиск только в одномерном массиве.

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

Именно в таких случаях связка ПОИСКПОЗ и ИНДЕКС в Excel оказывается просто незаменимой.

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

1. =ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))
2. Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:
3. В данной формуле функция ИНДЕКС принимает все 3 аргумента:

1. Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.

2. Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функцииПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:

3. Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функцииПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

• Если подставить в исходную громоздкую формулу вместо функций ПОИСКПОЗ уже вычисленные данные из ячеек D15 и D16, то формула преобразится в более компактный и понятный вид:
• =ИНДЕКС(B2:E13;D15;D16)

Источник: https://studopedia.net/1_40027_funktsiya-prosmotr-v-EXCEL-na-prostom-primere.html

Excel-лайфхаки для тех, кто занимается отчётностью и обработкой данных

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

В этой статье собраны несложные приёмы, позволяющие упростить работу в Excel.

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Функции ВПР (VLOOKUP) и ГПР (HLOOKUP) работают только в том случае, если искомые значения находятся в первом столбце или строке той таблицы, из которой вы планируете получить данные.

В остальных случаях есть два варианта:

1. Использовать функцию ПРОСМОТР (LOOKUP).
   У неё следующий синтаксис: ПРОСМОТР (искомое_значение; вектор_просмотра; вектор_результата). Но для её корректной работы нужно, чтобы значения диапазона вектор_просмотра были отсортированы по возрастанию:
2. Использовать сочетание функций ПОИСКПОЗ (MATCH) и ИНДЕКС (INDEX).
   Функция ПОИСКПОЗ возвращает порядковый номер элемента в массиве (с её помощью вы можете найти, в какой строке таблицы искомый элемент), а функция ИНДЕКС возвращает элемент массива с заданным номером (который мы и узнаем с помощью функции ПОИСКПОЗ).Синтаксис функций:
   • ПОИСКПОЗ (искомое_значение; массив_поиска; тип_сопоставления) — для нашего случая нам нужен тип сопоставления «точное сопоставление», ему соответствует цифра 0.
   • ИНДЕКС (массив; номер_строки; [номер_столбца]). В данном случае номер столбца указывать не нужно, так как массив состоит из одной строки.

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

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

Выделяем столбец «Тематика», нажимаем на ленте в группе «Главная» кнопку «Найти и выделить» → «Выделить группу ячеек» → «Пустые ячейки» и начинаем ввод формулы (то есть ставим знак равно) и ссылаемся на ячейку сверху, просто нажимая стрелку вверх на клавиатуре. После этого нажимаем Ctrl + Enter. После этого можно сохранить полученные данные как значения, так как формулы больше не нужны:

Как найти ошибки в формуле

Вычисление отдельной части формулы

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

1. Чтобы вычислить часть формулы прямо в строке формул, выделите эту часть и нажмите F9:
   В данном примере была проблема с функцией ПОИСК (SEARCH) — в ней были перепутаны местами аргументы. Важно помнить, что если вы не отмените вычисление части функции и нажмёте Enter, то вычисленная часть так и останется числом.
2. Нажмите на кнопку «Вычислить формулу» в группе «Формулы» на ленте:

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

Как определить, от чего зависит или на что ссылается формула

• Чтобы определить, от каких ячеек зависит формула, в группе «Формулы» на ленте нажмите на кнопку «Влияющие ячейки»:
• 
• Появляются стрелки, указывающие, от чего зависит результат вычислений.
• Если отображается символ, выделенный на картинке красным цветом, то формула зависит от ячеек, находящихся на других листах или в других книгах:
• 
• Щёлкнув на него, мы увидим, где именно находятся влияющие ячейки или диапазоны:
• 
• Рядом с кнопкой «Влияющие ячейки» находится кнопка «Зависимые ячейки», работающая аналогично: она отображает стрелки от активной ячейки с формулой к ячейкам, которые зависят от неё.
• Кнопка «Убрать стрелки», расположенная в том же блоке, позволяет убрать стрелки к влияющим ячейкам, стрелки к зависимым ячейкам или же оба типа стрелок сразу:

Как найти сумму (количество, среднее) значений ячеек с нескольких листов

1. Допустим, у вас есть несколько однотипных листов с данными, которые вы хотите сложить, посчитать или обработать как-то иначе:
2. Для этого в ячейку, в которой вы хотите видеть результат, введите стандартную формулу, например СУММ (SUM), и укажите в аргументе через двоеточие название первого и последнего листов из списка тех листов, что вам нужно обработать:
3. Вы получите сумму ячеек с адресом B3 с листов «Данные1», «Данные2», «Данные3»:

Такая адресация работает для листов, расположенных последовательно. Синтаксис следующий: =ФУНКЦИЯ (первый_лист:последний_лист!ссылка на диапазон).

Как автоматически строить шаблонные фразы

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

• Объединяем текст с помощью знака & (можете заменить его функцией СЦЕПИТЬ (CONCATENATE), но в этом нет особого смысла).
• Текст всегда записывается в кавычках, ссылки на ячейки с текстом — всегда без.
• Чтобы получить служебный символ «кавычки», используем функцию СИМВОЛ (CHAR) с аргументом 32.

• Пример создания шаблонной фразы с помощью формул:
• Результат:
• В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:
• ТЕКСТ (значение; формат)
• Формат указывается в кавычках точно так же, как если бы вы вводили пользовательский формат в окне «Формат ячеек».

Автоматизировать можно и более сложные тексты.

В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

1. При объединении ячеек сохраняется только одно значение.

   Excel предупреждает об этом при попытке объединить ячейки:

2. Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):
3. Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

• Если вам нужно построить сводную сразу из нескольких источников данных, придётся добавить на ленту или панель быстрого доступа «Мастер сводных таблиц и диаграмм», в котором есть такая опция.
• Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:
• После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:
• При щелчке на неё появляется диалоговое окно:

В нём вам необходимо выбрать пункт «В нескольких диапазонах консолидации» и нажать «Далее». В следующем пункте можно выбрать «Создать одно поле страницы» или «Создать поля страницы». Если вы хотите самостоятельно придумать имя для каждого из источников данных — выберите второй пункт:

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

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

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

1. ДЛСТР (LEN) — вычисляет длину текста, единственный аргумент — текст. Пример: ДЛСТР (“машина”) = 6.
2. ПОДСТАВИТЬ (SUBSTITUTE) — заменяет в текстовой строке определённый текст другим. Синтаксис: ПОДСТАВИТЬ (текст; стар_текст; нов_текст). Пример: ПОДСТАВИТЬ (“автомобиль”;“авто”;“”)= “мобиль”.
3. ПРОПИСН (UPPER) — заменяет все символы в строке на прописные. Единственный аргумент — текст. Пример: ПРОПИСН (“машина”) = “МАШИНА”. Эта функция понадобится нам, чтобы делать поиск без учёта регистра. Ведь ПРОПИСН(“машина”)=ПРОПИСН(“Машина”)

1. Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:
2. ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6
3. А затем разделить эту разницу на длину той строки, которую мы искали:
4. 6 / ДЛСТР (“МТС”) = 2
5. Именно два раза строка «МТС» входит в исходную.
6. Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):
7. =(ДЛСТР(текст)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст);ПРОПИСН(искомый);“”)))/ДЛСТР(искомый)
8. В нашем примере формула выглядит следующим образом:
9. =(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)

Источник: https://Lifehacker.ru/8-priyomov-raboty-v-excel/

Функция ПРОСМОТР в Excel на простом примере — Microsoft Excel для начинающих

В этом уроке мы познакомимся с функцией ПРОСМОТР, которая позволяет извлекать нужную информацию из электронных таблиц Excel.

На самом деле, Excel располагает несколькими функциями по поиску информации в книге, и каждая из них имеет свои преимущества и недостатки.

Далее Вы узнаете в каких случаях следует применять именно функцию ПРОСМОТР, рассмотрите несколько примеров, а также познакомитесь с ее вариантами записи.

Варианты записи функции ПРОСМОТР

Начнем с того, что функция ПРОСМОТР имеет две формы записи: векторная и массив. Вводя функцию на рабочий лист, Excel напоминает Вам об этом следующим образом:

Форма массива

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

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

Векторная форма

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

Вот это да! Это ж надо такое понаписать… Чтобы стало понятней, рассмотрим небольшой пример.

Пример 1

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

В данном примере функцию ВПР не применить, поскольку просматриваемый столбец не является крайним левым. Именно в таких случаях можно использовать функцию ПРОСМОТР. Формула будет выглядеть следующим образом:

Первым аргументом функции ПРОСМОТР является ячейка C1, где мы указываем искомое значение, т.е. фамилию. Диапазон B1:B7 является просматриваемым, его еще называют просматриваемый вектор.

Из соответствующей ячейки диапазона A1:A7 функция ПРОСМОТР возвращает результат, такой диапазон также называют вектором результатов. Нажав Enter, убеждаемся, что все верно.

Пример 2

Функцию ПРОСМОТР в Excel удобно использовать, когда векторы просмотра и результатов относятся к разным таблицам, располагаются в отдаленных частях листа или же вовсе на разных листах. Самое главное, чтобы оба вектора имели одинаковую размерность.

На рисунке ниже Вы можете увидеть один из таких примеров:

Как видите, диапазоны смещены друг относительно друга, как по вертикали, так и по горизонтали, но формула все равно вернет правильный результат. Главное, чтобы размерность векторов совпадала. Нажав Enter, мы получим требуемый результат:

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

Так вот коротко и на примерах мы познакомились с функцией ПРОСМОТР и научились использовать ее в рабочих книгах Excel. Надеюсь, что данная информация оказалась для Вас полезной, и Вы обязательно найдете ей применение. Всего Вам доброго и успехов в изучении Excel.

Оцените качество статьи. Нам важно ваше мнение:

Источник: https://office-guru.ru/excel/funkcija-prosmotr-v-excel-na-prostom-primere-83.html

Функция ПРОСМОТР() в MS EXCEL

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

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

Перед использованием функции ПРОСМОТР() убедитесь, что диапазон значений, по которым будет производиться поиск, ОТСОРТИРОВАН ПО ВОЗРАСТАНИЮ, иначе функция может вернуть неправильный результат. Если это невозможно, рекомендуется использовать функции ВПР() и ПОИСКПОЗ() совместно с функцией ИНДЕКС().

Синтаксис функции

• Существует 2 формы задания аргументов функции ПРОСМОТР(): форма массива и форма вектора.
• Форма массива
• Форма массива функции ПРОСМОТР() просматривает первый (левый) столбец таблицы и, если находит искомое значение, возвращает значение из соответствующей строки самого правого столбца таблицы (массива).
• ПРОСМОТР(искомое_значение; массив)

Формула =ПРОСМОТР(«яблоки»; A2:B10) просматривает диапазон ячеек А2:А10.

Если, например, в ячейке А5 содержится искомое значение «яблоки», то формула возвращает значение из ячейки B5, т.е. из соответствующей ячейки самого правого столбца таблицы (B2:B10). Внимание! Значения в диапазоне А2:А10 должны быть отсортированы по возрастанию.

Если функцииПРОСМОТР() не удается найти искомое_значение, то выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Функция ПРОСМОТР() — также имеет векторную форму. Вектор представляет собой диапазон ячеек, размещенный в одном столбце или одной строке.

ПРОСМОТР(искомое_значение; просматриваемый_вектор; вектор_результатов)

Формула =ПРОСМОТР(«яблоки»; A2:A10; B2:B10) просматривает диапазон ячеек А2:А10. Если, например, в ячейке А5 содержится искомое значение «яблоки», то формула возвращает значение из ячейки B5, т.е.

из соответствующей ячейки самого правого столбца таблицы (B2:B10). Внимание! Значения в диапазоне А2:А10 должны быть отсортированы по возрастанию.

Если функции ПРОСМОТР() не удается найти искомое_значение, то выбирается наибольшее значение, которое меньше искомого_значения или равно ему.

Если функция ПРОСМОТР() не находит соответствующего значения, то возвращается значение ошибки #Н/Д.

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

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

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

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

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

Поиск позиции в массиве констант

Поиск значения можно производить не только в диапазонах ячеек, но и в массивах констант. Например, формула =ПРОСМОТР(22;{0;60;70;80;90};{«F»;»D»;»C»;»B»;»A»}) вернет значение F. Такой вид записи удобен, когда стоит задача решаемая с помощью вложенных ЕСЛИ: если значение

Источник: https://excel2.ru/articles/funkciya-prosmotr-v-ms-excel-prosmotr

Функция Просмотр()

Массив
– это группа ячеек или значений, который
EXCEL обрабатывает как единый модуль.
Иначе, это какой-то объем данных. Многие
функции EXCEL либо используют массив в
качестве аргумента, либо возвращают
массив как результат. Функция Просмотр()
использует массив в качестве аргумента.

Функция
Просмотр()
возвращает значение из строки, из столбца
или из массива. Функция Просмотр()
имеет две синтаксические формы: векторнаяи
форма массива.

Векторная форма функции Просмотр

Пример

Предприятию
выдается кредит. В зависимости от суммы
кредита начисляется процент за
использование кредита (таблица Шкала).
Сумма возврата складывается из суммы
кредита и суммы процента за пользование
кредитом. Величина процента за
использование кредита рассчитывается
с помощью функции Просмотр()
(см.
рис.26).

Для
расчета процента за использование
кредита для Предприятия1 вводится
формула:

=ПРОСМОТР(B3;$A$14:$A$19;$B$14:$B$19)

Рисунок
26. Функция Просмотр

Вектор
— это диапазон, который содержит только
одну строку или один столбец.

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

ПРОСМОТР(искомое_значение;вектор_просмотра;вектор_результата)

Искомое_значение
— это значение, которое ПРОСМОТР()
ищет в векторе просмотра. Искомое_значение
может быть числом, текстом, логическим
значением, именем или ссылкой, ссылающимися
на значение.

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

Ограничения:

• Значения в аргументе Вектор_просмотра должны быть упорядочены, т.е. расположены в порядке возрастания: …, -2, -1, 0, 1, 2, …, A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР() может вернуть неверный результат. Тексты в нижнем и верхнем регистре считаются эквивалентными.
• Вектор_результата— это интервал, содержащий только одну строку или один столбец. Он должен быть того же размера, что и Вектор просмотра.

Особенности:

• Если ПРОСМОТР не может найти Искомое_значение, то подходящим считается наибольшее значение в аргументе Вектор_просмотра, которое меньше, чем Искомое_значение.
• Если Искомое_значение меньше, чем наименьшее значение в аргументе Вектор_просмотра, то функция ПРОСМОТР возвращает значение ошибки #Н/Д.
• Обратить внимание на использование абсолютной адресацией, использование имен диапазонов в формуле.

Форма
массива функции Просмотр()

Форма
массива функции ПРОСМОТР()
просматривает первую строку или первый
столбец массива, находит указанное
значение и возвращает значение из
аналогичной позиции последней строки
или столбца массива.

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

• Совет:
• Лучше
  использовать функции ГПР()
  или ВПР()
  вместо функции ПРОСМОТР(),
  используемой в
  форме массива.
• ПРОСМОТР(искомое_значение;массив)

Искомое_значение
— это значение, которое ПРОСМОТР ищет в
массиве. Искомое_значение может быть
числом, текстом, логическим значением,
именем или ссылкой, ссылающимися на
значение.

Особенности:

• Если ПРОСМОТР() не может найти искомое_значение, то подходящим считается наибольшее значение в аргументе массив, которое меньше, чем искомое_значение.
• Если искомое_значение меньше, чем наименьшее значение в первой строке или первом столбце (в зависимости от размерности массива), то функция ПРОСМОТР() возвращает значение ошибки #Н/Д.
• Форма массива функции ПРОСМОТР() очень похожа на функции ГПР() и ВПР.() Различие заключается в том, что функция ГПР() ищет искомое_значение в первой строке, функция ВПР() ищет в первом столбце, а функция ПРОСМОТР ищет в соответствии с размерностями массива.
• Если ширина массива больше его высоты (то есть имеет больше столбцов чем строк), то функция ПРОСМОТР() ищет искомое_значение в первой строке.
• Если массив больше в высоту, чем в ширину (то есть имеет больше строк, чем столбцов), то функция ПРОСМОТР() ищет в первом столбце.
• Используя функции ГПР() и ВПР() можно указывать индекс по направлению вниз и вправо, а функция ПРОСМОТР() всегда выбирает последнюю ячейку в строке или столбце.

Источник: https://studfile.net/preview/5715531/page:14/

Создаем свою первую функцию в Excel

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

1. В качестве формулы на листе, где она может принимать аргументы и возвращать значения.
2. Как часть вашей подпрограммы VBA. В процедуре Sub или внутри других функций.
3. В правилах условного форматирования.

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

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

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

Основное отличие в том, что процедура (sub) используется для выполнения набора команд, и не призвана в отличие от функции (function) возвращать  значение (или массив значений).

Для демонстрации приведем пример. Например есть ряд чисел от 1 до 100 и необходимо отделить четные от нечетных.

С помощью процедуры (sub) вы можете, к примеру, пройтись по ячейкам и выделить нечетные с помощью заливки. А функцию можно использовать в соседнем столбце и она вернет ИСТИНА или ЛОЖЬ в зависимости от того четное значение или нет. Т.е. вы не сможете изменить цвет заливки с помощью функции на листе.   

Создание простой пользовательской функции в VBA

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

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

Function Цифры(Текст As String) As Long
Dim i As Long
Dim result As String

For i = 1 To Len(Текст)
If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)
Next

Цифры = CLng(result)
End Function

Чтобы у вас все заработало, необходимо вставить данный код в модуль книги. Если вы не знаете как это сделать, то начните со статьи Как записать макрос в Excel.

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

• Она стала доступна, как и любая другая встроенная функция (как создать скрытую функцию, расскажем далее).
• Когда вы ввели знак «=» и начинаете вводить имя функции, то Excel выводит все совпадения и показывает не только встроенные функции, но и пользовательские.

Разбираем функцию пошагово

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

Function Цифры(Текст As String) As Long

Слово Function говорит о начале функции, далее идет ее название, в нашем случае Цифры.

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

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

После названия аргумента мы указали As String, это означает, что наш аргумент — текстовое значение или ссылка на ячейку, содержащее текстовое значение.

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

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

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

Dim i As Long
Dim result As String

Переменную i мы буем использовать для перебора символов. А переменную result для хранения промежуточного результата функции.

Задача функции — пройти по всем символам переменной Текст и сохранить только те, что являются цифрами. Поэтому начнем цикл с 1 и до последнего символа.

For i = 1 To Len(Текст)

Основная строка функции — это проверка является ли очередной символ текста цифрой и если да — то сохранение его в переменной result

If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1)

• Для этого нам потребуется функция IsNumeric — она возвращает True если текст — число и False в противном случае.
• Функция Mid берет из аргумента Текст i-ый символ (значение 1, указывает что функция Mid берет только 1 символ)/
• Функция Next — закрывает цикл For тут все понятно.

Цифры = CLng(result)

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

Последняя строка кода — End Function. Это обязательная строка кода, которая сообщает VBA, что код функции заканчивается здесь.

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

Скачать

Источник: https://micro-solution.ru/excel/vba/create-user-functions