Файл: Структурирование и отбор в Excel.docx

Скачать файл
Заказать решение

ВУЗ: Не указан

Категория: Методичка

Дисциплина: Информатика

Добавлен: 21.10.2018

Просмотров: 612

Скачиваний: 20

ВНИМАНИЕ! Если данный файл нарушает Ваши авторские права, то обязательно сообщите нам.

Лабораторная работа 3


Тема: Структурирование и отбор данных в MS Excel’2007/2010


Цель работы: Освоить структуризацию данных и основные операции по работе с простейшими базами данных в MS Excel.

Содержание работы:

  1. Оформить таблицу в MS Excel’2007/2010.

  2. Осуществить операции с записями, используя Форму.

  3. Выполнить сортировку данных в списке.

  4. Осуществить выборку данных, используя фильтрацию.


Задание 1. Создание таблицы


Рассмотрим заданную таблицу «УЧЕТ ТОВАРОВ НА СКЛАДЕ», представленную на рисунке 1.



Рисунок 1 - Таблица «Учет товаров на складе»


Таблица имеет вид базы данных, состоящей из записей продажи товаров со склада. Запись указывает: организацию, которой продан товар, дата продажа, наименование товара, единица измерения товара, его стоимость и Кол1 – количество товара на складе, Кол2 – количество товара проданного, Кол3 – количество оставшегося товара.

Для создания простейшей базы данных необходимо:

  1. открыть новую книгу MS Excel, сохранить в личной папке под именем «Учет товара»;

  2. на первом листе книги «Учет товара» создать таблицу, представленную на рисунке 1, задав самостоятельно параметры форматирования данных;

  3. переименовать лист на «Данные»;

  4. сохранить изменения.

Задание 2. Работа с данными таблицы через Форму

Для работы с данными через Форму нужно выполнить следующее:

  1. выполнить команду: кнопка Office (или вкладка Файл для версии 2010)→кнопка Параметры Excel →раздел Настройка→ элемент Команды не на ленте списка Выбрать команды из → в списке ниже с помощью полосы прокрутки найти команду Форма и выделить ее;

  2. щелкнуть по кнопке Добавить, после чего в правом поле Настройка панели быстрого доступа появится данная команда;

  3. щелкнуть по кнопке OK;

  4. установить курсор в любую ячейку таблицы и щелкнуть по кнопке Форма на панели быстрого доступа;

  5. в открывшемся диалоговом окне Данные (рисунок 3) дополнить таблицу новыми записями согласно рисунку 2, нажать кнопку Добавить.

Замечание: следует отметить, что имя диалогового окна команды Форма зависит от того, как назван текущий лист, если у листа нет названия, то данное окно будет иметь имя Лист1 или Лист 2 или Лист N.

№ п/п

Организация

Дата

Наименование товара

Единица измерения

Цена

Кол1

Кол2

8

АО "Альянс"

3 фев

мука

уп

288,00р.

235

235

9

АОЗТ "Белокуриха"

12 фев

хлеб

шт

6,50р.

1500

1500

10

к/х "Восток"

12 окт

картофель

кг

15,00р.

10000

10000

11

АОЗТ "Белокуриха"

2 мар

хлеб

шт

6,50р.

2000

2000

12

к/х "Восток"

2 мар

сыр

кг

100,00р.

500

450

13

Бийский маслосырзавод

5 мар

сыр

кг

112,00р.

300

250

14

Бийский маслосырзавод

4 апр

сыр

кг

105,00р.

250

250

15

к/х "Восток"

6 апр

мука

уп

270,00р.

560

500

16

Бийский маслосырзавод

6 май

сахар

кг

30,00р.

23000

23000

17

к/х "Восток"

13 июн

лимон

кг

65,00р.

236

130

18

к/х "Восток"

13 июн

хлеб

шт

7,00р.

12300

12300

19

Бийский маслосырзавод

13 июн

сода

уп

56,00р.

