Построение и обработка списков (баз данных)

Цель работы: научиться создавать при помощи EXCEL базу данных, содержащую различные сведения о работе фирмы.

Задачи:

1. Создание списков.

2. Организация связей между списками.

3. Создание БЛАНКА ЗАКАЗА.

Теоретические сведения.

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

· Название столбца занимает одну ячейку;

· Все данные в ячейках столбца представлены в одном формате;

· Все названия столбцов размещены в одной строке и образуют строку заголовка списка;

· Данные помещаются в строке следующей за строкой заголовков.

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

Задание 1. Создание списков.

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

1.1. Создание списка КЛИЕНТЫ.

1. Создайте рабочую книгу ( Файл / Создать ).

2. Присвойте первому рабочему листу имя Клиенты.

3. Введите в ячейки A1: F1 следующие заголовки:

· Название фирмы

· Код

· Контактная персона

· Город

· Телефон

· Скидка (%)

4. Отформатируйте таблицу, используя пункт меню Формат / Автоформат. В списке форматов выберите Список1.

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

1.2. Создание списка ТОВАРЫ.

1. Перейдите на второй лист рабочей книги и присвойте ему имя ТОВАРЫ.

2. Список ТОВАРЫ должен состоять из трех столбцов: Номер, Наименование товара, Цена (USD).

3. В ячейки А1-С1 введите соответствующие заголовки.

4. Введите записи в список ТОВАРЫ.

5. Отформатируйте таблицу аналогично списку КЛИЕНТЫ.

1.3. Создание списка ЗАКАЗЫ.

1. Перейдите на третий лист рабочей книги и присвойте ему имя ЗАКАЗЫ.

2. Определяем следующую структуру списка:

3. Введите данные по следующим полям: Месяц, Дата, Номер заказа, Номер товара, Код заказчика, Количество.

4. Отформатируйте таблицу по своему усмотрению.

5. Заполним автоматически столбцы Наименование товара, Фирма, Сумма, Скидка, Оплачено в списке ЗАКАЗЫ, используя данные из соответствующих столбцов списка ТОВАРЫ и списка КЛИЕНТЫ соответственно.

Автоматическое заполнение столбцов.

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

1) СИНТАКСИС функции ПРОСМОТР:

ПРОСМОТР (искомое значение; просматриваемый вектор; вектор результатов)

Вектор в MS Excel - это массив, который содержит только одну строку или один столбец.

Искомое_значение - это значение, которое ПРОСМОТР ищет в первом векторе.

Искомое_значение может быть числом, текстом, логическим значением, именем или ссылкой, ссылающимися на значение.

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

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

2) СИНТАКСИС функции ЕСЛИ:

ЕСЛИ (Р, А, В), где Р - логическое выражение, А – значение, вычисленное при истинном значении Р, В – значение, вычисленное при ложном значении Р.

С помощьюфункции ЕСЛИ можно задать отмену заполнения ячеек в том случае, если запись не введена, что позволит избежать появления значений ошибки.

1. Заполним столбец Наименование товара. Для этого.

· Сделайте текущей ячейку E2.

· В строке формул введите следующую формулу:

=ПРОСМОТР(D2; Товары! $A$2: $A$10; Товары! $B$2: $B$10) , где ячейка D2 – искомое значение, которое необходимо найти в просматриваемом векторе, диапазон $A$2: $A$10 – просматриваемый вектор (столбец Номер товара в списке Товары, $B$2: $B$10 – вектор результатов (столбец Наименование товара в списке Товары).

· Скопируйте формулу в смежные ячейки E3: E12.

2. Используя функцию ПРОСМОТР, заполните самостоятельно столбец Фирма.

3. Определим значение столбца Сумма: Цена (список Товары)*Количество (список Заказы ).

4. Для этого в ячейку I2 введите формулу: =ЕСЛИ(H2=" "; " "; H2*ПРОСМОТР(D2; Товары! $A$2: $A$10; Товары! $C$2: $C$10)), где H2 – ячейка с количеством, D2 - просматриваемая ячейка, диапазон $A$2: $A$10 – просматриваемый вектор (столбец Номер товара в списке Товары), а диапазон $C$2: $C$10 – вектор результатов (столбец Цена в списке Товары).

5. Т. О. если ячейка Н2 = 0 (данные отсутствуют), то возвращаемое значение отсутствует (‘’’’), если ячейка Н2 содержит данные, то возвращаемое значение равно H2*ПРОСМОТР(D2; Товары! $A$2: $A$10; Товары! $C$2: $C$10).

6. Рассчитайте значения столбца Скидка. Для этого в ячейку J2 введите формулу: =I2*ПРОСМОТР(F2; Клиенты! $B$2: $B$10; Клиенты! $F$2: $F$10), где диапазон $B$2: $B$10 - просматриваемый вектор (столбец Код заказчика в списке Клиенты), а диапазон $F$2: $F$10 – вектор результатов (столбец Скидка в списке Клиенты).

7. Рассчитайте значения столбца Оплачено.

Задание 2. Создание Бланка заказов.

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

1. Откройте 4-ый лист вашей рабочей книги. Дайте ему название Бланк.

2. В область для номера заказа введите любой номер из списка Заказы

3. В остальные выделенные области будем вносить формулы:

· В область Дата внесите формулу, позволяющая вводить автоматически дату

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Дата из этого же списка)

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

= ПРОСМОТР (Ячейка Номер заказа из Бланка; поле Номер заказа из списка Заказы; поле Фирма из этого же списка)

· Аналогично заполним выделенные области Количество, Скидка, К оплате.

· В область Доверенность выдал внесите формулу, позволяющая вводить автоматически фамилию: = ПРОСМОТР (ячейка Название фирмы из Бланка, Поле Название фирмы из списка Клиенты, поле Контактная персона из этого же списка).

· Аналогично заполните область Телефон.

· В область Цена единицы внесите формулу: =ПРОСМОТР (ячейка Название товара из Бланка, поле Наименование товара из списка Товары, поле Цена из этого же списка)

4. Внесите в ячейку G11 для поля Оформил свою Фамилию.

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

Контрольные вопросы

1. Что такое список.

2. Обязательные требования к оформлению списка.

3. Требования к полям списка.

4. Можно ли использовать объединение ячеек при создании списка.

5. Объясните действие функции ПРОСМОТР.

6. Использование функции ЕСЛИ при работе со списками.

Задания для самостоятельной работы.

1. Создайте таблицу «Сводная сессионная ведомость» вашей группы. Таблица состоит из следующих списков:

1. Список СТУДЕНТ. Поля: Фамилия студента, Предмет, Оценка, Дата экзамена

2. Список ПРЕДМЕТ. Поля: Фамилия преподавателя, Предмет, Дата экзамена, Аудитория.

2. Создайте Бланк для разрешения на пересдачу экзамена, который должен автоматически заполняться по данным, содержащимся в списках. Бланк должен заполняться, если оценка равна 2. Используйте для этого функцию ЕСЛИ (оценка=2, печатается фамилия, иначе - ‘’ ‘’).

ЛАБОРАТОРНАЯ РАБОТА №9.

Сводные таблицы

Цель работы: изучить механизмы подведения Итогов посредством объединения (консолидирования) данных разных источников.

Задачи:

1. Консолидирование данных из разных списков.

2. Конструирование сводных таблиц.

3. Анализ данных в сводных таблицах

Теоретические сведения.

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

Область источник Лист 2

Область источник Книга 1/лист1

Область источник Лист 1

Область консолидации

В Excel существуют операции, позволяющие выполнять несколько вариантов консолидации списков.

· По расположению

· По категориям

· С помощью сводных таблиц

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

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

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

Создание и обработка сводных таблиц осуществляется с помощью Мастера сводных таблиц, Данные / Сводная таблица.

Задание 1. Консолидация данных по расположению.

1. Подготовим исходные данные.

· В качестве таблицы источника выберем «Ведомость» (см. пред. работа).

· С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (октябрь и ноябрь).

· Скопируем их и разнесем по разным листам книги. Листы назовем Октябрь и Ноябрь.

· Из таблиц удалим столбцы Стаж и Месяц.

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

2. Выполним консолидацию данных по расположению.

· Добавим в книгу новый лист Консолидация.

· Активизируем ячейку А1 и выполним команду Данные / Консолидация.

· В открывшемся окне введем диапазон, занимаемым первым списком – Октябрь! $A$3: $I$7. Нажмем кнопку Добавить.

· Далее введем диапазон, занимаемым вторым списком – Ноябрь! $A$3: $I$7. Нажмем кнопку Добавить.

· Установим функцию из раскрываемого списка, например Сумма

· Установим флажки на опциях: Использование в качестве имен, или Создавать связи с исходными данными (но не одновременно). Нажмем ОК.

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

Задание 2. Консолидация по категориям.

1. Подготовим исходные данные.

· В качестве таблицы источника выберем «Ведомость» (см. пред. работа).

· С помощью команды Фильтр / Автофильтр выберем из нее последовательно записи, относящиеся к месяцам (сентябрь, октябрь и ноябрь).

· Скопируем их и разнесем по разным листам книги. Листы назовем Сентябрь, Октябрь и Ноябрь.

· Из таблиц удалим столбцы Стаж и Месяц.

· Данные в источниках дополнять не будем. Количество записей в каждой из таблиц отличается.

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

Задание 3. Консолидация списков с разным числом полей.

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

⇐ Предыдущая 2 3 4 5 678 9 10 11 Следующая ⇒