БД, фильтрация, сортировка, поиск

• сортировка

• фильтрация

• поиск

​​​Базы данных (БД)

​Базы данных (БД) — набор упорядоченной информации, которая хранится в одном месте. Их создают, чтобы быстро находить, систематизировать и редактировать нужные данные, а при необходимости собирать их в отчёт или показывать в виде диаграммы. Специализированных программ для формирования и ведения баз данных много. В том же MS Office для этого есть отдельное приложение — Microsoft Access. Но и в Excel достаточно возможностей для создания простых баз и удобного управления ими. Есть ограничение — количество строк базы данных в Excel не должно быть больше одного миллиона.

​Как выглядит база данных в Excel
База данных в Excel — таблица с информацией. Она состоит из однотипных строк — записей базы данных. Записи распределены по столбцам — полям базы данных.















Пример базы данных по продажам автомобилей в Excel
Названия полей — шапка таблицы — определяют структуру базы данных. Они показывают, какую информацию содержит база. В примере выше это имя менеджера автосалона, дата продажи, модель и характеристики автомобиля, который он продал. Каждая запись — строка таблицы — относится к одному объекту базы данных и содержит информацию о нём. В нашем примере записи характеризуют продажи, совершённые менеджерами автосалона.

Multiple Choice

Как выглядит база данных в Excel?

• Таблица с информацией

• График

• Текстовый документ

• Презентация

​При создании базы данных нельзя оставлять промежуточные строки полностью пустыми, как на скриншоте ниже. Так база теряет свою целостность — в таком виде ей нельзя управлять как единым объектом. Если в базе данных есть промежуточные пустые строки, ей нельзя управлять как единым объектом Скриншот: Excel / Skillbox Media Разберём пошагово, как создать базу данных из примера выше и управлять ей.
















Создание базы данных в Excel
Шаг 1
Создаём структуру базы данных Выше мы определили, что структуру базы данных определяют названия полей (шапка таблицы). Задача для нашего примера — создать базу данных, в которой будут храниться все данные о продажах автомобилей менеджерами автосалона. Каждая запись базы — одна продажа, поэтому названия полей БД будут такими:
• «Менеджер»; • «Дата продажи»; • «Марка, модель»;
• «Цвет»; • «Год выпуска»; • «Объём двигателя, л»; • «Цена, руб.».

Multiple Choice

Создание базы данных в Excel. Шаг 1. Создаём структуру базы данных. Какова задача для примера создания базы данных о продажах автомобилей?

• Создать базу данных о продажах

• Определить структуру базы данных

• Сохранить все данные о продажах

• Создать таблицу с пустыми строками

Multiple Choice

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

• Потеря целостности данных

• Увеличение объема базы

• Сложность в управлении

• Невозможность добавления новых данных

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
















Вводим названия полей базы данных и форматируем их


Шаг 2
Создаём записи базы данных В нашем примере запись базы данных — одна продажа. Перенесём в таблицу всю имеющуюся информацию о продажах. При заполнении ячеек с записями важно придерживаться одного стиля написания. Например, Ф. И. О. менеджеров во всех строках вводить в виде «Иванов И. И.». Если где-то написать «Иван Иванов», то дальше в работе с БД будут возникать ошибки.

Multiple Choice

Что произойдет, если в базе данных будет ошибка в написании имени менеджера?

• Ошибки не возникнут

• Будут возникать ошибки

• Данные будут потеряны

• База данных будет удалена

Multiple Choice

Как правильно вводить названия полей базы данных в Excel?

• С заглавной буквы

• С маленькой буквы

• С пробелами

• С цифрами

​Переносим в базу данных информацию о продажах
На этом же этапе важно проконтролировать, подходит ли формат ячеек данным в них. По умолчанию все ячейки получают общий формат. Чтобы в дальнейшем базой данных было удобнее пользоваться, можно изменить формат там, где это нужно. В нашем примере данные в столбцах A, C и D должны быть в текстовом формате. Данные столбца B должны быть в формате даты — его Excel определил и присвоил автоматически. Данные столбцов E, F — в числовом формате, столбца G — в финансовом.

Multiple Choice

Какой формат должен быть у данных в столбцах A, C и D?

• Текстовый

• Числовой

• Дата

• Финансовый

Меняем формат ячеек

В появившемся меню выберем нужный формат и настроим его. В нашем примере для ячейки «Цена, руб.» выберем финансовый формат, уберём десятичные знаки (знаки после запятой) и выключим отображение символа ₽.

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

​Настраиваем формат ячеек
Также изменить формат можно на панели вкладки «Главная».

Open Ended

Как можно изменить формат ячейки в Excel?

Multiple Choice

Какое количество десятичных знаков мы можем установить для денежного формата?

• 0

• 1

• 2

• 3

Multiple Choice

Какой формат мы выбираем для ячейки «Цена, руб.»?

• Общий

• Числовой

• Денежный

• Текстовый

1. Для этого выделим все поля и записи базы данных, включая шапку.
Нажмём правой кнопкой мыши и выберем «Имя диапазона»

Готово — мы внесли в базу данных информацию о продажах и отформатировали её. В следующем разделе разберёмся, как с ней работать.

2. В появившемся окне вводим имя базы данных без пробелов.

​​Шаг 3 Присваиваем базе данных имя

Multiple Choice

Как создать название базы данных в Excel?

• Ввести имя базы данных без пробелов

• Выбрать формат данных

• Сохранить файл

• Закрыть Excel

Multiple Choice

Как присвоить базе данных имя в Excel?

• Выделить все поля и записи

• Нажать правой кнопкой мыши

• Выбрать «Имя диапазона»

• Ввести имя базы данных

Multiple Choice

Как изменить формат данных в Excel?

• С помощью главной панели

• С помощью меню Файл

• С помощью вкладки Вставка

• С помощью вкладки Данные

Multiple Choice

Какое имя мы вводим в базу данных?

• БД1

• Книга

• Продажи

• Данные

​Работа с базой данных в Excel

​Сейчас в созданной базе данных все записи расположены хаотично — не упорядочены ни по датам, ни по фамилиям менеджеров. Разберёмся, как привести БД в более удобный для работы вид. Все необходимые для этого функции расположены на вкладке «Данные».


















На вкладке «Данные» расположены все необходимые инструменты для работы с БД в Excel

Multiple Select

На вкладке «Данные» расположены все необходимые инструменты для работы с БД в Excel. Каковы основные функции, которые можно использовать?

• Сортировка

• Фильтрация

• Группировка

• Суммирование

Multiple Choice

Как привести базу данных в более удобный для работы вид?

• Сортировать по дате

• Сортировать по фамилиям менеджеров

• Фильтровать записи

• Удалить лишние записи

​Фильтрация записей
Для начала добавим фильтры. Это инструмент, с помощью которого из большого объёма информации выбирают и показывают только нужную в данный момент. Подробнее о фильтрах в Excel говорили в этой статье Skillbox Media. Выберем любую ячейку из базы данных и на вкладке «Данные» нажмём кнопку «Фильтры».

​Фильтрация записей
Предположим, нужно показать только сделки менеджера Тригубова М. — нажмём на стрелку поля «Менеджер» и оставим галочку только напротив него. Затем нажмём «Применить фильтр».

Выбираем менеджера, данные о сделках которого нужно показать

Готово — в базе отражены данные о продажах только одного менеджера.

Open Ended

Какой инструмент используется для выбора информации из большого объёма данных?

Multiple Choice

Как показать только сделки менеджера Тригубова М. в таблице?

• Нажать на стрелку поля "Менеджер" и выбрать Тригубова М.

• Удалить все фильтры и заново установить

• Выбрать все сделки и нажать "Применить фильтр"

• Сортировать по дате

Multiple Choice

Как установить фильтрацию в Excel?

• Выбрать ячейку и нажать на кнопку "Фильтры"

• Нажать на кнопку "Применить фильтр"

• Выбрать ячейку и нажать на кнопку "Сортировка"

• Нажать на кнопку "Удалить фильтр"

​На кнопке со стрелкой появился дополнительный значок. Он означает, что в этом столбце настроена фильтрация. Чтобы её снять, нужно нажать на этот дополнительный значок и выбрать «Очистить фильтр».

















Записи БД можно фильтровать по нескольким параметрам одновременно. Для примера покажем среди продаж Тригубова М. только автомобили дешевле 2 млн рублей. Для этого в уже отфильтрованной таблице откроем меню фильтра для столбца «Цена, руб.» и нажмём на параметр «Выберите». В появившемся меню выберем параметр «Меньше».

​Готово — фильтрация сработала по двум параметрам.
Теперь БД показывает только те проданные менеджером авто, цена которых ниже 2 млн рублей.

Результат фильтрации по двум параметрам

Кроме этого, в Excel можно установить расширенный фильтр. Он позволяет фильтровать БД по сложным критериям сразу в нескольких столбцах.

Multiple Choice

Как можно отфильтровать записи в базе данных по нескольким параметрам одновременно?

• Выбрать все

• Выбрать несколько

• Выбрать один

• Не фильтровать

Multiple Choice

Какой фильтр нужно выбрать, чтобы показать данные о продажах только одного менеджера?

• Очистить фильтр

• Показать все

• Выбрать менеджера

• Сохранить фильтр

Multiple Choice

Какой фильтр применяется для выбора автомобилей дешевле 2 миллионов рублей?

• Фильтр по цвету

• Фильтр по возрасту

• Фильтр по цене

• Фильтр по модели

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

Для примера отсортируем сделки выбранного менеджера в хронологическом порядке — по датам. Для этого выделим любую ячейку в поле «Дата продажи» и нажмём кнопку «Сортировка».

​Результат сортировки по датам продаж

То же самое можно сделать другим способом — выбрать любую ячейку столбца, который нужно отсортировать, и нажать на одну из двух кнопок рядом с кнопкой «Сортировка»: «Сортировка от старых к новым» или «Сортировка от новых к старым». В этом случае данные отсортируются без вызова дополнительных окон.

Open Ended

Как можно отсортировать данные в Excel?

Multiple Choice

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

• Фильтрация

• Сортировка

• Группировка

• Суммирование

Multiple Choice

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

• Данные отсортированы по дате

• Данные отсортированы по алфавиту

• Данные отсортированы по количеству

• Данные отсортированы по значению

Multiple Choice

Какой параметр сортировки мы выбираем в окне сортировки?

• От старых к новым

• По алфавиту

• По количеству

• По дате

Multiple Choice

Как можно настроить сортировку по критериям, выбранным пользователем в Excel?

• Использовать стандартные настройки

• Выбрать ячейку и нажать на кнопку сортировки

• Сортировать только по одной колонке

• Сортировать по цвету ячеек

Multiple Choice

Какой способ сортировки данных в Excel показан на изображении?

• Сортировка по возрастанию

• Сортировка по убыванию

• Сортировка по дате

• Сортировка по алфавиту

​Поиск записей
В процессе работы базы данных могут разрастись до миллиона строк — найти нужную информацию станет сложнее. Фильтрация и сортировка не всегда упрощают задачу. В этом случае для быстрого поиска нужной ячейки — текста или цифры — можно воспользоваться функцией поиска. Предположим, нужно найти в БД автомобиль стоимостью 14 млн рублей. Перейдём на вкладку «Главная» и нажмём на кнопку «Найти и выделить». Также быстрый поиск можно задавать с любой вкладки Excel — через значок лупы в правом верхнем углу экрана.

​Нажимаем сюда, чтобы найти нужное значение в БД
В появившемся окне введём значение, которое нужно найти, — 14000000 — и нажмём «Найти далее».

Multiple Choice

Какой параметр поиска нужно задать в Excel для нахождения значения 14000000?

• 14000000

• 1400000

• 140000

• 14000

Multiple Choice

Как найти значение 14000000 в базе данных Excel?

• Нажать на кнопку "Найти"

• Перейти на вкладку "Главная"

• Ввести значение в ячейку

• Выделить ячейку