Тема: Условное форматирование.

Цель: Знакомство с возможностями условного форматирования таблиц.

Темы: Создание и использование правил условного форматирования.

1. Создайте таблицу, приведенную на рис.4.5.

1.1. Примените к диапазону В3:В14 условное форматирование с помощью набора значков «три сигнала светофора без обрамления», а к диапазону С3:С14 - «пять четвертей».

1.1.1. Активизируйте команду Главная – Стили – Условное форматирование – Наборы значков.

1.1.2. Выберите команду Управление правилами и перейдите в диалоговое окно Диспетчер правил условного форматирования. Ознакомьтесь с возможностями данного окна.

1.2. Создайте правило условного форматирования на основе формулы. Отформатируйте только те значения диапазона В3:В14, которые больше 40%, выделив их красной заливкой. Для этого активизируйте команду Главная – Стили – Условное форматирование – Создать правило. В диалоговом окне Создание правила форматирования выберите Использовать формулу и введите формулу =В3>$А$16. Перейдя в диалоговое окно Формат ячеек, установите нужный формат. Повторите указанные действия для диапазона С3:С14 и порога, записанного в ячейке А17.

Рис.4.5

2. Создайте таблицу, приведенную на рис.4.6.

2.1. С помощью условного форматирования определите повторяющиеся значения в диапазоне с фамилиями.

2.2. Для диапазона В2:В14 выделите значения, превышающие два заказа и значения, равные одному заказу.

2.3. Для диапазона С2:С14 выделите суммы заказов, выше среднего значения и ниже среднего, а также выделите четыре наибольших сумм заказов.

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

2.5. К диапазону Курьер примените условное форматирование Текст содержит и выделите значение Гермес.

Рис.4.6

3. Предъявите результаты преподавателю.

Лабораторная работа № 5.

Тема: Организация таблиц.

Цель: Знакомство с организацией вычислений в таблицах.

Темы: Работа с группами листов. Использование «формулы массива». «Автовычисление», «Автоформатирование». Влияющие и зависимые ячейки.

1. Пользуясь методом группового заполнения листов, создайте на трех листах нового документа таблицу, приведенную на рис.5.1, введя данные в диапазон В4:F8. Дайте листам имена "Таб1", "Таб2", "Таб3".

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

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

2.2. В диапазоне В10:F10 запишите формулы для вычисления суммарной нагрузки по видам нагрузки, пользуясь буфером обмена (ввести формулу, вычисляющую суммарную нагрузку по лекциям в ячейку B10, затем воспользоваться командами Главная – Буфер обмена – Копировать и Главная – Буфер обмена – Вставить, предварительно выделив диапазон вставки).

Рис.5.1

2.3. Запишите формулу для суммирования нагрузки по строкам в ячейку G9.

2.4. Запишите формулу для суммирования нагрузки по столбцам в ячейку G10.

2.5. Запишите формулу для вычисления процентного содержания нагрузки для группы ЕС61-63 в общей сумме часов (ячейка H4).

2.6. Скопируйте данную формулу в диапазон H5:H8, пользуясь автозаполнением.

2.8. Запишите формулу для вычисления процентного содержания лекционной нагрузки в общей сумме часов (ячейка В11).

2.9. Заполните аналогичными формулами диапазон C11:F11, пользуясь командой Главная – Редактирование – Заполнить вправо.

3. Пользуясь автовычислением, определите среднее, минимальное и максимальное значения нагрузки для групп ЕС61-63 и СУ61 и зафиксируйте результаты.

4. Активизируйте режим ручного пересчета формул (Office – Параметры Excel).

4.1. Несколько раз измените значения в таблице и выполните ручной пересчет.

5. Отформатируйте таблицу на листе "Таб2" по образцу, представленному на рис.5.2, обратив внимание на центровку строки заголовка и формат процентного представления чисел в ячейках (H4:H8 и В11:F11).

5.1. Заголовки столбцов оформите с использованием непосредственного форматирования.

5.2. Для форматирования ячеек А10:А11 используйте копирование формата, созданного в п.5.1.

5.3. Отформатируйте таблицу на листе "Таб3", пользуясь функцией автоформатирования.

Рис.5.2

6. Пользуясь командой Формулы – Зависимости формул, выявите влияющие и зависимые ячейки для ячейки G9.

