Лабораторная работа №1. Учет продаж средствами табличного процессора Excel
Цель работы: постановка и решение задачи «Учет продаж»
Используемые средства: функции СУММ(), ПРОМ.ИТОГИ(), БДСУММ(); автофильтр, расширенный фильтр.
В процессах обработки информации с помощью компьютера особо важное место занимают базы данных, которые позволяют заменить традиционные формы хранения картотек, каталогов, счетов на более современную и удобную форму.
База данных представляет собой совокупность информации, упорядоченной определенным образом в рамках строго определенной структуры, наличие которой упрощает и ускоряет процесс поиска и обработки данных.
В Microsoft Excel в качестве базы данных используется список, который представляет собой совокупность строк рабочего листа, содержащих однотипные данные, например, реквизиты счетов, расположенные в столбцах. Данные в столбцах списка должны быть одного типа, например, текст в одном столбце и числа - в следующем. Для такого списка выделяется фиксированная область обычной таблицы. Каждая запись располагается в отдельной строке, а для каждого поля отводится один столбец.
Так как таблица содержит 65536 строк и 256 колонок, то база данных в Excel не может содержать больше, чем 65535 записей (одна строка выделяется под заголовки или названия полей), причем каждая из них не может иметь более 256 полей. Для большинства задач этого вполне хватает.
Рассмотрим возможности, которые предоставляет программа Microsoft Excel по созданию и сопровождению баз данных, а также способы извлечения и обработки содержащейся в них информации и приемы построения на основе полученных данных различных диаграмм.
Технология работы
Создание рабочего листа базы данных
В качестве примера мы создадим простую базу, содержащую данные о продажах, осуществленных менеджерами, а затем рассмотрим, как извлекать из нее нужную информацию и представлять ее графически.
При создании базы данных необходимо придерживаться следующих рекомендаций:
На листе не следует размещать более одного списка. Некоторые функции обработки списков, например, фильтры, не позволяют обрабатывать несколько списков одновременно.
В самом списке не должно быть пустых строк и столбцов. Это упростит и ускорит выделение нужных данных.
Список должен быть организован так, чтобы во всех строках в одинаковых столбцах находились однотипные данные.
Перед данными в ячейки не следует вводить лишние пробелы, так как они влияют на сортировку.
Между названием таблицы и базой данных на рабочем листе следует оставить по меньшей мере одну пустую строку. Это позволит Microsoft Excel автоматически обнаружить и выделить список при выполнении сортировки, наложении фильтра или вставке вычисляемых автоматически итоговых значений
Эти рекомендации следует учитывать при создании списка без использования формы
Наша база данных будет состоять из пяти полей (столбцов) со следующими заголовками(именами полей):
Дата - здесь будут вводиться даты продаж;
Товар – наименование проданного товара;
Клиент - название фирмы-покупателя;
Менеджер - фамилия менеджера, осуществившего продажу,
Сумма - сумма каждой продажи.
После того как структура базы данных будет создана, мы заполним ее записями о том, когда, какой товар, кому, кто из менеджеров и на какую сумму осуществил продажу. Пример заполненной базы приведен в таблице 1.
Этот пример приведен также в файле Учет_продаж.xls.
Таблица 1 – Продажи менеджеров в январе
-
Продажи за январь:
руб.
Дата
Товар
Клиент
Менеджер
Сумма
Итого:
11.01.06
Товар1
Альфа ООО
Иванов
1000
12.01.06
Товар2
Бетта АО
Петров
2000
12.01.06
Товар1
Гамма АО
Петров
15000
14.11.06
Товар3
Дельта ЗАО
Иванов
30000
15.01.06
Товар1
Альфа ООО
Сергеев
1200
16.01.06
Товар3
Гамма АО
Петров
1500
17.01.06
Товар2
Дельта ЗАО
Сергеев
20000
Скопируйте файл Учет_продаж.xls. в свою папку и откройте его.
Введите формулы для вычисления общей суммы продаж и промежуточных итогов (Вам помогут примечания к ячейкам).
Общую сумму продаж за месяц поместите в ячейку D1: СУММ(E4:E100)
Для ввода формулы можно воспользоваться кнопкой Σ (Автосумма) на стандартной панели инструментов
Значения, которые нужно суммировать, расположены в столбце E, начиная с ячейки E4. Так как в базу данных еще будут вноситься записи, общее количество которых заранее не известно, то в формуле следует указать диапазон ячеек, исходя из предполагаемого количества записей, например, - E4:E1000 (максимально возможный диапазон E4:E65533, так как в таблице содержится 65536 строки). Можно задать в диапазоне и весь столбец E:E, так как он содержит только те числовые значения, которые надо суммировать. При вводе новых записей итоговая сумма будет автоматически перевычисляться.
В ячейку G3 введите функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ из категории Математические. В поле Номер_функции введите 9. Это число указывает порядковый номер функции, которую нужно использовать при вычислении промежуточных итогов и соответствует суммированию.
Если Вы все сделали правильно, в ячейках D1 и G3 отобразились одинаковые суммы.
Попытайтесь ответить на вопрос: «Зачем в одном отчете две одинаковые суммы?».
Ответа пока нет? Продолжим работу.