Создание и связывание таблиц в Excel с использованием ВПР (VLOOKUP)

Практическая работа №7 включает создание и связывание таблиц в Excel. Вот шаги для выполнения задания:

Задание 1: Создание и связывание таблиц

Цель: Создать основу для анализа данных с использованием связанных таблиц.

Шаги:

1. Создание листов:

   • Откройте новый файл в Excel.
   • Создайте два листа и назовите их "Товары" и "Продажи".

2. Таблица "Товары":

   • На листе "Товары" создайте таблицу со следующими столбцами:
     • Артикул
     • Наименование товара
     • Цена (руб.)
   • Заполните таблицу данными, как показано в примере:

   Артикул	Наименование товара	Цена (руб.)
   G001	Беспроводные наушники	3500
   G002	Power Bank 10000 mAh	1800
   G003	Умные часы	4700
   G004	Bluetooth колонка	2500

   • Выделите диапазон с данными и отформатируйте его как таблицу (Главная → Форматировать как таблицу).

3. Таблица "Продажи":

   • На листе "Продажи" создайте таблицу со следующими столбцами:
     • Дата продажи
     • Менеджер
     • Артикул товара
     • Количество
   • Заполните таблицу данными, как показано в примере:

   Дата продажи	Менеджер	Артикул	Количество
   10.10.2023	Иванов	G001	2
   10.10.2023	Петрова	G003	1
   11.10.2023	Сидоров	G002	3
   11.10.2023	Иванов	G004	1
   12.10.2023	Петрова	G001	1
   12.10.2023	Сидоров	G003	2

4. Связывание таблиц:

   • В таблице "Продажи" добавьте столбец "Стоимость" рядом со столбцом "Количество".
   • В первой ячейке столбца "Стоимость" (например, D2) введите формулу:

   =[@Количество] * ВПР([@[Артикул товара]]; Товары!$A$2:$C$5; 3; ЛОЖЬ)

   • Объяснение формулы ВПР (VLOOKUP):
     • [@[Артикул товара]] – это значение, которое ищем (артикул товара из текущей строки таблицы "Продажи").
     • Товары!$A$2:$C$5 – это диапазон, в котором ищем (вся таблица "Товары"). Знаки $ делают ссылку абсолютной, чтобы при копировании формулы диапазон не смещался.
     • 3 – это номер столбца в таблице "Товары", из которого нужно вернуть значение (в данном случае, столбец "Цена").
     • ЛОЖЬ – указывает на точное соответствие при поиске.

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

Задание 1: Объяснение функции ВПР (VLOOKUP)

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

Синтаксис функции ВПР

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

• искомое_значение: Значение, которое нужно найти в первом столбце таблицы.
• таблица: Диапазон ячеек, в котором производится поиск. Первый столбец этого диапазона используется для поиска искомого_значения.
• номер_столбца: Номер столбца в указанном диапазоне, из которого возвращается значение. Например, если номер_столбца равен 3, то функция вернет значение из третьего столбца найденной строки.
• [интервальный_просмотр]: Необязательный аргумент, определяющий тип поиска:
  • ИСТИНА или опущено: Приблизительное соответствие. Используется, когда первый столбец отсортирован. Если точное соответствие не найдено, возвращается ближайшее меньшее значение.
  • ЛОЖЬ: Точное соответствие. Функция ищет точное совпадение искомого_значения в первом столбце. Если точное соответствие не найдено, возвращается ошибка #Н/Д.

Примеры использования ВПР

1. Поиск цены товара по артикулу:

   Допустим, у вас есть таблица "Товары" с колонками "Артикул", "Наименование", "Цена". Вы хотите найти цену товара по его артикулу.

   Формула:

   =ВПР("G001"; Товары!$A$2:$C$10; 3; ЛОЖЬ)

   Здесь:

   • "G001" - искомый артикул.
   • Товары!$A$2:$C$10 - диапазон таблицы "Товары".
   • 3 - номер столбца с ценой.
   • ЛОЖЬ - требуется точное соответствие.

2. Поиск информации о сотруднике по ID:

   У вас есть таблица "Сотрудники" с колонками "ID", "Имя", "Должность", "Зарплата". Нужно найти должность сотрудника по его ID.

   Формула:

   =ВПР(123; Сотрудники!$A$2:$D$20; 3; ЛОЖЬ)

   Здесь:

   • 123 - искомый ID сотрудника.
   • Сотрудники!$A$2:$D$20 - диапазон таблицы "Сотрудники".
   • 3 - номер столбца с должностью.
   • ЛОЖЬ - требуется точное соответствие.

Альтернативные варианты

1. Функция ИНДЕКС и ПОИСКПОЗ:

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

   • ПОИСКПОЗ находит позицию элемента в массиве.
   • ИНДЕКС возвращает значение элемента по указанной позиции в диапазоне.

   Пример:

   =ИНДЕКС(Товары!$C$2:$C$10; ПОИСКПОЗ("G001"; Товары!$A$2:$A$10; 0))

   Здесь:

   • Товары!$C$2:$C$10 - диапазон, откуда нужно вернуть значение (цены).
   • ПОИСКПОЗ("G001"; Товары!$A$2:$A$10; 0) - находит позицию артикула "G001" в столбце артикулов.
   • 0 в ПОИСКПОЗ означает точное соответствие.

2. Функция XLOOKUP (доступна в новых версиях Excel):

   XLOOKUP - это более современная и мощная альтернатива VLOOKUP. Она решает многие ограничения VLOOKUP и предлагает более интуитивный синтаксис.

   Пример:

   =XLOOKUP("G001"; Товары!$A$2:$A$10; Товары!$C$2:$C$10)

   Здесь:

   • "G001" - искомый артикул.
   • Товары!$A$2:$A$10 - диапазон, где ищем артикул.
   • Товары!$C$2:$C$10 - диапазон, откуда возвращаем цену.

Преимущества и недостатки

• ВПР (VLOOKUP):
  • Преимущества: Широко используется, хорошо известна.
  • Недостатки: Ограничена поиском только в первом столбце, требует указания номера столбца, менее гибкая.
• ИНДЕКС и ПОИСКПОЗ:
  • Преимущества: Более гибкая, позволяет искать в любом столбце и возвращать значения из любого диапазона.
  • Недостатки: Более сложная в написании и понимании.
• XLOOKUP:
  • Преимущества: Самая гибкая и мощная, позволяет искать в любом столбце, не требует указания номера столбца, обрабатывает ошибки более эффективно.
  • Недостатки: Доступна только в новых версиях Excel.