Как работает функция просмотра в Excel

Обновлено: 21.11.2024

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше

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

Чтобы получить подробную информацию о функции, щелкните ее название в первом столбце.

Примечание. Маркеры версий указывают версию Excel, в которой была введена функция. Эти функции недоступны в более ранних версиях. Например, маркер версии 2013 указывает, что эта функция доступна в Excel 2013 и всех более поздних версиях.

Описание

Возвращает ссылку в виде текста в одну ячейку на листе

Возвращает количество областей в ссылке

Выбирает значение из списка значений

Возвращает номер столбца ссылки

Возвращает количество столбцов в ссылке

Фильтрует диапазон данных на основе заданных вами критериев

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

Возвращает формулу по указанной ссылке в виде текста

Функция ПОЛУЧИТЬ.ДАННЫЕ

Возвращает данные, хранящиеся в отчете сводной таблицы

Просматривает верхнюю строку массива и возвращает значение указанной ячейки

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

Использует индекс для выбора значения из ссылки или массива

Возвращает ссылку, указанную текстовым значением

Ищет значения в векторе или массиве

Ищет значения в ссылке или массиве

Возвращает смещение ссылки от заданной ссылки

Возвращает номер строки ссылки

Возвращает количество строк в ссылке

Получает данные в режиме реального времени из программы, которая поддерживает автоматизацию COM

Сортирует содержимое диапазона или массива

Сортирует содержимое диапазона или массива на основе значений в соответствующем диапазоне или массиве

Возвращает транспонирование массива

Возвращает список уникальных значений в списке или диапазоне

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

Выполняет поиск в диапазоне или массиве и возвращает элемент, соответствующий первому найденному совпадению. Если совпадения не существует, XLOOKUP может вернуть ближайшее (приблизительное) совпадение.

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

Важно! Расчетные результаты формул и некоторых функций листа Excel могут незначительно отличаться на ПК с Windows, использующем архитектуру x86 или x86-64, и ПК с Windows RT, использующем архитектуру ARM. Узнайте больше о различиях.

Случалось ли вам когда-нибудь вместе с кем-то работать над листом, просматривая большой набор данных, и вдруг таблица уменьшалась, и вы не могли закончить свою работу? Это довольно разрушительно, не так ли?

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

Примечание. Представления листов в настоящее время ограничены файлами Excel 2007 или более поздних версий, хранящимися в OneDrive, OneDrive для бизнеса и SharePoint. Если вы сохраните локальную копию файла, содержащего представления листов, представления листов будут недоступны до тех пор, пока файл не будет сохранен в SharePoint и открыт из этой среды. Если вы добавите представления листов в книгу и сохраните ее как Excel 97-2003, представления листов будут удалены.

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

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

Добавить вид листа

Выберите рабочий лист, на котором вы хотите просмотреть лист, затем нажмите «Вид» > «Вид листа» > «Создать».

Примените нужную сортировку/фильтр. Excel автоматически называет новое представление временным представлением, чтобы указать, что представление листа еще не сохранено. Чтобы сохранить его, нажмите «Временный вид» в меню вида листа, введите имя нового вида листа и нажмите клавишу ВВОД.

Использование представления листа

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

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

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

При применении вида листа рядом с названием вкладки рабочего листа появляется символ глаза. При наведении курсора на глаз отображается имя активного вида листа.

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

Закрытие или переключение между представлениями листа

Чтобы закрыть вид листа и вернуться к виду по умолчанию, нажмите «Вид» > «Вид листа» > «Выход».

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

Удалить вид листа

Если вы решите, что вам больше не нужен определенный вид листа, нажмите «Вид» > «Виды листа» > «Параметры», выберите нужный вид и нажмите «Удалить».

Параметры просмотра листа

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

Часто задаваемые вопросы

Почему параметры просмотра листа отображаются серым цветом? Вы можете использовать представления листа только в документе, который хранится в папке SharePoint или OneDrive.

Является ли просмотр листа приватным и только для меня? Нет, другие люди, которые совместно используют книгу, могут видеть созданные вами представления, если они перейдут на вкладку "Вид" и просмотрят меню представления листа в группе "Представления листа".

Что произойдет, если активен вид листа, а я закрою файл и снова открою его? Любой активный вид листа автоматически сбрасывается на вид по умолчанию.

Можно ли создавать разные виды листа? Вы можете создать до 256 видов листов, но, вероятно, не хотите слишком усложнять.

Нужна дополнительная помощь?

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

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

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

В своей простейшей форме функция ВПР говорит:

=VLOOKUP(Что вы хотите найти, где вы хотите это найти, номер столбца в диапазоне, содержащем возвращаемое значение, возвращает приблизительное или точное совпадение — указывается как 1/ИСТИНА или 0/ЛОЖЬ ).

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

Используйте функцию ВПР для поиска значения в таблице.

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

Имя аргумента

Описание

искомое_значение (обязательно)

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

Например, если table-array охватывает ячейки B2:D7, то искомое_значение должно находиться в столбце B.

Искомое_значение может быть значением или ссылкой на ячейку.

table_array (обязательно)

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

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

col_index_num (обязательно)

Номер столбца (начиная с 1 для крайнего левого столбца table_array), который содержит возвращаемое значение.

range_lookup (необязательно)

Логическое значение, указывающее, хотите ли вы, чтобы функция ВПР находила приблизительное или точное совпадение:

Приблизительное соответствие — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован либо в числовом, либо в алфавитном порядке, после чего выполняется поиск ближайшего значения. Это метод по умолчанию, если вы его не укажете. Например, =ВПР(90,A1:B100,2,ИСТИНА).

Точное совпадение — 0/FALSE ищет точное значение в первом столбце. Например, =ВПР("Кузнец",A1:B100,2,ЛОЖЬ).

С чего начать

Для построения синтаксиса ВПР вам понадобятся четыре элемента информации:

Значение, которое вы хотите найти, также называемое значением поиска.

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

Номер столбца в диапазоне, который содержит возвращаемое значение. Например, если вы укажете диапазон B2:D11, вы должны считать B первым столбцом, C вторым и так далее.

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

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

=ВПР(искомое значение, диапазон, содержащий искомое значение, номер столбца в диапазоне, содержащем возвращаемое значение, приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).

Примеры

Вот несколько примеров ВПР:

Пример 1

Пример 2

Пример 3

Пример 4

Пример 5

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

Здесь столбцы AF и H имеют значения или формулы, которые используют только значения на листе, а остальные столбцы используют функцию ВПР и значения столбца A (код клиента) и столбца B (поверенный) для получения данных из других источников. таблицы.

Скопируйте таблицу с общими полями на новый лист и дайте ей имя.

Нажмите «Данные» > «Инструменты данных» > «Взаимосвязи», чтобы открыть диалоговое окно «Управление взаимосвязями».

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

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

Имя связанной таблицы поиска. Это table_array в вашей формуле ВПР.

Поле (столбец) в связанной таблице поиска, которое содержит данные, которые вы хотите добавить в новый столбец. Эта информация не отображается в диалоговом окне «Управление отношениями» — вам придется просмотреть связанную таблицу поиска, чтобы увидеть, какое поле вы хотите получить.Вы хотите отметить номер столбца (A=1) — это col_index_num в вашей формуле.

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

В нашем примере в столбце G используется Attorney (lookup_value), чтобы получить данные о ставке счета из четвертого столбца (col_index_num = 4) из таблицы Attorneys. tblAttorneys (table_array) с формулой =VLOOKUP([@Attorney],tbl_Attorneys,4,FALSE).

В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВПР(A2,'Адвокаты'!A:D,4,FALSE).

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

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

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

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

Советы. Рассмотрите одну из новых функций поиска, в зависимости от используемой версии Office.

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

Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет выполнять поиск в любом направлении (вверх, вниз, влево, вправо).

Есть два способа использования ПРОСМОТРА: векторная форма и форма массива

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

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

Массив — это набор значений в строках и столбцах (например, в таблице), в которых вы хотите выполнить поиск. Например, если вы хотите выполнить поиск в столбцах A и B, до строки 6. ПРОСМОТР вернет ближайшее совпадение. Чтобы использовать форму массива, ваши данные должны быть отсортированы.

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

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

Синтаксис

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

Синтаксис векторной формы функции ПРОСМОТР имеет следующие аргументы:

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

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

Важно: значения в lookup_vector должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

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

Примечания

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

Векторные примеры

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

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

Скопируйте эти данные в столбец A

Скопируйте эти данные в столбец B

Затем скопируйте формулы ПРОСМОТР из следующей таблицы в столбец 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.

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

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

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

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

Синтаксис

Синтаксис формы массива функции ПРОСМОТР имеет следующие аргументы:

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

Если ПРОСМОТР не может найти значение искомое_значение, используется наибольшее значение в массиве, которое меньше или равно искомое_значение.

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

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

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

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

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

Важно: значения в массиве должны располагаться в порядке возрастания: . -2, -1, 0, 1, 2, . AZ, ЛОЖЬ, ИСТИНА; в противном случае ПРОСМОТР может не вернуть правильное значение. Текст в верхнем и нижнем регистре эквивалентен.

Читайте также: