Создание списка данных в Excel

Лабораторная работа № 5-6

Тема: Использование Excel как базы данных.

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

Списки Excel, как локальная база данных

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

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

Как правило, работа с любыми списками состоит из следующих операций:

- Ввод данных.

- Фильтрация списка с целью отображения отдельных строк, отвечающих определенным условиям

- Сортировка списка

- Вставка формул для вычисления промежуточных итогов

- Создания формул, для вычисления результатов, которые получены на основе списка, отфильтрованного по определенным условиям.

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

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

Списки данных должны удовлетворять ряду правил:

- Первая строка списка (строка заголовка) должна содержать понятные описательные подписи (по одной для каждого столбца.

- Каждый столбец должен содержать только один тип информации.

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

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

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

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

- Прежде чем вводить данные, отформатируйте столбец. Например, если столбец содержит стоимость реализации, записи в нем должны отображаться в денежном формате.

- Рекомендуется именовать рабочий лист названием списка.

Пример

Список «Реализация» стройматериалов. Поля: номер, дата, продавец, телефон продавца, товар, цена, количество.

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

2. В таблицу Реализация добавить поле «Стоимость» и рассчитать стоимость реализации по формуле цена*количество.

3. Заполнить список реальным содержимым, состоящим не менее чем из 30записей за временной период равный 2 месяца. При вводе сведений о реализации товара и сотруднике, его осуществившем, необходимо воспользоваться инструментом выпадающих списков. Для автоматического проставления цены выбранного товара, атакже телефона сотрудника воспользоватьсяфункцией ВПР.

4. Упорядочить список по наименованию товаров, для каждого наименования – ФИО продавца, для каждого продавца – стоимость проданных товаров.

5. Составить список продавцов, реализовавших сайдинг в количестве от 10 до 20 шт.

6. Составить список товаров, проданных в первой декаде апреля сумма реализации которых выше среднего значения.

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

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

Ход выполнения работы

Создание списка данных в Excel

Создайте три новых рабочих листа.

Присвойте первому рабочему листу имя ТоварыВведите в ячейки A1: B1 следующие заголовки: Товар, Цена. Заполните список сведениями о реализуемых товаров.

Присвойте второму рабочему листу имя Сотрудники.Введите в ячейки A1: B1 следующие заголовки: Сотрудник, Телефон. Заполните список сведениями о продавцах.

ПоскоДля того, чтобы при вводе телефона

Присвойте третьему рабочему листу имя Реализация.Определяем следующую структуру списка.

Установите курсор в ячейку В2. Перейдите на вкладку Вид, в группе Окно выберите командуЗакрепить областии затем пункт Закрепить области

Ввод данных о реализации

- В ячейку А2 введите начальное значение 1. Выполните команду Прогрессия (вкладка Главная, группа Редактирование, кнопка Заполнить). Задайте в диалоге опцию Расположение по столбцам, Арифметическая прогрессия, Шаг 1, Предельное значение 30. Заполняются ячейки А3:А42.

- В ячейку B2 введите произвольную дату, например 17 марта. Для того, чтобы заполнить датами все строки списка воспользуйтесь Маркером заполнения. Выделите ячейку В2.Наведите указатель мыши на правый нижний угол (указатель мыши É) и нажав левую кнопку мыши протяните до конца диапазона (ячейка В31)

- В ячейку С2 необходимо вводить данные о сотрудниках, которые расположены на отдельном рабочем листе «Сотрудники». Для этого необходимо присвоить имя диапазону, где содержится список-справочник. Перейдите на лист Сотрудники. Выделите диапазон ячеек А2:А5.Затем щелкните в поле Имя и введите имя Сотрудники. Для подтверждения выбранного имени нажмите <Enter>.

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

На вкладке ленты Данные в группе Работа с данными нажмите кнопку Проверка данных.В поле со списком Тип данных выберите пункт Список, а в поле Источник введите =Сотрудники. Щелкните на кнопке ОК

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

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

В окне ввода аргументов для функции укажите следующие значения:

- Искомое значение – ФИО сотрудника, которое функция должна найти в крайнем левом столбце справочника «Сотрудники». В нашем случае – Автоменко Максимиз ячейки С2.

- Таблица – таблица, из которой берутся искомые значения, то есть наш справочник. Для ссылки перейдите на лист Сотрудники и выделите диапазон ячеек с данными A2:B5. Для того, чтобы ссылка на диапазон оставалась неизменной закрепите ее абсолютными ссылками – нажмите клавишу F4.

- Номер_столбца – порядковый номер (не буква!) столбца в справочнике из которого будем брать значения цены. Первый столбец справочника с названиями имеет номер 1, следовательно, нам нужна цена из столбца с номером 2.

- Интервальный_просмотр (RangeLookup) – введите 0.

Нажмите ОК и скопируйте введенную функцию на весь диапазон

- В ячейку Е2 необходимо вводить данные о товарах, которые расположены на отдельном рабочем листе «Товары». Для этого необходимо присвоить имя диапазону, где содержится список-справочник. Перейдите на лист Товары. Выделите диапазон ячеек А2:А6.Затем щелкните в поле Имя и введите имя Товары. Для подтверждения выбранного имени нажмите <Enter>.

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

На вкладке ленты Данные в группе Работа с данными нажмите кнопку Проверка данных.В поле со списком Тип данных выберите пункт Список, а в поле Источник введите =Товары. Щелкните на кнопке ОК

- В ячейке F2 необходимо предусмотреть автоматическое заполнение цены товара. Выделите ячейкуF2 и откройте мастер функции. В категории Ссылки и массивынайдите функцию ВПРи нажмите ОК.

В окне ввода аргументов для функции укажите следующие значения:

- Искомое значение – то наименование товара, которое функция должна найти в крайнем левом столбце справочника. В нашем случае – Гидроизоляция из ячейки Е2.

- Таблица – таблица, из которой берутся искомые значения, то есть наш справочник. Для ссылки перейдите на лист Справочник и выделите диапазон ячеек с данными A2:B6. Для того, чтобы ссылка на диапазон оставалась неизменной закрепите ее абсолютными ссылками – нажмите клавишу F4.

- Интервальный_просмотр (RangeLookup) – введите 0.

Нажмите ОК и скопируйте введенную функцию на весь диапазон.

- В ячейке G2 необходимо ввести количество реализованной продукции

- В ячейке Н2 необходимо ввести формулу для подсчета стоимости (цена * количество) реализации =G2*F2

Сортировка списка

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

Для того, чтобы отсортировать список по нескольким полям, например, по полю Товар, ФИО продавца и Стоимость

Автофильтр

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

Задание 1.

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

Для того, чтобы выбрать данные о реализации одного товара – Сайдинг, выберите требуемое значение из развернувшегося набора в поле Товары.

Результат выполнения фильтрации показан на рис.

Для того, чтобы выбрать данные о количестве реализации – от 10 до 20 шт.Щелкните на стрелочке в заголовке поля Количество и выполните команду Числовые фильтры иМежду. В появившемся диалоговом окне установите параметры, как это указано на рис.

Фрагмент искомого списка приведен на рис.

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

Для копирования отфильтрованного списка выделите весь диапазон ячеек с данными нажимая на заголовки столбцов от A до Н. Выполните команду Копировать. Перейдите на новый лист. Выделите ячейку А1. Нажмите кнопкуВставить.

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

Задание 2.

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

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

Для того, чтобы выбрать данные о количестве реализации за первую декаду апреля щелкните на стрелочке в заголовке поля Дата и выполните команду Фильтры по дате иМежду. В появившемся диалоговом окне установите параметры, как это указано на рис.

Фрагмент искомого списка приведен на рис.

Сортировка

Дата добавления: 2018-06-01; просмотров: 766; Мы поможем в написании вашей работы!

Поделиться с друзьями:

12 Следующая ⇒

Мы поможем в написании ваших работ!