23000

20000

20

АОЗТ "Белокуриха"

4 янв

сок

уп

190,00р.

1500

1200

21

к/х "Заря"

4 ноя

картофель

кг

10,00р.

2500

2500

22

АО "Альянс"

13 янв

лимон

кг

60,00р.

120

120


Рисунок 2 – Вид таблицы с добавленными записями


  1. рассчитать значение Кол3= Кол1 – Кол2; сформировать формулу для ячейки I2, для остальных ячеек формулы получить путем копирования;

  2. подсчитать размер Дебита и Кредита как Цена*Кол1 и Цена*Кол3 соответственно; для этих полей установить тип данных – денежный в р.; сформировать формулы для ячеек J2 и K2 соответственно, для остальных ячеек формулы получить путем копирования;

  3. сохранить изменения.

  4. Замечание: если при вычислении полученный результат не умещается в размер ширины столбца, то в ячейке отображается набор диезов, т.е. ########. Следует увеличить ширину столбца, захватив мышью за его границу и растянув.



Рисунок 3 – Диалоговое окно формы


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

  1. в диалоговом окне Данные (рисунок 3) нажать кнопку Критерии;

  2. ввести в нужном поле условие и нажимать кнопку Далее.

Условия:

  • все записи, у которых цена < 1000 рублей;

  • все записи, у которых цена >6 рублей и товар – хлеб;

  1. удалить последнюю найденную запись, используя форму;

  2. изменить значение в 12 записи в поле Наименование товара сыр на свеклу, используя форму;

  3. сохранить изменения.


Задание 3. Сортировка (упорядочение) записей таблицы


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

Для этого необходимо:

  1. скопировать таблицу на новый лист;

  2. установить курсор в область таблицы, открыть на ленте вкладку Данные;

  3. в области Сортировка и фильтр нажать кнопку Сортировка, после чего появится диалоговое окно Сортировка (рисунок 4);

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

  5. щелкнуть кнопку Добавить уровень, во появившемся втором уровне выбрать Товар, значения сортировать в алфавитном порядке; далее в третьем – Кол2, значения сортировать по возрастанию;

  6. нажать кнопку OK и просмотреть результаты сортировки;

  7. выполнить сортировку по возрастанию для данных поля «№п/п», выделив предварительно весь первый столбец таблицы;

  8. переименовать лист в имя «Сортировка».



Р исунок 4 – диалоговое окно Сортировка


Задание 4. Группировка и структура данных в таблице


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

Для группировки данных следует:

  1. скопировать таблицу с листа Сортировка на новый лист, переименуйте его в Группировка;

  2. вставить пустые строки для разделения групп строк каждой организации друг от друга;

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

  4. в появившемся диалоговом окне Группирование (рисунок 5) выбрать строки;

  5. нажать кнопку OK, после чего слева от рабочей области листа появится значок «-» около данной группы строк; щелчок по данному значку скрывает записи таблицы, а сам значок меняется на «+» (рисунок 6);

  6. аналогичные действия следует выполнить для последующих групп;

  7. отобразить все данные по Бийскому маслосырзаводу.

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



Рисунок 5 – Диалоговое окно Группирование



Рисунок 6 – Вид таблицы с группированными данными


Задание 5. Фильтрация (выборка) записей таблицы


5.1 Использование Автофильтра

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

  1. Предположим необходимо выбрать из данной таблицы только те строки, где есть запись АОЗТ «Белокуриха». Для этого нужно:

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

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

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

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



Рисунок 7 - Вид раскрытого списка автофильтра


  1. аналогично с помощью автофильтра среди записей организации АОЗТ «Белокуриха» отобрать только те, у которых в столбце «Наименование товара» указан «хлеб», т.е. осуществить выборку по двум полям;

  2. вернуть все записи;

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

    • раскрыть список столбца «Цена», выбрать перечень команд Числовые фильтры (рисунок 8), выбрать команду Меньше, после чего появится диалоговое окно Пользовательский автофильтр (рисунок 9);

    • в появившемся окне указать условие <100;

    • нажать кнопку Ok.

    1. вернуть все записи.

    1. Отобразить записи, содержащие организацию колхоз «Восток» и цену товара, удовлетворяющую условию: >30 и 200.

    2. Отобразить записи, содержащие товар – лимон и количество проданного товара равное 40 или >120.




    5.2 Использование расширенного фильтра


    Для использования расширенного фильтра выполним задание из п.5.1. Для этого необходимо:



    Рисунок 8 – Вид списка автофильтра с перечнем числовых фильтров



    Рисунок 9 - Диалоговое окно Пользовательский автофильтр


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

    2. ниже таблицы создать диапазон критериев для задания условия поиска данных, а именно выделить строку заголовков таблицы и скопировать ее в пустую область ниже таблицы (рисунок 10), указать в поле Организация название АОЗТ «Белокуриха»;

    3. выбрать вкладку Данные, на области Сортировка и фильтр нажать кнопку Дополнительно, после чего появится диалоговое окно Расширенный фильтр (рисунок 11);


    Рисунок 10 - Диапазон критериев


    1. в появившемся окне активизировать радиокнопку фильтровать список на месте, проверить указания исходного диапазона (он должен соответствовать диапазону таблицы A1:K22);

    2. установить курсор в поле Диапазон условий диалогового окна, мышью выделить область диапазона критериев (рисунок 10) на листе A24:K25 (рисунок 12), после чего в поле появится диапазон, содержащий имя текущего листа и диапазон выделенных ячеек - 'Расширенный фильтр_1'!$A$24:$K$25; следует обратить внимание на то, что при выделении размер диалогового окна изменяется (рисунок 12);

    3. нажать кнопку Ok, после чего на листе отобразятся только те записи, которые соответствуют указанному критерию в диапазоне критериев (организация АОЗТ «Белокуриха»).















    Рисунок 11 – Диалоговое окно Расширенный фильтр


    1. Далее выборки данных по двум критериям (записи, содержащие организацию АОЗТ «Белокуриха» и товар - хлеб) и отображения результата в другом месте листа необходимо выполнить следующие действия:

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

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

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


    Рисунок 12 – Вид диалогового окна Расширенный фильтр при выделении диапазона критериев


    1. в появившемся диалоговом окне активизировать радиокнопку Скопировать результат в другое место, проверить указание исходного диапазона, отобразить в поле Диапазон условий диапазон ячеек, соответствующий диапазону критериев на листе;

    2. установить курсор в поле Поместить результат в диапазон;

    3. выделить мышью область ячеек на листе где будет располагаться результат выборки, например A27:K33, после чего в поле появится диапазон, содержащий имя текущего листа и диапазон выделенных ячеек - 'Расширенный фильтр_2'!$A$27:$K$33;

    4. нажать кнопку Ok, проанализировать результат.

    1. Осуществить отбор данных по двум критериям (записи, содержащие организацию АОЗТ «Белокуриха» и цену товара, не превышающую 100 рублей) с помощью расширенного фильтра на листе Расширенный фильтр_3, результат отобразить в новом месте. Диапазон критериев должен иметь вид, представленный на рисунке 13.


    Рисунок 13 – Вид диапазона критериев


    1. Осуществить отбор данных по критериям (записи, содержащие организацию колхоз «Восток» и цену товара, удовлетворяющую условию: <20 рублей или >70 рублей) с помощью расширенного фильтра на листе Расширенный фильтр_4, результат отобразить в новом месте. Диапазон критериев должен иметь вид, представленный на рисунке 14.



    Рисунок 14 – Вид диапазона критериев


    1. Осуществить отбор записей, содержащих количество оставшегося товара >200, с помощью расширенного фильтра на листе Расширенный фильтр_5, результат отобразить в новом месте. Сохранить все изменения.


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