7. Пользуясь "объемной" формулой =СУММ(Таб1:Таб3!G9), вычислите сумму значений в клетках G9 трех листов и зафиксируйте полученный результат в клетке G15 листа "Таб1".

8. Пользуясь командой Главная – Буфер обмена – Вставить – Специальная вставка, уменьшите значения в диапазоне B10:F10 в четыре раза.

9. Реализуйте подсчет суммы значений с последовательным накоплением сумм в столбце Накопленные суммы таблицы, приведенной на рис.5.3. Сумма с накоплением для ячейки С2 – это продажи за январь, для С3 – продажи за январь и февраль, для С4 – продажи за январь, февраль и март и т.д. Для осуществления этого алгоритма примените необходимую адресацию в формуле =сумм(В2:В2), помещенной в ячейку С2 указанного столбца и скопируйте ее в остальные ячейки С3:С14.

Рис.5.3

10. Предъявите результаты преподавателю.

Лабораторная работа № 6.

Тема: Функции.

Цель: Знакомство с использованиемфункций табличного процессора MS Excel.

Темы: Математические, статистические и логические функции. Функции даты и времени. Функции ссылки и массива. Текстовые функции. Функции для финансовых расчетов.

1. Научитесь пользоваться математическими и статистическими функциями.

1.1.Создайте таблицу, приведенную на рис.6.1.

Рис.6.1

1.2. Введите в столбец B функции, указанные в столбце А (столбец А заполнять не надо) и сравните полученные результаты с данными, приведенными в столбце В на рис.6.1.

1.3. Проанализируйте результаты и сохраните созданную таблицу в книге.

2. Научитесь пользоваться логическими функциями.

2.1. Активизируйте второй лист созданной книги.

2.2. Введите таблицу, приведенную на рис.6.2.

2.3. В клетку С2 введите формулу, по которой будет вычислена скидк а и скопируйте ее в диапазон С3:С6:

· если стоимость товара <2000 единиц, то скидка составляет 5% от стоимости товара,

· в противном случае - 10%.

2.4. В клетку D2 введите формулу, определяющую налог и скопируйте ее в диапазон D3:D6:

· если разность между стоимостью и скидкой >5000, то налог составит 5% от этой разности,

· в противном случае - 2%.

Рис.6.2

2.5. Повторите п.2.3 для следующих условий:

· если стоимость товара <2000, то скидка составляет 5% от стоимости товара,

· если стоимость товара >5000, то скидка составляет 15% от стоимости товара,

· в противном случае - 10%.

2.6. В клетку А10 может быть занесена одна из текстовых констант: "желтый", "зеленый", "красный". В клетку А11 введите формулу, которая в зависимости от содержимого клетки А10, будет возвращать значения: "ждите","идите" или "стойте", соответственно.

2.7. Занесите в клетки Е8:E10 три имени: (Лена, Зина, Вера), а в клетки F8:F10 занесите даты их рождений. В клетку E4 введите одно из упомянутых имен.

Пользуясь конструкцией "вложенного" оператора ЕСЛИ, выполните следующие действия:

- проанализировав имя в клетке Е4, запишите в клетку С12 функцию ЕСЛИ, обеспечивающую:

· вывод даты рождения, взятой из соответствующей клетки,

· если же введено неподходящее имя, вывод сообщения: "нет такого имени".

3. Научитесь пользоваться функциями даты и времени, ссылки и массива.

3.1. Активизируйте третий лист книги Имя_6_1.

3.2. Введите в клетку С2 функцию, отображающую сегодняшнюю дату.

3.3. Введите в клетку С3 функцию ДАТА, отображающую произвольно выбранную дату.

3.4. В клетку С5 запишите функцию ВЫБОР, позволяющую вывести название дня недели для даты, введенной в клетку С2 (понедельник, вторник, среда...).

3.5. В клетку С6 запишите аналогичную функцию для даты, введенной в клетку С3.

3.6. Вычислите возраст человека, поместив дату его рождения в клетку С10. Для этого используйте формулу:

= РАЗНДАТ(С10;СЕГОДНЯ();"y")

3.7. Представьте текущее время, используя функции ТДАТА() и СЕГОДНЯ().

3.8. Поместите в соседние ячейки текущую дату и время и дату и время, отстоящую от текущей на трое суток. Найдите количество часов и минут между этими датами, пользуясь форматом [ч]:мм:сс и Общим форматом, а также форматом 13:30. Зафиксируйте результаты и объясните различие.

3.9. Определите номер текущей недели и выведите сообщение:

"Сейчас идет № недели неделя".

3.10. На четвертом листе книги создайте таблицу, приведенную на рис.6.3.

3.10.1. Дайте имена диапазонам клеток, определяющим полученную стипендию за каждый семестр.

3.10.2. В клетку В8 запишите функцию, дающую ответ на вопрос: "Какую стипендию в n -м семестре получил m -й студент?" Значения n -го семестра и фамилия m -го студента должны быть введены в клетки А8 и А9. Для решения поставленной задачи используйте функции ПРОСМОТР и ВЫБОР.

Рис.6.3

4. Научитесь пользоваться статистическими функциями
РАНГ и ПРЕДСКАЗАНИЕ.

4.1. На пятом листе книги создайте таблицу, приведенную на рис.6.4.

4.2. Используя функцию РАНГ, определите ранги цехов в зависимости от объема продаж по каждому году и поместите результаты в соответствующие клетки таблицы. В ячейки J3:J7 запишите формулы для вычисления средних значений рангов цехов.

4.3. Пользуясь информацией об объемах продаж, спрогнозируйте объемы продаж для каждого цеха в 1999 году, пользуясь функцией ПРЕДСКАЗАНИЕ.

Рис.6.4

5. Научитесь использовать текстовые функции.

5.1. Используйте формулу

="Сегодня "&ТЕКСТ(СЕГОДНЯ();"ДДДД ДД ММММ ГГГГ \г\.")

Проанализируйте полученный результат и измените аргумент функции ТЕКСТ, применяющий формат.

5.2. Для данных таблицы, приведенной на рис.6.5, используйте функцию ТЕКСТ для получения информации, идентичной записи в ячейке В6. В ячейке В5 текст «Доход равен» и число из ячейки В3 объедините с помощью конкатенации: «Доход равен» & В3. (Обратите внимание, что число при этом не форматируется).

Рис.6.5

6. Научитесь пользоваться функциями для финансовых расчетов.

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

6. 2. Вычислите общее количество выплат по ссуде размером 70 000 руб. Ссуда взята под 6% годовых. Объем ежемесячных выплат по ссуде 1 643,95 руб. Для вычислений используйте функцию КПЕР.

6.3. Вычислите объем ссуды, которую можно получить на 4 года под 6% годовых, если объем выплат не превышает 1 643,95 руб. Для вычислений используйте функцию ПС.

6.4. Вычислите основную часть выплат по ссуде за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ОСПЛТ.

6.5. Вычислите часть выплат по ссуде, которая идет на выплату процентов за определенный период (первый, десятый, двадцатый и сорок восьмой месяцы). Ссуда 70 000 руб., взята на 4 года под 6% годовых. Для вычислений используйте функцию ПРПЛТ. Просуммируйте результаты вычислений функций ОСПЛТ и ПРПЛТ за соответствующие периоды и сделайте выводы.

7. Предъявите результаты работы преподавателю.

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

Тема: Диаграммы.

Цель: Знакомство с графическим представлением табличных данных в MS Excel.

Темы: Работа с диаграммами. Использование основных типов диаграмм. Создание и редактирование диаграмм.

1. Введите таблицу, представленную на рис.7.1, на первый и второй листы книги.

Рис.7.1

2. Научитесь создавать диаграммы на листе Диаграмма и на рабочем листе.

2.1 Выделите рабочий диапазон таблицы А4:G6, и нажмите клавишу F11 для быстрого построения гистограммы на отдельном листе.

2.2. Познакомьтесь с командами вкладки Работа с диаграммами – Конструктор - Тип и поменяйте гистограмму на нормированную гистограмму и проанализируйте полученный результат, верните прежний тип гистограммы.

2.3. Используя команду Работа с диаграммами – Конструктор – Данные – Строка/столбец, измените ориентацию рядов диаграммы, затем верните диаграмму к прежнему виду.

2.4. Познакомьтесь с экспресс - макетами диаграммы и примените один из них, для возврата используйте команду экспресс – макет 11.

