Добавление итогов в базу данных

Посчитать суммарную прибыль при продаже всех продуктов можно при помощи функции СУММ. Если же воспользоваться пунктом меню Итоги, то появляется возможность рассчитать сумму прибыли по каждому товару или по каждому поставщику. Итак, для суммирования прибыли по каждому из товаров сделаем следующее: отсортируем БД по наименованию товаров (рис. 3) и выполним команду Данные Итоги (рис. 4). Рабочий лист примет вид изображенный на рис. 5.

Рис. 4. Диалоговое окно Промежуточные итоги

Рис. 5. Итог по прибыли по всем видам товара

Фильтрация базы данных

Процесс поиска и отбора информации в базе данных MS Excel называется фильтрацией. В MS Excel есть два вида фильтра: Автофильтр и Расширенный фильтр.

Автофильтр

Для включения автофильтра необходимо:

1. Щелкнуть в любом месте базы данных, в нашем случае диапазон A1:I18.

2. Выполнить команду Данные Фильтр Автофильтр. Щелкнуть по кнопке списка справа от нужного поля (например, поле Наименование товара). Окно БД примет вид, изображенный на рис. 6. В качестве условия отбора можно выбрать либо любое значение из списка, либо пункт Условие.

Рис.6. БД после применения команды Автофильтр

Выберем в качестве условия значение Колбаса. В результате в БД останется информация, касающаяся только поставок колбасы (рис. 7).

Рис. 7. Фрагмент отфильтрованной БД

Результат будет тем же, если из списка предложенных фильтров выбрать Условие…, и в открывшемся диалоговом окне (рис. 9.8), ввести в качестве условия "равно Колбаса".

Рис. 8. Диалоговое окно Пользовательский Автофильтр

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

Рис. 9. Фрагмент диалогового окна Пользовательский Автофильтр

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

Для выделения из БД более сложных условий можно воспользоваться командой Данные Фильтр Расширенный фильтр. Рассмотрим работу с расширенным фильтром на примере выделения из БД записей донецких производителей колбасы. Для этого определим область для хранения условий отбора. Каждое условие записывается в две ячейки: в верхнюю - имя поля, в нижнюю - знак отношения (>,<,>=,<=,< >) и значение. В нашем случае в ячейку K1 введем Наименование товара, в ячейку K2 - Колбаса, в ячейку L1 - Город, в ячейку L2 - Донецк (рис. 10).

Рис. 10. Область для хранения условий отбора

Теперь выполним команду Данные Фильтр Расширенный фильтр (рис11).

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

В данном случае два условия соединены логическим действием "И". Для объединения с помощью "ИЛИ" необходимо между именем поля и условием пропустить строчку (рис.12).

Рис. 12. Соединение условий при помощи логического "ИЛИ"

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

Сводная таблица

Сводные таблицы - одно из наиболее мощных средств Excel по работе с базами данных. Они полезны как для анализа, так и для обобщения информации, хранящейся в БД.

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

· в списке или базе данных MS Excel - если данные берутся с одного рабочего листа;

· во внешнем источнике данных - если данные берутся из внешней базы данных;

· в нескольких диапазонах консолидации - если данные берутся с нескольких рабочих листов;

· в другой сводной таблице - если сводная таблица создается на основании данных другой сводной таблицы.

В этом же диалоговом окне указывается вид создаваемого отчета - сводная таблица или сводная диаграмма.

Рис. 13. Первое диалоговое окно Мастер сводных таблиц

В рассматриваемом примере переключатель устанавливается в положение В списке или базе данных MS Excel.

В поле Диапазон второго диалогового окна Мастер сводных таблиц (рис. 14) необходимо указать диапазон, на основании которого строится сводная таблица. В нашем случае: 'Поставщики товара'!$A$:$I$18.

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

Рис. 14. Второе диалоговое окно Мастер сводных таблиц

Рис. 15. Третье диалоговое окно Мастер сводных таблиц

Структуру сводной таблицы можно создать, воспользовавшись кнопкой Макет…, третьего диалогового окна Мастер сводных таблиц.

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

В окне имеются четыре области:

· Строка - для использования данных поля, расположенного в этой области, в качестве заголовка строки;

· Столбец - для использования данных поля, расположенного в этой области, в качестве заголовков столбцов;

· Данные - для суммирования значений поля, расположенного в этой области, в ячейках сводной таблицы;

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

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

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

Рис. 17. Диалоговое окно Вычисление поля сводной таблицы

Кнопка Параметры… третьего диалогового окна Мастер сводных таблиц позволяет задать некоторые параметры, определяющие вид сводной таблицы (рис. 18). Например, в поле Имя можно задать название таблицы. По умолчанию сводные таблицы называются Сводная таблица 1, Сводная таблица 2 и т.д. Устанавливая флажок Общие итоги по столбцам или флажок Общие итоги по строкам можно подвести итоги по столбцам или строкам в сводной таблице. Установка флажка Автоформат позволяет пользоваться средствами автоформата MS Excel. Установив флажок Сохранить данные вместе с таблицей можно создать дополнительную копию данных, позволяющую быстрее пересчитывать сводную таблицу при ее изменении.

Рис. 18. Диалоговое окно Параметры сводной таблицы

После щелчка по кнопке Готово в третьем диалоговом окне Мастер сводных таблиц на рабочем листе будет создана сводная таблица, представленная на рис. 19.

Рис. 19. Сводная таблица подсчета прибыли по каждому товару

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

1. Как сделать так, чтобы текстовые данные, содержащиеся в ячейках, полностью помещались бы по ширине столбца?

2. Как просмотреть и отредактировать формулу, содержащуюся в ячейке?

3. В чем состоит опасность применения абсолютной адресации к ячейкам при записи формул?

4. В чем состоит удобство применения относительной адресации к ячейкам при записи формул?

5. Какой тип адресации Excel использует по умолчанию?

6. Что такое диапазон?

7. Какой диапазон называется трехмерным?

8. Назовите три координаты трехмерного диапазона.

Лабораторная работа № 7. Создание графического документа в сфере профессиональной деятельности. Обработка графического объекта в графических редакторах. Изменение размеров, оптимизация.

Цель работы

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

.

Требование к отчету

Отчет о проделанной работе должен содержать:

– название и цель работы;

– описание основных этапов работы и результатов их выполнения;

– письменные ответы на контрольные вопросы