Умные таблицы и базы данных в Excel

После урока сможешь: превратить диапазон в «умную таблицу» (авто-расширение, встроенный автофильтр, структурные ссылки) и распознать в паре таблиц Excel базу данных — таблицу-факт и таблицу-справочник, связанные по ключу.

Вспомни: ВПР мы разбирали в теме 2.7 — здесь он используется как готовый инструмент связи таблиц, синтаксис заново не разбираем. А фраза «строка — запись, столбец — поле» уже звучала в теме 3.3 (реюз БГУ) — здесь мы применяем её на практике.

Умная таблица: Ctrl+T

Обычный диапазон можно превратить в «умную таблицу» — именованный объект с заголовками, встроенным автофильтром и собственной структурой: выдели диапазон → Ctrl+T (или «Вставка» → «Таблица»). У шапки сразу появляются стрелки автофильтра — включать его отдельно не нужно. Если ввести данные в строку сразу под таблицей, она сама расширится и захватит новую строку — формулы в соседних столбцах скопируются на неё автоматически.

Структурные ссылки

У умной таблицы есть имя (например, «Таблица1»), и формулы могут ссылаться на её столбцы по имени: Таблица1[Цена] вместо диапазона B2:B100. Такая ссылка всегда указывает на весь столбец «Цена» целиком, даже если таблица выросла — не нужно вручную поправлять границы диапазона.

База данных внутри Excel: записи, поля, ключ

Таблицу Excel можно рассматривать как маленькую базу данных: запись — это одна строка, целиком описывающая один объект (например, один товар или одного студента); поле — это один столбец, одна характеристика объекта (цена, группа, оценка). Ключ — столбец, который однозначно отличает одну запись от другой (например, «Код товара») и по которому таблицы можно связывать друг с другом.

Несколько связанных таблиц: факты и справочник

На практике данные часто хранят в двух таблицах: таблица-факт (события — продажи: дата, код товара, количество) и таблица-справочник (уникальные характеристики — товары: код товара, цена, категория). Чтобы узнать цену для каждой продажи, таблицы связывают по общему ключу «код товара» через ВПР (тема 2.7) — справочник при этом фиксируют абсолютной ссылкой ($), чтобы диапазон не «уезжал» при копировании формулы вниз (тема 2.2).

Таблица-ФАКТ «Продажи»            Таблица-СПРАВОЧНИК «Товары»
Дата       Код    Кол-во          Код    Цена   Категория
──────────────────────            ─────────────────────────
01.03     Т-01     5               Т-01    120    Хлеб
01.03     Т-02     3               Т-02     80    Молоко

   =ВПР(Код; Товары!$A$2:$C$100; 2; ЛОЖЬ)  ->  подтягивает Цену
   по общему ключу «Код» из справочника в таблицу продаж.

Проверь себя

Контрольный вопрос. Что делает Ctrl+T (или «Вставка» → «Таблица») с обычным диапазоном?

Апревращает его в «умную таблицу» — именованный объект с автофильтром в шапке

Бстроит по нему диаграмму

Вудаляет из него пустые строки

Гсортирует данные в диапазоне

Подсказка: Речь про превращение диапазона в особый объект, а не про действие над данными.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. При вводе новой строки сразу под «умной таблицей» происходит:

Атаблица сама расширяется, включая новую строку, и формулы копируются на неё

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

ВExcel показывает ошибку

Гновая строка становится отдельной таблицей

Подсказка: Это и есть главное удобство умной таблицы — автоматическое расширение.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Структурная ссылка вида Таблица1[Цена] вместо диапазона B2:B100…

Авсегда ссылается на весь текущий столбец «Цена» умной таблицы, даже если она выросла

Бработает только для первых 100 строк

Втребует одинакового имени листа

Гто же самое, что обычный диапазон, разницы нет

Подсказка: Структурная ссылка называет столбец по имени таблицы, а не по номерам строк.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Напиши структурную ссылку сам. Умная таблица называется Таблица1, в ней есть столбец Цена. Впиши ссылку на весь этот столбец в том виде, в каком её понимает Excel (имя таблицы и название столбца в квадратных скобках, без знака равно).

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

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Запись базы данных в Excel — это…

Аодна строка таблицы, целиком описывающая один объект (например, один товар)

Бодин столбец таблицы

Водна ячейка

Гвся таблица целиком

Подсказка: Вспомни тему 3.3: строка — это единая запись, а не отдельное поле.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Поле базы данных в Excel — это…

Аодин столбец таблицы, одна характеристика объекта

Бодна строка

Ввся таблица

Годин файл

Подсказка: Поле — это конкретная характеристика (цена, группа), а не весь объект целиком.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Ключ в таблице-базе данных нужен, чтобы…

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

Браскрашивать строки

Вускорить сортировку

Гзащитить лист паролем

Подсказка: Ключ — это то, что делает каждую запись уникальной и позволяет её найти в другой таблице.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Таблица «Продажи» (факты: дата, код товара, количество) и таблица «Товары» (справочник: код товара, цена, категория). Чтобы узнать цену для каждой продажи, нужно…

Асвязать таблицы по ключу «код товара» через ВПР

Бсложить обе таблицы

Вотсортировать по дате

Гприменить фильтр

Подсказка: Цена лежит в другой таблице — её нужно подтянуть по общему признаку, коду товара.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. В задаче выше формула ВПР ищет код товара в справочнике «Товары» и подтягивает нужное поле. Что произойдёт, если скопировать формулу вниз, а диапазон справочника указан БЕЗ фиксации ($)?

Адиапазон справочника «уедет» вниз, и часть строк не найдёт нужный товар

Бничего, всё сработает как надо

Вформула превратится в текст

ГExcel покажет предупреждение и остановит копирование

Подсказка: Вспомни тему 2.2: относительная ссылка при копировании сдвигается вслед за формулой.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Задание. Как называется таблица, которая хранит справочную информацию (например, цену и категорию товара по коду), а не сами события/факты? Впиши одно слово.

Подсказка: Противоположность «таблице-факту» (события) — таблица с уникальными характеристиками объектов.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Чем «умная таблица» отличается от обычного диапазона ячеек?

Аавтоматически расширяется, имеет структурные ссылки и встроенный автофильтр

Бумеет считать сама, без формул

Веё нельзя копировать

Гработает только с числами

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

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Контрольный вопрос. Отметь все верные утверждения про базу данных внутри Excel (не менее двух вариантов).

Выберите все верные варианты.

Азапись — это строка, поле — это столбец

Бтаблицы можно связать по общему ключу через ВПР

Всправочник хранит уникальные характеристики объектов, а не события

Гключ обязательно должен быть числом — текстовые ключи не работают

Думная таблица не может использоваться вместе со сводными таблицами

Подсказка: Два варианта придумывают несуществующие ограничения.

Проверить ответ →

Реальная проверка — на нашей платформе, бесплатно и без регистрации. Открыть задачу и ввести ответ →

Итог

Итог. Умная таблица (Ctrl+T) сама расширяется, даёт готовый автофильтр и структурные ссылки вида Таблица1[Столбец]. Внутри Excel таблицу можно рассматривать как базу данных: запись — строка, поле — столбец, ключ — то, что однозначно определяет запись и связывает таблицы. Таблицу-факт (события) и таблицу-справочник (характеристики) соединяют по ключу через ВПР, фиксируя справочник абсолютной ссылкой.

← Назад к главе 3  ·  ↑ В начало урока  ·  ⌂ В начало курса  ·  Вперёд: Глава 4. Сводные таблицы →