2.5. Снабдите диаграмму элементами диаграммы, перечень которых можно найти на вкладке Работа с диаграммами – Макет. На диаграмме должны быть подписи данных, легенда, название диаграммы, а также названия осей и таблица значений.

2.6. Выберите маркер диаграммы из ряда Факт с наибольшим значением, увеличьте размер шрифта подписи данных этого маркера и измените его заливку. Используйте команду Формат выделенного фрагмента на вкладке Работа с диаграммами - Макет или Работа с диаграммами - Формат.

2.7. Постройте на рабочем поле первого листа аналогичную гистограмму. Обратите внимание на команду Работа с диаграммами – Конструктор – Расположение, которая позволит расположить диаграмму на отдельном листе или непосредственно в текущем.

2.8. Добавьте новую строку в исходную таблицу, в которой будет рассчитано среднее значение между плановыми и фактическими показателями, и отредактируйте гистограмму, указав новый диапазон данных (Работа с диаграммами – Конструктор – Данные – Выбрать данные). Замените тип диаграммы для ряда среднего значения на график и используйте для него вспомогательную ось. Снабдите гистограмму всеми элементами диаграммы (п.2.5) и оформите ее по своему усмотрению. Сохраните книгу.

3. Познакомьтесь с диаграммами разных типов, предоставляемых Excel и расположите их на отдельных листах. Каждый лист должен иметь имя, соответствующее типу диаграммы, расположенной на нем.

3.1.Постройте диаграмму с областями (Area).

3.2.Постройте линейчатую диаграмму (Bar).

3.3.Постройте диаграмму типа график (Line).

3.4.Постройте круговую диаграмму для фактических показателей (Pie).

3.5.Постройте кольцевую диаграмму (Doughnut).

3.6.Постройте лепестковую диаграмму - "Радар" (Radar).

3.7.Постройте точечную диаграмму (XY).

3.8.Постройте объемную круговую диаграмму плановых показателей (3-D_Pie).

3.9.Постройте объемную гистограмму (3-D_Column).

3.10.Постройте объемную диаграмму с областями (3-D_Area).

4. Научитесь редактировать диаграммы[1].

4.1. В диаграмме "График" замените тип диаграммы для данных, обозначающих "План", на круговую и назовите лист "Line_Pie".

4.2. Отредактируйте круговую диаграмму, созданную на листе "Pie", так, как показано на рис.7.2.

4.3. Отредактируйте линейные графики так, как показано на рис.7.3.

Рис.7.2 Рис.7.3

4.4. Научитесь редактировать объемные диаграммы.

4.4.1. Установите "поворот" диаграммы вокруг оси Z для просмотра:

· фронтально расположенных рядов (угол 0 ^о);

· под углом в 30 ^о;

· под углом в 180 ^о;

4.4.2. Измените перспективу, сужая и расширяя поле зрения.

4.4.3. Измените порядок рядов, представленных в диаграмме.

5. Предъявите результаты преподавателю.

Лабораторная работа № 8.

Тема: Списки.

Цель: Знакомство с методами обработки данных, организованных в списки.

Темы: Сортировка, редактирование, просмотр, поиск и извлечение данных в списках MS Excel.

1. Сортировка списка по строкам.

1.1. Введите таблицу, приведенную на рис.8.1.

Рис.8.1

1.2. Пользуясь командой Главная – Редактирование – Сортировка и фильтр - Настраиваемая сортировка, отсортируйте список:

· по полу;

· по полу и должности;

· по полу, должности и году рождения.

Проанализируйте полученные результаты.

1.3. Пользуясь сортировкой и командой Данные – Структура – Промежуточные итоги, ответьте на вопросы а) - с).

a) Сколько Ивановых работают в фирме и кто из них самый молодой?

b) Каков средний возраст мужчин и женщин, работающих в фирме?

c) Сколько в фирме менеджеров, инженеров, водителей и представителей других должностей?

2. Сортировка списка по столбцам.

2.1. Введите таблицу, представленную на рис.8.2, на новый лист книги.

2.2. Отсортируйте ее так, чтобы предметы (названия полей) располагались в алфавитном порядке. Транспонируйте таблицу с помощью команд Главная – Буфер обмена - Копировать и Главная – Буфер обмена – Вставить - Транспонировать. Добавьте в конец таблицы строку "Средний балл" и заполните ее, введя соответствующую формулу.

