Файл: Лабораторная работа ms excel Ввод и форматирование данных Цель работы отработка базовых навыков работы в ms.doc

Рис. 1. База данных магазина «Ирикон»

- Отобрать из базы данных магазина товары, стоимость партии которых находится в пределах от 15 000 до 30 000 руб. включительно.

- Отобрать из базы данных магазина товары, поступившие не раньше 10.09.2007 и не позднее 21.11.2007.

- Отобрать из базы данных товары, которые поступили на склад в коли­честве больше 50 штук, и стоимость партии которых была меньше 1 500 000 руб.

- Отобрать из базы данных магазина следующие товары: Ноутбук ASUS A3L, стоимость которого менее 29000 руб., Мобильный телефон Nokia 8800 стоимостью более 36 000 руб. и Телевизор 54-55 см Витязь 54 CTV стоимостью менее 3000 руб.

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

Диапазон критериев с вычисляемым полем Среднее значение и резуль­тат отбора показаны на рис. 2.



Рис. 2. Диапазон условий и результат применения расширенного фильтра для вычисляемого критерия

- Отобрать из базы данных сведения о телевизорах, поступивших на склад с 05 ноября по 21 декабря 2007 г., стоимость партии поставки кото­рых была больше среднего значения по магазину, а также о товаре, сто­имость поставки которого была максимальной. Внимание: предварительно в базе данных измените количество Мобильных телефонов Nokia 8800 на 160.

Диапазоны критериев с безымянными вычисляемыми полями и резуль­тат отбора показаны на рис. 3.



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

При формировании критериев для ограничения сроков поставки теле­визоров была применена функция работы с датами ДатаЗнач, преобра­зующая дату из текстового представления в формат даты. Для выборки информации о других товарах с максимальным объемом поставки был применен символ шаблона *. Без указания данного символа информа­ция о фотоаппарате будет отсутствовать.

Задание для самостоятельного выполнения

Отобрать из базы данных сведения о ноутбуках, поступивших на склад с 10 октября по 28 октября 2007 г., стоимость партии поставки кото­рых была меньше среднего значения по магазину, а также о товаре, сто­имость поставки которого была минимальной.

---

Лабораторная работа № 6. ФОРМИРОВАНИЕ ИТОГОВ. СВОДНЫЕ ТАБЛИЦЫ

Цель работы:

- научиться формировать итогов;

- научиться подведению промежуточных итогов;

- создавать и редактировать сводные таблицы;

- использование консолидации данных.

Задание 1. Создайте базу данных объема продаж по регионам. Подумайте, как ускорить данный процесс.



Рис. 1. Фрагмент таблицы объема продаж по регионам

Для получения различных итоговых сведений по определенным груп­пам данных и в целом по списку используется специальная операция, запускаемая командами: Данные → Итоги.

Аналогом операции является специальная встроенная функция ПРОМЕЖУТОЧНЫЕ ИТОГИ.

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

Технология получения промежуточных итогов заключается в выполне­нии следующих действий:

1. Установить курсор в пределах Списка → Команда Данные → Итоги.

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



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

При необходимости можно установить флажки в нижней части диалогового окна.

• 
  Если установлен флажок Заменить текущие итоги, то автома­тически будут удалены старые существующие итоговые значе­ния и заменены новыми, рассчитанными по другим формулам.
  
• 
  Если установлен флажок Конец страницы между группами, то после каждого промежуточного итога вставляется разрыв страницы (т.е. каждая группа значений данных выводится на отдельном рабочем листе).
  
• 
  Если установлен флажок Итоги под данными, то промежуточ­ные итоги размещаются под данными.
  
• 
  Кнопка Убрать все очищает заданные параметры промежуточ­ных итогов.
  

---

Таблица 1.

Доступные функции для подведения промежуточных итогов списка

Словесное наименование операции	Номер операции	Встроенная функция – аналог операции
Среднее	1	СРЗНАЧ
Количество чисел	2	СЧЕТ
Количество	3	СЧЁТЗ
Максимум	4	МАКС
Минимум	5	МИН
Произведение	6	ПРОИЗВЕД
Смещенное отклонение	7	СТАНДОТКЛОН
Несмещенное отклонение	8	СТАНДОТКЛОНП
Сумма	9	СУММ
Смещенная дисперсия	10	ДИСП
Несмещенная дисперсия	11	ДИСПР

После создания промежуточных итогов Excel автоматически создает структуру документа.

---

Задание 2. Имеется таблица условных объемов продаж товаров по месяцам для нескольких регионов (рис. 1).

Требуется подвести итоги по каждому региону за год.

Технология подведения промежуточных итогов по регионам заключа­ется в следующем.

1. 
   Отсортируйте список по полю, по которому требуется подвести про­межуточный итог, в нашем примере — это поле Регион.
   
2. 
   Выделите любую ячейку списка.
   
3. 
   Выполните команды: Данные → Итоги.
   
4. 
   В диалоговом окне Промежуточные итоги в поле При каждом изме­нении в выберите имя поля, по которому образованы группы данных и для которого необходимо подвести итог, в нашем примере таким полем будет Регион. В поле окна Операция выберите итоговую фун­кцию, в нашем случае такой операцией будет Сумма. В поле Доба­вить итоги по пометьте флажком те поля, по которым требуется под­вести итоги, в данном случае таким полем является Объем продаж. Установите флажки в нижней части диалогового окна, если это необ­ходимо.
   
5. 
   Для закрытия диалогового окна щелкните по кнопке ОК. Результаты подведения итогов представлены на рис. 14.3.
   

Для облегчения анализа выполнения операции Промежуточные итоги числовые поля с итоговыми сведениями на рисунке снабжены приме­чаниями, содержащими сгенерированные формулы. По Краснодарскому региону текущие сведения раскрыты. По другим регионам — закрыты.

• 
  Для их открытия выполните щелчок мышью по значку +, находящемуся левее итоговой строки; для закрытия  щелчок по значку «-» скроет соответствующие текущие сведения.
  
• 
  Маленькие кнопочки «1», «2» и «3», представленные в левом верхнем углу после подведения промежуточных итогов (рис. 14.3), позво­ляют выводить на экран различные уровни детализации данных.
  

Рис. 3. Фрагмент окна Excel с итогами объема продаж по регионам

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

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

---

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

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

Рассмотрим создание сводной таблицы на примере анализа учета кре­дитных договоров.

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



Рис. 1. Фрагмент таблицы кредитных договоров банка с юридическими и физическими лицами

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

• 
  сколько заключено договоров в каждом филиале банка, и какова доля каждого филиала в общем объеме;
  
• 
  какова общая сумма кредитования юридических и физических лиц в каждом филиале;
  
• 
  в каком филиале было заключено наибольшее количество дого­воров по каждому типу и в какой валюте;
  
• 
  каково распределение кредитных договоров по срокам;
  
• 
  какой отрасли выдается большее количество кредитов в каж­дом из филиалов?
  

Создание сводной таблицы осуществляется выбором команд меню: Данные → Сводная таблица...

Шаг 1. Задание источника данных

• 
  Н а данном этапе (рис. 2) необходимо выбрать, что является источником данных для сводной таблицы.
  

Рис. 2. Диалоговое окно Шага 1
Мастера сводных таблиц и диаграмм

• 
  Установите вид созда­ваемого отчета: сводная таблица.
  

Выполним щелчок левой кнопкой мыши по кнопке окна Далее

---