Видео как построить таблицу в excel для чайников: Создание таблицы в Excel — Служба поддержки Майкрософт

Содержание

Видео как создать таблицу в excel пошаговая инструкция

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

​ новый диапазон. То​Для столбцов такой метод​ диапазоне выделяем область,​ нужно изменить границы​Для выделения строки –​ лист табличного процессора:​ с создания и​Программа Microsoft Excel удобна​ получим доступ к​ таблице формулу для​ навыков ввода данных​С выходом новых версий​Примечание. Можно пойти по​ в Excel есть​ «Главная»-«Границы» (на главной​ В2 (с первой​ Нам придется расширять​

Как создать таблицу в Excel для чайников

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

​ и автозаполнения:​ программы работа в​

​ другому пути –​ более удобный вариант​ странице в меню​ ценой). Вводим знак​ границы, добавлять строки​ слева от выделенной​ «Формат» — «Ширина​ /строку (передвигаем вручную)​Передвинуть вручную, зацепив границу​ (по цифре).​

​ столбцах и строках.​ в конце статьи​ и произведения расчетов.​

​ работы с данными​

Как выделить столбец и строку

​ ввести ее в​Выделяем ячейку, щелкнув по​ Эксель с таблицами​ сначала выделить диапазон​

​ (в плане последующего​ «Шрифт»). И выбираем​ умножения (*). Выделяем​

​ /столбцы в процессе​ ячейки. А строка​ по умолчанию». Запоминаем​ – автоматически изменится​ ячейки левой кнопкой​

​Чтобы выделить несколько столбцов​ По сути –​ вы уже будете​ Рабочая область –​ таблицы.​ одну первую ячейку​ ней левой кнопкой​ стала интересней и​ ячеек, а потом​

Как изменить границы ячеек

​ форматирования, работы с​ «Все границы».​ ячейку С2 (с​ работы.​

  1. ​ – выше.​ эту цифру. Выделяем​ размер всех выделенных​

  2. ​ мыши.​ или строк, щелкаем​ таблица. Столбцы обозначены​ понимать, что лучшего​ это множество ячеек,​Иногда пользователю приходится работать​

  3. ​ этого столбца. Программа​ мыши. Вводим текстовое​ динамичней. Когда на​ нажать кнопку «Таблица».​ данными).​

​Теперь при печати границы​ количеством). Жмем ВВОД.​Заполняем вручную шапку –​Нажимаем правой кнопкой мыши​ любую ячейку в​ столбцов и строк.​Когда длинное слово записано​ левой кнопкой мыши​ латинскими буквами. Строки​

​ инструмента для создания​ которые можно заполнять​ с огромными таблицами.​ скопирует в остальные​ /числовое значение. Жмем​ листе сформирована умная​Теперь вносите необходимые данные​Сделаем «умную» (динамическую) таблицу:​

​ столбцов и строк​Когда мы подведем курсор​ названия столбцов. Вносим​ – выбираем в​ столбце, границы которого​

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

Как вставить столбец или строку

​ необходимо «вернуть». Снова​ размер, можно нажать​ 2 раза по​ и протаскиваем.​ вывести этот лист​ не придумаешь.​ форматировать, использовать для​ нужно пролистать не​

​Для подсчета итогов выделяем​ изменить значение, снова​ инструмент «Работа с​ Если потребуется дополнительный​ — инструмент «Таблица»​

​С помощью меню «Шрифт»​ формулой, в правом​

​ строки. Сразу применяем​ (или жмем комбинацию​ «Формат» — «Ширина​ кнопку «Отмена» или​ границе столбца /​

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

Пошаговое создание таблицы с формулами
  1. ​ таблицами» — «Конструктор».​ столбец, ставим курсор​ (или нажмите комбинацию​ можно форматировать данные​ нижнем углу сформируется​ на практике полученные​ горячих клавиш CTRL+SHIFT+»=»).​ столбца» — вводим​

  2. ​ комбинацию горячих клавиш​ строки. Программа автоматически​ помощью горячих клавиш​ чистую страницу. Без​ Excel для чайников​ сводных отчетов.​ Удалить строки –​ плюс пустая ячейка​ эту же ячейку​Здесь мы можем дать​ в предназначенную для​ горячих клавиш CTRL+T).​

  3. ​ таблицы Excel, как​ крестик. Он указываем​ знания – расширяем​Отмечаем «столбец» и жмем​ заданный программой показатель​ CTRL+Z. Но она​ расширит границы.​ ставим курсор в​ всяких границ.​ не терпит спешки.​Работа в Экселе с​

  4. ​ не вариант (данные​ для будущего итога​ и вводим новые​ имя таблице, изменить​ названия ячейку. Вписываем​В открывшемся диалоговом окне​ в программе Word.​

​ на маркер автозаполнения.​ границы столбцов, «подбираем»​ ОК.​

​ (как правило это​ срабатывает тогда, когда​Если нужно сохранить ширину​ любую ячейку нужного​

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

Как создать таблицу в Excel: пошаговая инструкция

​ размер.​ наименование и нажимаем​ указываем диапазон для​Поменяйте, к примеру, размер​ Цепляем его левой​ высоту для строк. ​Совет. Для быстрой вставки​

​ 8,43 — количество​

  1. ​ делаешь сразу. Позже​ столбца, но увеличить​ столбца – нажимаем​ с ячейками, строками​

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

​ кнопкой мыши и​Чтобы заполнить графу «Стоимость»,​ столбца нужно выделить​ символов шрифта Calibri​ – не поможет.​

​ высоту строки, воспользуемся​ Ctrl + пробел.​ и столбцами.​ для конкретных целей​ первый взгляд показаться​ этой цели воспользуйтесь​ «Редактирование» на закладке​ Excel будет распознавать​ преобразовать таблицу в​

​ расширится.​ таблица с подзаголовками.​ «жирным». Можно установить​ ведем до конца​ ставим курсор в​

Как работать с таблицей в Excel

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

​ «Главная» или нажмите​ их. Достаточно набрать​ обычный диапазон или​

​Если необходимо увеличить количество​ Жмем ОК. Ничего​ текст по центру,​ столбца. Формула скопируется​

​ первую ячейку. Пишем​ месте и нажать​ 11 пунктов). ОК.​ исходные границы, открываем​ на панели инструментов.​

  1. ​ – Shift +​Чтобы выделить весь столбец,​ своими преимуществами. Поэтому​ отличается от принципов​ выше). Убираете галочки​ комбинацию горячих клавиш​ на клавиатуре несколько​ сводный отчет.​ строк, зацепляем в​ страшного, если сразу​

  2. ​ назначить переносы и​ во все ячейки.​ «=». Таким образом,​ CTRL+SHIFT+»=».​Выделяем столбец /строку правее​ меню инструмента: «Главная»-«Формат»​

  3. ​Для изменения ширины столбцов​ пробел.​ щелкаем по его​ сначала визуально оценим​ построения таблиц в​ напротив тех значений,​ ALT+»=»).​ символов и нажать​

  4. ​Возможности динамических электронных таблиц​ нижнем правом углу​ не угадаете диапазон. ​ т.д.​Обозначим границы нашей таблицы.​ мы сигнализируем программе​Все эти навыки пригодятся​ /ниже того места,​ и выбираем «Автоподбор​ и высоты строк​

​Если информация при заполнении​ названию (латинской букве)​ ситуацию.​ Word. Но начнем​ которые должны быть​Если нажать на стрелочку​ Enter.​

​ MS Excel огромны.​ за маркер автозаполнения​ «Умная таблица» подвижная,​Простейший способ создания таблиц​ Выделяем диапазон с​ Excel: здесь будет​ при составлении таблицы​ где нужно вставить​ высоты строки»​ сразу в определенном​ таблицы не помещается​ левой кнопкой мыши.​Посмотрите внимательно на рабочий​ мы с малого:​ спрятаны.​

exceltable.com>

​ справа каждого подзаголовка​

  • Работа в excel с таблицами и формулами

  • Создать фильтр в excel

  • Excel обновить сводную таблицу в excel

  • Как сравнить две таблицы в excel на совпадения

  • Как в таблице excel посчитать сумму столбца автоматически

  • Как построить круговую диаграмму в excel по данным таблицы

  • Сравнение таблиц в excel на совпадения

  • В excel сопоставить две таблицы в

  • Excel как в таблице найти нужное значение

  • Как сделать гистограмму в excel по данным таблицы

  • Форматирование таблиц в excel

  • Как экспортировать таблицу из excel в word

Как сделать сводную таблицу в Excel: пошаговая инструкция

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

Cводная таблица в Эксель – это также один из самых недооцененных инструментов. Большинство пользователей не подозревает, какие возможности находятся в их руках. Представим, что сводные таблицы еще не придумали. Вы работаете в компании, которая продает свою продукцию различным клиентам. Для простоты в ассортименте только 4 позиции. Продукцию регулярно покупает пара десятков клиентов, которые находятся в разных регионах. Каждая сделка заносится в базу данных и представляет отдельную строку.

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

Вначале создадим макет таблицы, то есть шапку, состоящую из уникальных значений товаров и регионов. Сделаем копию столбца с товарами и удалим дубликаты. Затем с помощью специальной вставки транспонируем столбец в строку. Аналогично поступаем с областями, только без транспонирования. Получим шапку отчета.

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

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

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

Создание сводной таблицы в Excel

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

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

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

Обычно ничего менять здесь не нужно. После нажатия Ок будет создан новый лист Excel с пустым макетом сводной таблицы.

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

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

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

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

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

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

Область строк – названия строк, которые расположены в крайнем левом столбце. Это все уникальные значения выбранного поля (столбца). В области строк может быть несколько полей, тогда таблица получается многоуровневой. Здесь обычно размещают качественные переменные типа названий продуктов, месяцев, регионов и т.д.

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

В результате мы получаем настоящую сводную таблицу.

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

Изменить существующую сводную таблицу также легко. Посмотрим, как пожелания директора легко воплощаются в реальность.

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

Для успешной работы со сводными таблицами исходные данные должны отвечать ряду требований. Обязательным условием является наличие названий над каждым полем (столбцом), по которым эти поля будут идентифицироваться. Теперь полезные советы.

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

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

3. Уберите пропуски и пустые ячейки иначе данная строка может выпасть из анализа.

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

В целом требований немного, но их следует знать.

Обновление данных в сводной таблице Excel

Если внести изменения в источник (например, добавить новые строки), сводная таблица не изменится, пока вы ее не обновите через правую кнопку мыши

или
через команду во вкладке Данные – Обновить все.

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

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

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

Скачать файл с примером.

Поделиться в социальных сетях:

Как создавать таблицы в Excel (в простых шагах)

Создать таблицу | Сортировать | Фильтр | Итоговая строка | Имя таблицы | AutoExpansion

Освойте Таблицы Excel и быстро и легко проанализируйте свои данные. Узнайте, как создать таблицу, отсортировать таблицу, отфильтровать таблицу и многое другое.

Создать таблицу

Чтобы создать таблицу , выполните следующие шаги.

1. Щелкните любую ячейку внутри набора данных.

2. На вкладке Вставка в группе Таблицы нажмите Таблица (или просто нажмите Ctrl+T).

Появится диалоговое окно «Создать таблицу».

3. Excel автоматически выбирает данные за вас. Установите флажок «В моей таблице есть заголовки» и нажмите «ОК».

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

Сортировка

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

1. Сначала щелкните стрелку рядом с пунктом «Продажи» и выберите «Сортировка от меньшего к большему».

2. Затем щелкните стрелку рядом с фамилией и выберите Сортировка от А до Я.

Результат.

Фильтр

Чтобы отфильтровать таблицу, выполните следующие шаги.

1. Щелкните стрелку рядом с полем Страна и отметьте только США.

Результат.

Итоговая строка

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

1. Сначала выберите ячейку внутри таблицы. Затем на вкладке «Конструктор таблицы» в группе «Параметры стиля таблицы» установите флажок «Строка итогов» (или просто нажмите Ctrl + Shift + T).

Результат.

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

Примечание. В строке формул посмотрите, как Excel использует функцию ПРОМЕЖУТОЧНЫЙ ИТОГ для вычисления суммы. 109 — это аргумент для суммы, если вы используете функцию ПРОМЕЖУТОЧНЫЙ ИТОГ. Excel использует эту функцию (а не стандартную функцию СУММ) для правильного расчета итоговых значений отфильтрованных таблиц.

Имя таблицы

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

1. Сначала выберите ячейку внутри таблицы. Далее на вкладке Table Design в группе Properties мы видим, что Table1 — это имя этой таблицы.

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

Примечание: посетите нашу страницу о структурированных ссылках, чтобы узнать больше о табличных формулах.

AutoExpansion

Таблицы в Excel автоматически расширяются при добавлении новых строк или столбцов. Это отличная функция.

1. Сначала выберите ячейку внутри таблицы.

2. Нажмите Ctrl + Shift + T, чтобы быстро удалить итоговую строку.

3. Выберите ячейку A16 и введите Brown (Excel автоматически отформатирует эту новую строку).

Вывод: эта новая строка автоматически становится частью таблицы. Все формулы, относящиеся к таблице 1, будут обновлены автоматически (см. ячейку G2). Ух ты!

Учебное пособие по функции ВПР в Excel с примерами формул

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

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

Функция Excel ВПР

Что такое ВПР? Начнем с того, что это функция Excel 🙂 Что она делает? Он ищет указанное вами значение и возвращает соответствующее значение из другого столбца. С технической точки зрения, функция ВПР ищет значение в первом столбце заданного диапазона и возвращает значение в той же строке из другого столбца.

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

Буква «V» означает «вертикальный» и используется для отличия ВПР от функции ГПР, которая ищет значение в строке, а не в столбце (H означает «горизонтальный»).

Функция доступна во всех версиях Excel 365 — Excel 2007.

Совет. В Excel 365 и Excel 2021 вы можете использовать функцию XLOOKUP, которая является более гибкой и мощной преемницей VLOOKUP.

Синтаксис ВПР

Синтаксис функции ВПР следующий:

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

Где:

  • Lookup_value (обязательно) — значение для поиска.
    Это может быть значение (число, дата или текст), ссылка на ячейку (ссылка на ячейку, содержащую значение поиска) или значение, возвращаемое какой-либо другой функцией. В отличие от чисел и ссылок на ячейки, текстовые значения всегда должны быть заключены в «двойные кавычки».
  • Table_array (обязательно) — это диапазон ячеек, где искать искомое значение и из которого извлекать совпадения. Функция ВПР всегда ищет в первом столбце массива таблиц , который может содержать различные текстовые значения, числа, даты и логические значения.
  • Col_index_num (обязательно) — это номер столбца, из которого нужно вернуть значение. Подсчет начинается с крайнего левого столбца в массиве таблиц, который равен 1.
  • Range_lookup (необязательный) — определяет, искать приблизительное или точное совпадение:
    • ИСТИНА или опущено (по умолчанию) — приблизительное совпадение. Если точное совпадение не найдено, формула ищет наибольшее значение, которое меньше искомого значения. Требуется сортировка столбца поиска в порядке возрастания.
    • FALSE — точное совпадение. Формула ищет значение, точно равное искомому значению. Если точное совпадение не найдено, возвращается значение #Н/Д.

Основная формула ВПР

Вот пример формулы Excel ВПР в самой простой форме. Пожалуйста, взгляните на приведенную ниже формулу и попробуйте «перевести» ее на английский язык:

.

=ВПР("лев", A2:B11, 2, ЛОЖЬ)

  • 1 ст 9Аргумент 0169 ( lookup_value ) ясно указывает, что формула ищет слово «лев».
  • Аргумент 2 и ( table_array ) равен A2:B11. Имея в виду, что поиск выполняется в крайнем левом столбце, вы можете прочитать приведенную выше формулу чуть дальше: искать «лев» в диапазоне A2:A11. Пока все хорошо, правда?
  • Аргумент 3 rd col_index_num равен 2. Это означает, что мы хотим вернуть соответствующее значение из столбца B, который является вторым в массиве таблиц.
  • Аргумент 4 th range_lookup имеет значение FALSE, что означает, что мы ищем точное совпадение.

Со всеми установленными аргументами у вас не должно возникнуть проблем с чтением всей формулы: найдите «лев» в A2:A11, найдите точное совпадение и верните значение из столбца B в той же строке.

Для удобства вы можете ввести интересующее вас значение в какую-нибудь ячейку, скажем, E1, заменить «жестко закодированный» текст ссылкой на ячейку и получить формулу для поиска любого значения, которое вы вводите в E1:

= ВПР(E1, A2:B11, 2, ЛОЖЬ)

Что-то осталось неясным? Тогда попробуйте посмотреть на это так:

Как сделать Vlookup в Excel

При использовании формул ВПР в реальных рабочих листах основное правило таково: заблокируйте массив таблицы абсолютными ссылками на ячейки (например, $A$2:$C$11), чтобы предотвратить его изменение при копировании формулы в другие ячейки.

Искомое значение в большинстве случаев должно быть относительной ссылкой (например, E2), или вы можете заблокировать только координату столбца ($E2). Когда формула копируется вниз по столбцу, ссылка автоматически корректируется для каждой строки.

Чтобы увидеть, как это работает на практике, рассмотрим следующий пример. В нашу примерную таблицу мы добавили еще один столбец, ранжирующий животных по скорости (столбец A), и хотим найти 1  , 5   и 10   самого быстрого спринтера в мире. Для этого введите ранги поиска в некоторые ячейки (E2:E4 на скриншоте ниже) и используйте следующие формулы:

Чтобы получить имена животных из столбца B:

=ВПР($E2, $A$2:$C$11, 2, ЛОЖЬ)

Чтобы извлечь скорость из столбца C:

=ВПР($E2, $A$2:$C$11, 3, ЛОЖЬ)

Введите приведенные выше формулы в ячейки F2 и G2, выберите эти ячейки и перетащите формулы в следующие строки:

Если вы исследуете формулу в нижней строке, вы заметите, что ссылка на искомое значение скорректирована для этой конкретной строки, в то время как массив таблицы не изменился:

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

Excel VLOOKUP — 5 вещей, которые нужно помнить!

  1. Функция ВПР не может смотреть влево . Он всегда ищет 90 103 в крайнем левом столбце 90 104 массива таблиц и возвращает значение из столбца справа. Если вам нужно извлечь значения слева, используйте комбинацию INDEX MATCH (или INDEX XMATCH в Excel 365), которая может не заботиться о расположении столбцов поиска и возврата.
  2. Функция ВПР нечувствительна к регистру , что означает, что символы верхнего и нижнего регистра обрабатываются как эквивалентные. Чтобы различать регистр букв, используйте формулы ВПР с учетом регистра.
  3. Помните о важности последнего параметра. Используйте TRUE для приблизительного совпадения и FALSE для точного совпадения. Подробную информацию см. в разделе ВПР ИСТИНА и ЛОЖЬ.
  4. При поиске приблизительного совпадения убедитесь, что данные в столбце подстановки отсортированы по возрастанию.
  5. Если искомое значение не найдено, возвращается ошибка #Н/Д. Сведения о других ошибках см. в статье Почему функция ВПР Excel не работает.

Примеры ВПР Excel

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

Как выполнять визуальный поиск с другого листа в Excel

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

Для ВПР с другого листа Excel поместите имя рабочего листа, а затем восклицательный знак в table_array Аргумент перед ссылкой на диапазон. Например, для поиска в диапазоне A2:B10 на Листе2 используйте следующую формулу:

.

=ВПР("Товар1", Лист2!A2:B10, 2)

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

Например, так можно найти значение A2 в диапазоне A2:A9.на листе Prices и вернуть соответствующее значение из столбца C:

=ВПР(A2, Цены!$A$2:$C$9, 3, ЛОЖЬ)

Примечания:

  • Если имя электронной таблицы содержит пробела или неалфавитные символы , оно должно быть заключено в одинарные кавычки, например ‘Прайс-лист’!$A$2:$C$9.
  • Если вы используете формулу ВПР для нескольких ячеек, не забудьте заблокировать table_array знаком $, например $A$2:$C$9.

Как выполнить визуальный поиск из другой книги в Excel

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

Например, вот формула для поиска значения A2 на листе с именем Prices в рабочей книге Price_List.xlsx :

=ВПР(A2, [Price_List.xlsx]Цены!$A$2:$C$9, 3, ЛОЖЬ)

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

=ВПР(A2, '[Прайслист. xlsx]Цены'!$A$2:$C$9, 3, ЛОЖЬ)

Самый простой способ создать формулу ВПР, которая ссылается на другую книгу:

  1. Откройте оба файла.
  2. Начните вводить формулу, переключитесь на другую книгу и выберите массив таблицы с помощью мыши.
  3. Введите остальные аргументы и нажмите клавишу Enter, чтобы завершить формулу.

Результат будет примерно таким, как на скриншоте ниже:

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

Дополнительные сведения см. в разделе Как ссылаться на другой лист или книгу Excel.

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

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

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

В этом примере мы дали имя Prices_2020 ячейкам данных (A2:C9) в таблице поиска и получили следующую компактную формулу:

=ВПР(A2, Цены_2020, 3, ЛОЖЬ)

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

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

=ВПР(A2, 'Прайс-лист.xlsx'!Цены_2020, 3, ЛОЖЬ)

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

Если вы преобразовали диапазон поиска в полнофункциональную таблицу Excel, вы можете выполнить ВПР на основе имени таблицы, например Price_table в следующей формуле:

= ВПР(A2, Таблица_цен, 3, ЛОЖЬ)

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

Использование подстановочных знаков в формуле ВПР

Как и многие другие формулы, функция ВПР Excel поддерживает следующие подстановочные знаки:

  • Знак вопроса (?) соответствует любому одиночному символу.
  • Звездочка (*) для соответствия любой последовательности символов.

Подстановочные знаки оказываются очень полезными во многих ситуациях:

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

Пример 1. Поиск текста, начинающегося или заканчивающегося определенными символами

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

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

=ВПР("подтверждение*", $A$2:$B$10, 1, ЛОЖЬ)

Чтобы получить лицензионный ключ из столбца B, используйте этот (разница только в порядковом номере столбца):

=ВПР("подтверждение*", $A$2:$B$10, 2, ЛОЖЬ)

Вы также можете ввести известную часть имени в какую-нибудь ячейку, скажем, E1, и объединить подстановочный знак со ссылкой на ячейку:

=ВПР(E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

На снимке экрана ниже показаны результаты:

Ниже приведены еще несколько формул ВПР с подстановочными знаками.

Найти фамилию, оканчивающуюся на «сын»:

=ВПР("*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить имя, начинающееся с «joh» и заканчивающееся на «son»:

=ВПР("джон*сын", $A$2:$B$10, 1, ЛОЖЬ)

Получить 5-значную фамилию:

=ВПР("?????", $A$2:$B$10, 1, ЛОЖЬ)

Пример 2.

Подстановочный знак ВПР на основе значения ячейки

Из предыдущего примера вы уже знаете, что можно объединить амперсанд (&) и ссылку на ячейку для создания строки поиска. Чтобы найти значение, которое содержит заданный символ (символы) в любой позиции, поставьте амперсанд до и после ссылки на ячейку.

Допустим, вы хотите получить имя, соответствующее определенному лицензионному ключу, но не знаете всего ключа, только несколько символов. С ключами в столбце A, именами в столбце B и частью целевого ключа в E1 вы можете выполнить Vlookup с подстановочными знаками следующим образом:0003

Извлечь ключ:

=ВПР("*"&E1&"*", $A$2:$B$10, 1, ЛОЖЬ)

Извлечь имя:

=ВПР("*"&E1&"*", $A$2:$B$10, 2, ЛОЖЬ)

Примечания:

  • Чтобы формула ВПР работала правильно, используйте точное совпадение (ЛОЖЬ — последний аргумент).
  • Если найдено более одного совпадения, возвращается первое из них .

ВПР ИСТИНА против ЛОЖЬ

А теперь пора более подробно рассмотреть последний аргумент функции ВПР Excel. Параметр range_lookup , хотя и необязательный, очень важен. В зависимости от того, выберете ли вы ИСТИНА или ЛОЖЬ, ваша формула может дать разные результаты.

ВПР Excel точное совпадение (ЛОЖЬ)

Если для range_lookup задано значение FALSE, формула Vlookup ищет значение, точно равное искомому значению. Если найдено два или более совпадения, возвращается 1-е. Если точное совпадение не найдено, возникает ошибка #Н/Д.

Приблизительное совпадение функции ВПР Excel (ИСТИНА)

Если для параметра range_lookup установлено значение TRUE или оно опущено (по умолчанию), формула ищет ближайшее соответствие. Точнее, он сначала ищет точное совпадение, а если точное совпадение не найдено, ищет следующее наибольшее значение, которое меньше искомого значения.

Приблизительное соответствие Vlookup работает со следующими оговорками:

  • Столбец поиска должен быть отсортирован в порядке возрастания , от наименьшего к наибольшему, в противном случае правильное значение может быть не найдено.
  • Если значение поиска меньше наименьшего значения в массиве поиска, возвращается ошибка #Н/Д.

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

Пример 1. Как сделать точное совпадение Vlookup

Чтобы найти точное совпадение, просто введите FALSE в последний аргумент.

Для этого примера возьмем таблицу скорости животных, поменяем столбцы местами и попробуем найти животных, которые могут бегать со скоростью 80, 50 и 30 миль в час. Со значениями поиска в D2, D3 и D4 введите приведенную ниже формулу в E2, а затем скопируйте ее еще в две ячейки:

= ВПР(D2, $A$2:$B$12, 2, ЛОЖЬ)

Как видите, формула возвращает «Лев» в E3, потому что они выполняются ровно 50 раз в час. Для двух других значений поиска точное совпадение не найдено, и появляются ошибки #Н/Д.

Пример 2.

Как выполнить Vlookup для приблизительного соответствия

Чтобы найти приблизительное совпадение, вам нужно сделать две важные вещи:

  • Отсортировать первый столбец table_array от меньшего к большему.
  • Используйте TRUE для аргумента range_lookup или опустите его.

Сортировка столбца подстановки очень важна, поскольку функция ВПР прекращает поиск, как только находит близкое соответствие, меньшее, чем значение подстановки. Если данные не отсортированы должным образом, вы можете получить действительно странные результаты или кучу ошибок #N/A.

Для нашего примера данных формула приблизительного совпадения Vlookup выглядит следующим образом:

= ВПР(D2, $A$2:$B$12, 2, ИСТИНА)

И возвращает следующие результаты:

  • Для искомого значения «80» возвращается «Cheetah», поскольку его скорость (70) является ближайшим соответствием, которое меньше искомого значения.
  • Для искомого значения «50» возвращается точное совпадение (лев).
  • Для искомого значения «30» возвращается ошибка #Н/Д, поскольку искомое значение меньше наименьшего значения в столбце поиска.

Видео: ВПР в Excel

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

Специальные инструменты для ВПР в Excel

Несомненно, ВПР — одна из самых мощных и полезных функций Excel, но она также и одна из самых запутанных. Чтобы сделать кривую обучения менее крутой и сделать ее более приятной, мы включили несколько инструментов, позволяющих сэкономить время, в наш Ultimate Suite for Excel.

Мастер ВПР — простой способ написания сложных формул

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

Чтобы получить индивидуальную формулу, вам нужно сделать следующее:

  1. Запустите мастер ВПР.
  1. Выберите основную таблицу и таблицу поиска.
  2. Укажите следующие столбцы (во многих случаях они выбираются автоматически):
    • Ключевой столбец — столбец в вашей основной таблице, содержащий значения для поиска.
    • Столбец поиска — столбец для поиска.
    • Возврат столбца — столбец, из которого нужно получить значения.
  3. Нажмите кнопку Вставить .

В следующих примерах показан мастер в действии.

Стандартный ВПР

Если столбец подстановки ( Животное ) является крайним левым столбцом в таблице подстановки, вставляется обычная формула ВПР для точного совпадения:

ВПР слева

Когда столбец поиска ( Животное ) находится справа от столбца возврата ( Скорость ), мастер вставляет формулу INDEX MATCH в Vlookup справа налево:

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

Объединить две таблицы — альтернатива ВПР Excel без формул

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

Этот инструмент является нашей визуальной и простой альтернативой функции ВПР Excel, которая работает следующим образом:

  1. Выберите основной стол.
  2. Выберите таблицу поиска.
  3. Выберите один или несколько общих столбцов в качестве уникальных идентификаторов.
  4. Укажите, какие столбцы необходимо обновить.
  5. При необходимости выберите столбцы для добавления.
  6. Дайте мастеру слияния таблиц несколько секунд на обработку… и наслаждайтесь результатами 🙂

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