2.3.Выполните сортировки по столбцам.

2.3.1. Переставьте столбцы так, чтобы фамилии студентов расположились в обратном алфавитном порядке.

2.3.2. Переставьте столбцы так, чтобы в первых колонках были худшие учащиеся (с минимальным средним баллом).

2.3.3. Переставьте столбцы так, чтобы в первых колонках были учащиеся, хорошо успевающие по математике, но с провалами по физкультуре.

2.3.4. Переставьте столбцы так, чтобы фамилии студентов расположились в алфавитном порядке, снова транспонируйте таблицу и поместите ее, начиная с клетки A25.

3. Обработка списков с помощью формы.

3.1. Активизируйте лист с исходной таблицей (рис.8.1).

3.2. Активизируйте окно Форма, предварительно поместив его на панель быстрого доступа (Office – Параметры Excel – Настройка – Команда не на ленте – Добавить (в панель быстрого доступа),

просмотрите все записи в списке;

"примите" (добавить) на работу одного инженера;

"увольте" (удалить) одного водителя (используя кнопку "Критерии");

сотруднице Абрамовой поменяйте фамилию на Иванова (используя кнопку "Критерии").

Рис.8.2

4. Измените структуру таблицы П.8.1.

4.1. Введите дополнительное поле "Оклад" после поля "Пол" и заполните его осмысленными значениями.

4.2. После поля "Оклад" добавьте еще три поля: "Надбавки", "Налоги", "К выплате".

4.3. Установите надбавки в размере 1000 руб. женщинам старше 50 лет. Поиск соответствующих лиц выполняйте через форму.

4.4. В обычном режиме редактирования заполните поле "Налог" - 5% от суммы оклада и надбавки, если она не превышает порога в 10 000 тыс. руб., и 10%, если свыше. При заполнении поля используйте функцию ЕСЛИ.

4.5. Запишите формулу в поле "К выплате" ("Оклад" + "Надбавки" - "Налоги").

5. Предъявите результаты преподавателю.

Лабораторная работа № 9.

Тема: Фильтрация данных.

Цель: Освоить возможности фильтрации табличных данных.

Темы: Фильтрация данных с помощью автофильтра. Расширенная фильтрация и сложные критерии.

1. Фильтрация записей с помощью функции автофильтра.

1.1. Для выполнения задания скопируйте итоговый список сотрудников из Задания №8 в новую книгу.

1.2. Примените автофильтр, пользуясь командой Данные – Сортировка и фильтр - Фильтр, или Главная – Редактирование – Сортировка и фильтр – Фильтр, определите:

· сколько в фирме женщин и каков их средний заработок;

· троих самых великовозрастных, независимо от пола;

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

· кто из мужчин живет в центральном районе (телефон начинается от 310 до 315);

· каков суммарный заработок у менеджеров и инженеров;

· сколько в фирме работает Ивановых, и каков их суммарный оклад;

· сколько сотрудников получают больше 3 000 руб., но меньше 10 000 тыс. руб. Сколько из них женщин;

· сколько сотрудников получают больше 10 000 руб. или меньше 3000 руб., и кто из них не получает надбавки;

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

2.1. Для выполнения задания скопируйте "Ведомость оценок" (рис.8.2 из Задания № 8), расположите ее в начале чистого листа; определите область критериев справа от таблицы.

2.2. Определив область для извлечения данных под таблицей, найдите студентов, имеющих:

· тройки по математике; l тройки по математике, но четверки по физике;

· двойки по математике, но пять по физике и средний балл больше 3,5 (предварительно добавив в таблицу столбец "Ср. балл");

· тройки по математике или тройки по физике;

· двойку по любому предмету (хотя бы одну).

2.3. Извлеките (на месте) данные о студентах, имеющих:

· средний балл меньше 4;

· средний балл больше, чем 3,5 и оценку по математике больше 3;

· средний балл не меньше, чем 4,5 или меньше 4, но по биологии - 5;

· средний балл больше, чем 3,5, но меньше 4[2].

2.4. Извлеките только фамилии студентов[3]:

· не имеющих двоек; l имеющих хотя бы одну двойку.

· не имеющих двоек и имеющих средний балл не меньше 4;

3. Предъявите результаты преподавателю.

Лабораторная работа № 10.

Тема: Связывание таблиц.

Цель: Научиться создавать связи между таблицами.

Темы: Создание и использование связей между данными. Использование «объемных формул».

1. Создайте три таблицы, содержащие сведения о ценах на программные продукты, по образцу, приведенному на рис.10.1. Для каждого месяца первого квартала на отдельном листе книги Имя_10_1 создается собственная таблица с названием "Прайс-лист (Месяц)", где месяц - Январь, Февраль, Март.

1.1. При создании таблиц организуйте связи между таблицами "Прайс-лист (Январь)" и таблицами "Прайс-лист (Февраль)" и "Прайс-лист (Март)", для чего скопируйте диапазон ячеек А3:В13 январской таблицы цен в буфер, перейдите в таблицу "Прайс-лист (Февраль)" и воспользуйтесь командами Главная – Буфер обмена – Вставить – Вставить связь (или Главная – Буфер обмена – Вставить – Специальная вставка – Вставить связь). Аналогично установите связь с таблицей "Прайс-лист(Март)".

Рис.10.1

1.2. Переменную часть таблиц (столбец "Цена") отредактируйте согласно данным, приведенным на рис.10.1. Переименуйте листы, дав им соответствующие имена (Январь, Февраль, Март).

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

2. Создайте таблицы "Отгрузка (Январь)", "Отгрузка (Февраль)" и "Отгрузка (Март)"по образцу, приведенному на рис.10.2, пользуясь режимом группового заполнения, и дайте листам книги названия: Отгр_ЯНВ, Отгр_ФЕВ, Отгр_МАР.

2.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Январь)". Эта формула приведена в строке формул, показанной на рис.10.2 в верхней части.

2.2. Скопируйте формулу в ячейки D5:D13.

2.3. Запишите в ячейку D14 формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).

2.4. Активизируйте команду Формулы – Зависимости формул – Влияющие ячейки для ячейки D14.

2.5. Установите курсор в ячейку D4 и отобразите влияющие ячейки. Пронаблюдайте, как отображается зависимость от внешней таблицы "Прайс_лист (Январь)", связанной с таблицей "Отгрузка(Январь)". Обратите внимание, как в строке формул выглядит формула со ссылкой на ячейку из другой таблицы, и из каких элементов состоит эта ссылка.

2.6. Сохраните созданную книгу с шестью листами под именем Имя_10_1.

2.7. Сохраните копию книги под именем Имя_10_2.

2.8. Удалите из книги Имя_10_1 листы "Отгр_ЯНВ", "Отгр_ФЕВ" и "Отгр_МАР", сохранив в ней только прайс_листы.

Рис.10.2

3. Оставьте открытыми обе книги. Заполните таблицу "Отгрузка(Февраль)" книги Имя_10_2, пользуясь "Прайс_листом(Февраль)" книги Имя_10_1.

3.1. В ячейке D4 запишите формулу, обеспечивающую ссылку на таблицу "Прайс_лист (Февраль)". Эта формула приведена в строке формул, показанной на рис.10.3.а, в верхней части.

3.2. Скопируйте формулу в ячейки D5:D13.

4. Заполните таблицу "Отгрузка(Март)" книги Имя_10_2, пользуясь "Прайс_листом(Март)" книги Имя_10_1 аналогично п.3.1 и 3.2

4.1. Закройте книгу Имя_10_1. Просмотрите формулу в D4.Она приведена в строке формул, показанной на рис.10.3.б, в верхней части.

4.2. Запишите в ячейки D14 отгрузочных листов формулу, выполняющую суммирование по столбцу "Итого" (ячейки D4:D13).

Рис.10.3.а Рис.10.3.б

5. Создайте новую таблицу "Суммарный доход за три месяца", в которой будут сведены итоговые значения выручки за все кварталы за счет организации "трехмерной связи", т.е. связи между одинаковыми клетками однотипных таблиц. Принцип создания такой таблицы представлен на рис.10.4. В создаваемой таблице запишите две формулы для получения одного и того же значения, но в одной из них запишите формулу с непосредственным обращением к каждой таблице, а в другой - с обращением к блоку таблиц, так называемую "объемную" формулу. Примеры записи таких формул приведены на рис.10.4 непосредственно под ячейками В4, В7 и выделены курсивом.

Рис.10.4

6. Предъявите результаты преподавателю.

Лабораторная работа № 11.

Тема: Консолидация данных.

Цель: Знакомство с механизмом консолидации данных.

Темы: Консолидация «по положению». Консолидация «по категориям». Консолидация со связью.

1. Создайте три таблицы, содержащие сведения о поставляемых товарах, по образцу, приведенному на рис.11.1. Для каждого месяца первого квартала на отдельном листе книги Имя_11_1 создается собственная таблица с названием "Поставки товаров в месяце", где месяц - январь, февраль, март. При создании таблиц пользуйтесь режимом "группового заполнения листов" или копирования данных.

1.1. Переменная часть таблиц (столбцы "Объем" и "Дата") должна соответствовать данным, приведенным на рис.11.1. Переименуйте листы, дав им соответствующие имена (Янв, Фев, Мар).

Рис.11.1

2. Вставьте новый лист, дав ему имя "Конс_данные". Скопируйте в него заголовок таблицы и откорректируйте его соответствующим образом (рис.11.3). Установите курсор в первую свободную ячейку (А3).

Рис.11.2

2.1. Активизируйте диалоговое окно Консолидация с помощью команд Данные – Работа с данными - Консолидация, и, последовательно указывая в поле Ссылка необходимые адреса консолидируемых областей, сформируйте их полный список, состоящий из трех записей, как представлено на рис.11.2.

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

Рис.11.3

2.3. Выполните консолидацию. Сравните полученные результаты с приведенными на рис.11.3.

2.4. Просмотрите созданную структуру, последовательно показывая или скрывая уровни этой структуры. Откройте второй (внутренний) уровень для поставщиков из С.Петербурга и Череповца. Сравните полученный результат с представленным на рис.11.4.

Рис.11.4 Рис.11.5

2.5. Пользуясь командами Формулы – Зависимости формул – Влияющие ячейки, проследите влияющие ячейки для ячеек С7, С10, С38. Убедитесь в правильности полученных результатов.

2.6. Раскройте структуру для первых трех консолидированных данных и просмотрите формулы в столбце С (Формулы – Зависимости формул – Влияющие ячейки – Показать формулы). Верните отображение результатов вычислений.

2.7. Меняя данные в ячейках листов "Янв", "Фев", "Мар", проследите за автоматическим пересчетом общей итоговой суммы (ячейка С38) и частичных сумм в ячейках С10, С26 и т.д.

3. Сохраните созданную книгу с четырьмя листами под именем Имя_11_1.

3.1. Откройте новую книгу и создайте в ней одну таблицу, имеющую аналогичную предыдущим структуру и содержащую данные за второй квартал. Образец такой таблицы на рис.11.5. Назовите лист с таблицей "2кварт". Сохраните созданную книгу под именем Имя_11_2.

3.2. Сверните окно рабочей книги.

Рис.11.6

3.3. На новом листе книги Имя_11_1 выполните консолидацию четырех диапазонов ячеек - трех из листов "Янв", "Фев", "Мар" книги Имя_11_1, а четвертого из соответствующего диапазона книги Имя_11_2 листа "2кварт". Пользуйтесь кнопкой Обзор диалогового окна Консолидация. Обратите внимание на структуру ссылки при задании области консолидации из неактивной книги. Проверьте результат и сравните его с тем, что представлен на рис.11.6.

3.4. Закройте книгу Имя_11_2. Обратите внимание на структуру ссылки при задании области консолидации из закрытой книги.

3.5. Выполните консолидацию данных из четырех таблиц (аналогично пункту 3.3), но задав в качестве обработки данных вычисление среднего значения консолидируемых данных. Сравните полученный результат с представленным на рис.11.7.

4. Добавьте в книгу еще один лист, именовав его как "Конс_данные2", и выполните на этом листе консолидацию данных, расположенных в таблицах листов "Янв", "Фев", "Мар" и "Апр" (структура таблицы листа "Апр" приведена на рис.11.8), обратив внимание на задание консолидируемой области для листа "Апр".

Рис.11.7

4.1. Проверьте правильность структуры таблицы на листе "Конс_данные2", сравнив ее с представленной на рис.11.9.

Рис.11.8 Рис.11.9

5. Предъявите результаты преподавателю.

Лабораторная работа № 12.

Тема: Сводные таблицы.

Цель: Научиться создавать сводные таблицы.

Темы: Изменение структурной организации данных с использованием сводных таблиц. Обработка данных (фильтрация, группировка, подведение итогов) в сводных таблицах. Вычисления в сводных таблицах. Представление и анализ данных. Сводные диаграммы.

1. Создайте новую книгу. На листе этой книги создайте таблицу (рис.12.1), содержащую данные о продажах автомобилей тремя гипотетическими фирмами (Альфа, Бета и Гамма) за первое полугодие 2009 года. Назовите лист "Продажи" и сохраните книгу.

Рис.12.1

2. Выполните команду Вставка – Таблицы – Сводная таблица, предварительно установив курсор в исходную таблицу. В диалоговом окне «Создание сводной таблицы» выберите диапазон данных для анализа и местоположение сводной таблицы (на новом листе).

2.1. Для создания таблицы, приведенной на рис.12.2, заполните макет сводной таблицы в панели Список полей сводной таблицы, поместив поля таблицы в соответствующие области: поле Марка – в Названия строк, поле Фирма – в Названия столбцов, а поле Цена – в область Значения. Полученная в результате этих действий сводная таблица, а также панель Список полей сводной таблицы показаны на рис.12.2.

2.2 Сформулируйте вопрос, на который отвечает созданная сводная таблица.

2.3. Познакомьтесь с вкладкой Работа со сводными таблицами. Измените функцию суммирования по полю Цена на среднее значение, установив курсор на числовые данные таблицы и выбрав из контекстного меню команду Итоги по …или команду Работа со сводными таблицами – Параметры – Активное поле – Параметры поля – Операция. Измените название поля на Средняя цена, используя текстовое поле Пользовательское имя в том же диалоговом окне.

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

2.5. Измените форматирование числовых значений сводной таблицы, применив денежный формат (Работа со сводными таблицами – Параметры – Активное поле – Параметры поля). Примените стили к сводной таблице (Работа со сводными таблицами – Конструктор – Параметры стилей сводной таблицы).

Рис.12.2

2.6. Рассмотрите возможности отображения и сокрытия итогов вычислений (Работа со сводными таблицами – Конструктор – Макет).

2.7. Скройте и восстановите заголовки полей (Работа со сводными таблицами – Параметры – Показать или скрыть – Заголовки полей).

2.8. Постройте диаграмму, отображающую данные сводной таблицы, используя команды Работа со сводными таблицами – Параметры – Сервис – Сводная диаграмма.

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

Рис.12.3

4. Сформируйте сводную таблицу, позволяющую оценить сумму продаж и даты продаж. Таблица представлена на рис.12.4. Проанализируйте данные таблицы.

4.1. Сгруппируйте данные по месяцам, затем по кварталам, затем по месяцам и кварталам (Работа со сводными таблицами – Параметры – Группировать – Группировка по полю). Таблица представлена на рис.12.5.а. Проанализируйте данные таблицы.

Рис.12.4

4.2. Примените к датам продажи группировку по выделенному, выделив сначала группу зимнее-летних месяцев, а затем группу остальных. После выделения обратитесь к команде Работа со сводными таблицами – Параметры – Группировать – Группировка по выделенному. Таблица представлена на рис.12.5.б. Проанализируйте данные таблицы.

Конфликты в семейной жизни. Как это изменить? Редкий брак и взаимоотношения существуют без конфликтов и напряженности. Через это проходят все...

Что способствует осуществлению желаний? Стопроцентная, непоколебимая уверенность в своем...

Живите по правилу: МАЛО ЛИ ЧТО НА СВЕТЕ СУЩЕСТВУЕТ? Я неслучайно подчеркиваю, что место в голове ограничено, а информации вокруг много, и что ваше право...

ЧТО ТАКОЕ УВЕРЕННОЕ ПОВЕДЕНИЕ В МЕЖЛИЧНОСТНЫХ ОТНОШЕНИЯХ? Исторически существует три основных модели различий, существующих между...

Не нашли то, что искали? Воспользуйтесь поиском гугл на сайте: