Что такое динамический просмотр в excel
Обновлено: 08.07.2024
Функция ПРОСМОТР( ) , английский вариант 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 .
Формулы для вывода соответствующих значений Адреса и ИНН (форма массива) : =ПРОСМОТР(A19;A8:B15) и =ПРОСМОТР(A19;A8:С15)
Формулы для вывода соответствующих значений Адреса и ИНН (форма вектора) : =ПРОСМОТР(A19;A8:A15;B8:B15) и =ПРОСМОТР(A19;A8:A15;С8:С15)
В файле примера можно найти применение формул при наличии повторов в столбце поиска.
Как видно из картинки выше, в случае повторов в столбце поиска, функция ПРОСМОТР() выводит то значение, которое расположено ниже.
Вывод : функция ПРОСМОТР() не имеет преимуществ по сравнению с функцией ВПР() .
Поиск позиции в массиве констант
Поиск значения можно производить не только в диапазонах ячеек, но и в массивах констант . Например, формула =ПРОСМОТР(22;;) вернет значение F . Такой вид записи удобен, когда стоит задача решаемая с помощью вложенных ЕСЛИ: если значение файл примера ).
Если функции ПРОСМОТР() не удается найти искомое_значение , то выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
Чтобы отобразить содержимое ячейки в фигуре, текстовом поле или элементе диаграммы, можно связать фигуру, текстовое поле или элемент диаграммы с ячейкой, содержаной данные, которые вы хотите отобразить. С помощью камеры можно также отобразить содержимое диапазона ячеев, связав диапазон ячеев с изображением.
Поскольку ячейка или диапазон ячеок связаны с графическим объектом, изменения, внося в данные в этой ячейке или диапазоне ячеок, автоматически появляются в графическом объекте.
Динамическое отображение содержимого ячейки в фигуре или текстовом поле на экранеЕсли на вашем компьютере нет фигуры или текстового поле, сделайте следующее:
Чтобы создать фигуру, на вкладке Вставка в группе Иллюстрации нажмите кнопку Фигуры ивыберите фигуру, которую вы хотите использовать.
Чтобы создать текстовое поле, на вкладке Вставка в группе Текст нажмите кнопку Текстовое поле инарисуйте текстовое поле нужного размера.
На сайте щелкните фигуру или текстовое поле, с которыми вы хотите связать содержимое ячейки.
Введите в панели формул знак равно(=).
Щелкните ячейку с данными или текстом, на которые вы хотите связаться.
Совет: Вы также можете ввести ссылку на ячейку на нем. Включив имя листа и восклицательный восклицательный пункт; Например, =Лист1! F2.
Нажмите клавишу ВВОД.
Содержимое ячейки отображается в выбранной фигуре или текстовом поле.
Примечание: Эту процедуру нельзя использовать в фигуре начертания, линии или соединителей.
Динамическое отображение содержимого ячейки в названии, подписи или текстовом поле на диаграммеНа диаграмме щелкните название, надпись или текстовое поле, которое вы хотите связать с ячейкой на листе, или выберите его из списка элементов диаграммы, вы можете сделать следующее:
Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.
На вкладке Формат в группе Текущий фрагмент щелкните стрелку рядом с полем Область диаграммы, а затем выберите нужный элемент диаграммы.
Введите в панели формул знак равно(=).
Вы можете выбрать ячейку с данными или текстом, которые должны отображаться в заголовке, подписи или текстовом поле на диаграмме.
Совет: Вы также можете ввести ссылку на ячейку на нем. Включив имя листа и восклицательный восклицательный пункт, например Лист1! F2
Нажмите клавишу ВВОД.
Содержимое ячейки отображается в выбранном заголовке, надписи или текстовом поле.
Динамическое отображение содержимого диапазона ячеев на рисункеЕсли вы еще не сделали этого, добавьте камеры на панель быстрого доступа.
Щелкните стрелку рядом с панелью инструментов и выберите пункт Другие команды.
В списке Выбрать команды из выберите пункт Все команды.
В списке выберите Камера, нажмите кнопку Добавитьи нажмите кнопку ОК.
Вы можете выбрать диапазон ячеек.
На панели быстрого доступа нажмите кнопку Камера .
Щелкните место на диаграмме или в том месте, куда нужно вставить изображение диапазона ячеев.
На рисунке отображается содержимое диапазона ячеев.
Чтобы отформатовать рисунок или сделать другие действия, щелкните его правой кнопкой мыши и выберите команду.
Например, можно щелкнуть команду Формат рисунка, чтобы изменить границу или сделать фон прозрачным.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Используйте функцию ПРОСМОТР, одну из функций поиска и работы со ссылками, когда нужно взять одну строку или столбец и найти значение, находящееся в той же позиции в другой строке или столбце.
Предположим, что вы знаете артикул детали автомобиля, но не знаете ее цену. Тогда, используя функцию ПРОСМОТР, вы сможете вернуть значение цены в ячейку H2 при вводе артикула в ячейку H1.
Используйте функцию ПРОСМОТР для поиска в одной строке или одном столбце. В приведенном выше примере рассматривается поиск цен в столбце D.
Советы: Рассмотрим одну из новых функций подытогов в зависимости от Office используемой версии.
Используйте функцию ВПР для поиска данных в одной строке или столбце, а также для поиска в нескольких строках и столбцах (например, в таблице). Это расширенная версия функции ПРОСМОТР. Посмотрите видеоролик о том, как использовать функцию ВПР.
Если вы используете Microsoft 365, используйте XLOOKUP — это не только быстрее, но и позволяет искать в любом направлении (вверх, вниз, влево, вправо).
Функцию ПРОСМОТР можно использовать двумя способами: в векторной форме и в форме массива.
Векторнаяформа: используйте эту форму просмотр для поиска значения в одной строке или в одном столбце. Используйте векторную форму, если нужно указать диапазон, содержащий значения, которые вы хотите найти. Например, если вы хотите найти значение в столбце A, вниз до строки 6.
Форма массива:мы настоятельно рекомендуем использовать вместо формы массива функции ВЛП или Г ПРОСМОТР. Просмотрите это видео об использовании ВКП. Форма массива обеспечивает совместимость с другими программами электронных таблиц, но ее функциональность ограничена.
Массив — это набор значений в строках и столбцах (например, в таблице), в которых выполняется поиск. Например, если вам нужно найти значение в первых шести строках столбцов A и B, это и будет поиском с использованием массива. Функция ПРОСМОТР вернет наиболее близкое значение. Чтобы использовать форму массива, сначала необходимо отсортировать данные.
Векторная форма
При использовании векторной формы функции ПРОСМОТР выполняется поиск значения в пределах только одной строки или одного столбца (так называемый вектор) и возврат значения из той же позиции второго диапазона.
Синтаксис
ПРОСМОТР(искомое_значение; просматриваемый_вектор; [вектор_результатов])
Функция ПРОСМОТР в векторной форме имеет аргументы, указанные ниже.
Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в первом векторе. Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
Просматриваемый_вектор Обязательный аргумент. Диапазон, состоящий из одной строки или одного столбца. Значения в аргументе просматриваемый_вектор могут быть текстом, числами или логическими значениями.
Важно: Значения в аргументе просматриваемый_вектор должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
result_vector Необязательный. Диапазон, содержащий только одну строку или столбец. Размер result_vector должен быть того же размера, что и lookup_vector. Размер должен быть одинаковым.
Замечания
Если функции ПРОСМОТР не удается найти искомое_значение, то в просматриваемом_векторе выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
Примеры векторов
Чтобы лучше разобраться в работе функции ПРОСМОТР, вы можете сами опробовать рассмотренные примеры на практике. В первом примере у вас должна получиться электронная таблица, которая выглядит примерно так:
Скопируйте данные из таблицы ниже и вставьте их в новый лист 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, а затем — ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
Форма массива
Совет: Мы настоятельно рекомендуем использовать вместо формы массива функции ВЛП или Г ПРОСМОТР. Посмотрите это видео о ВЛКП, в котором есть примеры. Форма массива функции ПРОСМОТР обеспечивает совместимость с другими программами электронных таблиц, но ее функциональность ограничена.
Форма массива функции ПРОСМОТР просматривает первую строку или первый столбец массив, находит указанное значение и возвращает значение из аналогичной позиции последней строки или столбца массива. Эта форма функции ПРОСМОТР используется, если сравниваемые значения находятся в первой строке или первом столбце массива.
Синтаксис
Функция ПРОСМОТР в форме массива имеет аргументы, указанные ниже.
Искомое_значение. Обязательный аргумент. Значение, которое функция ПРОСМОТР ищет в массиве. Аргумент искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой на значение.
Если функции ПРОСМОТР не удается найти искомое_значение, то в массиве выбирается наибольшее значение, которое меньше искомого_значения или равно ему.
Массив. Обязательный аргумент. Диапазон ячеек, содержащих текст, числа или логические значения, которые требуется сравнить с искомым значением.
Форма массива функции ПРОСМОТР очень похожа на функции ГПР и ВПР. Различие заключается в том, что функция ГПР ищет искомое_значение в первой строке, функция ВПР — в первом столбце, а функция ПРОСМОТР выполняет поиск в соответствии с размерностями массива.
Если ширина массива больше его высоты (т. е. есть массив имеет больше столбцов чем строк), то функция ПРОСМОТР ищет искомое_значение в первой строке.
Если высота массива больше его ширины (т. е. массив имеет больше строк, чем столбцов), то функция ПРОСМОТР выполняет поиск в первом столбце.
Используя функции ГПР и ВПР, можно указывать индекс по направлению вниз и вправо, а функция ПРОСМОТР всегда выбирает последнее значение в строке или столбце.
Важно: Значения в массиве должны быть расположены в порядке возрастания: . -2, -1, 0, 1, 2, . A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ПРОСМОТР может возвратить неправильный результат. Текст в нижнем и верхнем регистрах считается эквивалентным.
Динамические диаграммы используются в операциях, где необходимо автоматическое обновление для выбора динамического диапазона путем изменения данных, которые, в свою очередь, обновляют диаграмму или график. Аналогичным образом, динамические диаграммы в Excel обновляются или изменяются при изменении или удалении динамического диапазона данных, используемого в качестве исходных данных.
Для подготовки динамической диаграммы используются два основных метода:
- С помощью таблицы Excel
- Использование именованного диапазона
Как создать динамическую диаграмму в Excel?
Динамическая диаграмма в Excel очень проста и легка в создании. Давайте разберем работу Dynamic Chart в Excel на нескольких примерах.
Вы можете скачать этот шаблон Excel с динамической диаграммой здесь - Шаблон Excel с динамической диаграммой
Пример № 1 - с помощью таблицы Excel
Это один из самых простых способов создать динамическую диаграмму в Excel, которая доступна в версиях Excel 2007 года и более поздних версий. Основные шаги, которые необходимо выполнить:
- Создайте таблицу в Excel, выбрав опцию таблицы в Вставить
- Появится диалоговое окно для указания диапазона для таблицы и опции «Моя таблица имеет заголовки».
- Выберите таблицу и вставьте для нее подходящий график.
- Сложенная линия с диаграммой маркеров вставлена. Ваш график будет выглядеть так, как показано ниже:
- Измените данные в таблице и что в свою очередь изменит график.
На шаге 4 можно заметить, что после изменения динамического диапазона ввода столбца график автоматически обновляется. Нет необходимости менять график, который оказывается эффективным способом анализа данных.
Пример № 2 - с помощью именованного диапазона
Этот метод используется в сценарии, где пользователь использует более старую версию Excel, такую как Excel 2003. Он прост в использовании, но он не такой гибкий, как метод таблицы, используемый в excels для динамической диаграммы. Есть 2 шага в реализации этого метода:
- Создание динамического именованного диапазона для динамического графика.
- Создание диаграммы с использованием именованных диапазонов.
Создание динамического именованного диапазона для динамической диаграммы
На этом этапе функция OFFSET (Формула) используется для создания динамического именованного диапазона для конкретной динамической диаграммы, которая будет подготовлена. Эта функция OFFSET возвращает ячейку или диапазон ячеек, которые указаны для указанных строк и столбцов. Основные шаги, которые необходимо выполнить:
- Составьте таблицу данных, как показано в предыдущем методе.
- Диалоговое окно Диспетчер имен появится в этом Нажмите «Новый».
- В появившемся диалоговом окне из опции «Диспетчер имен» назначьте имя на вкладке для опции «Имя» и введите формулу OFFSET на вкладке «Относится к».
Так как в примере были взяты два имени (Player и Runs), будут определены два диапазона имен, и формула OFFSET для обоих имен будет иметь вид:
- Игрок = OFFSET ($ 4, 0, 0 $, COUNTA ($ A $ 4: $ A $ 100), 1)
- Пробеги = СМЕЩЕНИЕ ($ B $ 4, 0, 0, COUNTA ($ B $ 4: $ B $ 100), 1)
Используемая здесь формула также использует функцию COUNTA. Он получает счетчик количества непустых ячеек в целевом столбце, и счетчик переходит к аргументу высоты функции OFFSET, которая указывает количество возвращаемых строк. На шаге 1 метода 2 показано определение именованного диапазона для динамической диаграммы, где для двух имен создается таблица, а формула OFFSET используется для определения диапазона и создания диапазонов имен, чтобы сделать диаграмму динамичной.
Создание диаграммы с использованием именованных диапазонов
На этом этапе диаграмма выбирается и вставляется, а созданные диапазоны имен используются для представления данных и превращения диаграммы в динамическую диаграмму. Шаги, которые необходимо выполнить:
- Выберите всю диаграмму, либо щелкните правой кнопкой мыши или перейдите к параметру «Выбор данных», либо на вкладке «Дизайн» перейдите к параметру «Выбор данных».
- После выбора опции выбора данных появится диалоговое окно «Выбор источника данных». В этом нажмите на кнопку «Добавить».
- В опции «Добавить» появится другое диалоговое окно. Таким образом, на вкладке имени серии выберите имя, заданное для диапазона, и в качестве значения серии введите имя рабочего листа перед именованным диапазоном (метод 2! Выполнения). Нажмите ОК
- В метках оси диалоговое окно вводит имя листа и затем именованный диапазон (Method2! Player). Нажмите ОК.
- Дайте название диаграммы в качестве анализа соответствия.
После выполнения этих шагов создается динамическая диаграмма с использованием метода формулы, а также она обновляется автоматически после вставки или удаления данных.
Плюсы Динамической Диаграммы в Excel
- Динамический график - это эффективный по времени инструмент. Экономит время на автоматическом обновлении диаграммы всякий раз, когда новые данные добавляются к существующим данным.
- Быстрая визуализация данных обеспечивается в случае настройки для существующих данных.
- Используемая формула OFFSET преодолевает ограничения, видимые в VLOOKUP в Excel.
- Динамический график чрезвычайно полезен для финансового аналитика, который отслеживает данные компаний. Это помогает им понять тенденцию в показателях и финансовой устойчивости компании, просто вставив обновленный результат.
Минусы динамической диаграммы в Excel
- В случае нескольких сотен формул, используемых в книге Excel, это может повлиять на производительность Microsoft Excel по отношению к его пересчету, необходимому при изменении данных.
- Динамические диаграммы представляют информацию более простым способом, но также делают более сложные аспекты информации менее очевидными.
- Для пользователя, который не используется для использования Excel, пользователю может быть трудно понять функциональные возможности процесса.
- По сравнению с обычным графиком, динамическая обработка графиков утомительна и требует много времени
Что нужно помнить о динамической диаграмме в Excel
- При создании диапазонов имен для диаграмм не должно быть пробелов в данных таблицы или таблицы данных.
- Следует соблюдать соглашение об именах, особенно при создании диаграммы с использованием диапазонов имен.
- В случае первого метода, т.е. с использованием таблицы Excel, при каждом удалении данных диаграмма обновляется автоматически, но в правой части диаграммы будет пустое место. Поэтому в этом случае перетащите синюю метку внизу таблицы Excel.
Рекомендуемые статьи
Это было руководство по динамической диаграмме в Excel. Здесь мы обсудим его использование и как создать динамическую диаграмму в Excel с примерами Excel и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel -
Читайте также: