Лабораторная работа №7

Работа с базами данных в Ms. Excel

Структурирование и отбор данных в ЭТ.

Часть I.

Упражнение №1. Сортировка (упорядочение) записей списка. Рассмотрим заданную таблицу УЧЕТ ТОВАРОВ НА СКЛАДЕ, представленную ниже. Таблица имеет вид базы данных, состоящей из записей продажи товаров со склада. Запись указывает, какой организации продан товар, когда проведена продажа, товар, единицу измерения товара, его стоимость и количество. В столбцах Дебит и Кредит заносится стоимость покупки и долг перед организацией, т.е. Цена*Кол-во. В последнем столбце указывается форма оплаты: безналичный расчет (б/р), бартер (бар), наличный расчет (н/р).

С оздайте эту таблицу на Листе 1 новой Книги .

Технология работы:

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

2. Курсор установить в область таблицы, выполнить команду Данные/Сортировка. В первом уровне сортировки выбрать поле Организация, во втором – Товар, в третьем – Количество1.

3. Просмотрите результаты сортировки.

4. Лист1 переименуйте в Сортировка.

Упражнение №2. Фильтрация(выборка) записей списка.

Автофильтр:

1. Скопируйте таблицу с Листа Сортировка на Лист2 и назовите новый лист Автофильтр.

2. Пусть нам необходимо выбрать из данного списка только те строки, где есть запись АОЗТ«Белокуриха». Выполнить команду Данные/Фильтр/Автофильтр.

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

4. Щелкнуть на значок в столбце Организация и выбрать АОЗТ«Белокуриха». Появились только те записи, где присутствует указанная организация. Чтобы вернуть все записи, надо опять щелкнуть на значок и выбрать строку Все.

5. Вывести на экран записи, содержащие организацию АОЗТ«Белокуриха», где в столбце «Товар» присутствует «хлеб», т.е. осуществить выборку по двум полям. Вернуть все записи.

6. Вывести на экран записи, содержащие организацию АОЗТ«Белокуриха», в которых цена товара не превышает 3 000. Для выборки по столбцу «Цена» при открытии меню выбрать строку Условие. В появившемся окне Пользовательский автофильтр при помощи значков открывшегося меню установить условие <3 000 в верхней строке. Вернуть все записи.

7. Вывести на экран записи, содержащие колхоз «Восток» и дату покупки товара в промежутке после 2 марта до 13 июня. В данном случае в окне Пользовательский автофильтр заполнить обе строки. Вернуть все записи.

8. Вывести на экран записи, содержащие колхоз «Восток», а в поле Цена установить условие: >700, но <16 000.

---

Расширенный фильтр:

1. Прочитать справку Excel Фильтры/Расширенные: примеры условий отбора расширенного фильтра.

2. Скопируйте таблицу с лист Сортировка на Лист3 и дайте имя Расширенный фильтр.

3. Выполнить задание по автофильтру, воспользовавшись командой Расширенный фильтр.

• н иже таблицы, оставив пустые 2-3 строки заголовка таблицы, например, в строку 23. В строке 24 сформировать критерий отбора записей. В столбец Организация ввести АОЗТ«Белокуриха», в столбец Товар – «хлеб». Установить курсор в область таблицы, в которой будет производиться выборка данных.

• Далее выполнить команду Данные/Фильтр/Расширенный фильтр. В появившемся диалоговом окне в строке Исходный диапазон появится запись A2:J20.

• В диалоговом окне установить курсор в строку Диапазон условий, перейти в таблицу и выделить диапазон A23:J24.

• Для того чтобы данные печатались в другом месте, необходимо активизировать кнопку Скопировать результат в другое место и также указать диапазон, куда будут выводиться отсортированные данные, например, $A$26:$J$36.

Начиная со строки и ниже, расположен результат выполнения расширенного фильтра (см рис. 4).

4. Выполнить задания П.6 из упражнения. В данном случае для выборки товаров, стоимость которых не превышает 16000, в диапазоне критериев введите «<16000»

Примечание. Алгоритм выполнения расширенного списка можно прочитать в справке Фильтры/Расширенные: Фильтрация списка с помощью расширенного фильтра.

Рис. 4

---

Лабораторная работа с базами данных в Ms. Excel

Структурирование и отбор данных в ЭТ.

Часть II.

Упражнение №3. Автоматическое подведение итогов. Пусть нам нужно подвести итоги о продаже товаров каждой организации, затем еще итоги в каждой организации по датам.

Технология работы.

1. Скопируйте таблицу с листа Сортировка на новый лист, назовите Итоги.

2. Сначала нужно упорядочить таблицу по полю Организация, второй уровень сортировки – Дата.

3. Выполнить команду Данные/Итоги. В появившемся окне в первой строке выбрать Организация, в строке Операция из списка выбрать Сумма, в третьей строке выбрать поля, по которым подводить итоги, Дебет и Кредит.

4. Просмотреть результаты. В левой половине экрана появились символы структуры (Значки «+» и «-»). Ознакомьтесь с их назначением.

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

Упражнение №4. Консолидация данных (способ получения итоговой информации из разных листов, одинаковых по структуре).

1. Предположим, есть три таблицы одинаковой структуры УЧЕТ ПРОДАЖИ ТОВАРА одной фирмы, имеющей три склада в разных точках города. Создадим эти таблицы. Скопируйте таблицу с листа Сортировка на новые листы, назовите их «склад1», «склад2», «склад3». Измените некоторые данные на этих листах, например, для организации АОЗТ«Белокуриха» для товара хлеб Количество1, Количество2 или Цену.

2. Пусть необходимо подвести итоги о продаже хлеба организации АОЗТ«Белокуриха» в сумме в этих трех точках.

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

4. Дать следующему листу имя Консолидация. Находясь на этом листе, выполнить команду Данные/Консолидация.

5. В появившемся диалоговом окне выбрать функцию Сумма.

6. Щелкнуть мышью в поле Ссылки, перейти на лист Склад1 и выделить итоговую сумму продажи сахара АОЗТ«Белокуриха». Данные появятся в поле ссылки. Нажать кнопку Добавить.

7. То же самое выполнить для листов Склад2, Склад3.

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

---

Лабораторная работа с базами данных в Ms. Excel

Структурирование и отбор данных в ЭТ.

Часть III.

Упражнение №5. Сводные таблицы. Команда Данные/ Сводная таблица вызывает Мастера сводных таблиц для построения сводов, т.е. итогов определенных видов на основных списков данных. Предположим, нам необходимо узнать, на какую сумму закупила товар какая-нибудь организация.

1. Скопировать таблицу с листа Сортировка на новый лист и переименовать – Сводная .

2. В ыполнить команду Данные/Сводная таблица.

3. Выполнить первые два шага с Мастером самостоятельно.

4. На третьем шаге выбрать Новый лист, Макет.., в появившемся диалоговом окне (рисунок 5) перетащить значки с названиями столбцов нашей таблицы следующем образом:

информация, которую мы хотим разместить в строках, например:

• Т

  рис.1.

  овар. Схватить мышью значок и перетащить в область Строка

перенести значок Дебет в область Данные.

5. Если выполнить двойной щелчок на перенесенных значках, можно отредактировать их назначение. На значках, помещенных в окне Строка, активизировано состояние строки, в окне Столбец – столбца. Двойной щелчок на значке в области данных позволяет выполнить операцию.

6. Просмотреть полученную таблицу (рис.6).

7. Установить курсор в область сводной таблицы и выполнить команду Данные/ Сводная таблица.

8. Программа предлагает создать на новом листе сводную таблицу или создать на листе существующей таблицы.

9. Перенести поле Организация на область Столбец.

10. Просмотреть полученную таблицу (рис.7).

рис.3

рис.2

5. Можно менять группировку строк и столбцов следующим образом: в полученной таблице поле Организация перенести влево. Ваша таблица примет другой вид. В конце таблицы – общая сумма (рис.8а).

6. Если нужна группировка не по товарам, а по Организациям, поле Товар перетащить вправо (рис8б).

а) б)

рис.4

Фильтрация сведенных данных:

13. Перетащить в полученной таблице поле Организация в ячейку А1.

14. Щелкнуть по язычку Все и выбрать интересующую вас организацию (рис. 9).

---

Смотрите также файлы

• LR_9_Excel_СозданиеТабл_ОАО_ЗАО_ч1.doc

• LR_9_Excel_СозданиеТабл_ОАО_ЗАО_ч2.doc

• LR_1_Excel_решение_кв_ур_ПРИМЕР.doc

• LR_4_5_Exsel_расчетЗП.doc

• LR_6_Excel_Оптимизация.docx