Тема: «Ввод и редактирование данных. Форматирование таблиц. Консолидация данных»

Цель:

• Получить навыки ввода и редактирования данных

• Изучить способы форматирования ячеек. Стили ячеек

• Изучить способы форматирование таблиц. Стили таблиц

• Изучить способы ввода формулы в ячейку

• Получить навыки работы с мастером функций

• Изучить различные способы консолидации данных с помощью окна Консолидация

Время проведения: 2 часа.

Вопросы для подготовки:

1. Ввод данных. Числовые форматы

2. Форматирование ячеек таблицы. Стиль ячеек

3. Форматирование таблицы. Стиль таблицы

4. Работа с мастером функций

5. Назначение процедуры консолидации данных. Работа с окном диалога «Консолидация

Литература:

1. Экономическая информатика/ Под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2000. – 560 с.

2. Базовый курс Excel. изучаемMsOffice. М: изд-во Современная школа, 2007

3. Сергеев Александр Петрович. Использование Microsoft Office Excel 2007, - М: Диалектика, 2007

4. Грег Харвей. MicrosoftOfficeExcel2007 для "чайников". Полный справочник, - м: диалектика, 2008

5. Пол Мак-Федрис. Формулы и функции в MicrosoftOfficeExcel2007, - М: Вильямс, 2008

6. Сингаевская Галина Ивановна. Функции в MicrosoftOfficeExcel2007, - М: Диалектика, 2008

7. Джон Уокенбах. Microsoft Office Excel 2007. Библия пользователя, – М: Диалектика, 2008

8. Глушаков С.В. Microsoft Office 2007. Лучший самоучитель. М.:АСТ: АСТ МОСКВА; Владимир: ВКТ, 2008.

1. Ввод и редактирование данных. Форматирование таблицы.

   1. Ввод и редактирование

Откройте электронную таблицу Excel. Сохраните документ Книга1 на дискете (флэш – диске) с именем А:\Excel\ Финансовый отчет за первый квартал.

Создайте Таблицу 1 на листе 1 (таблица находится на стр.8). При заполнении ячейки, ввод данных осуществляется клавишей Enterили клавишами управления курсором - переходом к следующей ячейке в нужном направлении, а также щелчком на строке формул по значку.

Редактирование (исправление) введённых данных осуществляется в режиме редактирования, который открывается после двойного щелчка мышью по ячейке или нажатием клавиши F2.

2. Форматирование таблицы

Выделите заголовок (A1:Н1), на вкладке ГЛАВНАЯ примерьте к нему различные стили заголовка– команда ГЛАВНАЯ \СТИЛИ ЯЧЕЕК.

Командой ГЛАВНАЯ/ФОРМАТ/ФОРМАТ ЯЧЕЙКИ… откройте окно диалога ФОРМАТ ЯЧЕЕК. На вкладке «Число» окна диалога ФОРМАТ ЯЧЕЙКИ для диапазонов, указанных ниже, установите следующих числовые форматы:

A1:Н1 - текстовый

A2:A35 - текстовый

B2:B35 - текстовый

C2:C35 - денежный

D2:D35 - числовой

E2:E35;F2:F35;G2:G35 - денежный

Н2:Н35 – процентный.

Используя вкладки ЗАЛИВКА, ШРИФТ и ВЫРАВНИВАНИЕ отформатируйте таблицу.

В заключении выделите таблицу для этого, сделайте активной ячейку А1, нажмите клавишу Shiftи щелкните в ячейке Н35. Примените к выделенной таблице обрамление - вкладка ГРАНИЦА.

3. Расчеты в таблице

Закрепите область заголовка для того, чтобы при перемещении по таблице он не исчезал из поля зрения. Для этого сделайте активной ячейку А2 и примените команду ВИД/ЗАКРЕПИТЬ ОБЛАСТИ.

Введите в ячейку F2 формулу «Кол-во * Цена» - С2*D2. Ссылки (С2 иD2) на ячейки в формулу вводите, щелкнув в соответствующих ячейках таблицы мышью.

Ввод формулы осуществите со строки формул, сделав предварительно активной ячейку F2 или введите формулу непосредственно в ячейку.

Ввод формулы подтвердите клавишей Enterили щелчком по знакуна строке формул.

В ячейки F3:F35 формулы введите автозаполнением, для этого: выделите ячейкуF2 и крестиком, появившемся в правом нижнем углу ячейки, укажите диапазон копирования. Обратите внимание, как изменятся ссылки в формулах в ячейках диапазонаF3:F35.

В ячейку G2 введите формулу «Доход-Издержки» - (F2-E2). ЯчейкиG3:G35 заполните автозаполнением.

В ячейки F36,F37,F38 введите следующее:

F36 – текст «Общий объем продаж»;

F37 – текст «Общий доход»;

F38 – текст «Общий объем прибыли»

В ячейки F36,F37,F38 - формулы:

G36 –Sum(D2:D35) - формат числовой.

G37 –Sum(F2:F35) – формат денежный

G38 –Sum(G2:G35) – формат денежный

При вводе функции Sumпользуйтесь мастером функций, окно которого открывается командой ФОРМУЛЫ/ВСТАВИТЬ ФУНКЦИЮ, а также со строки формул на значке функции. В окне мастера функций выберите:

категория – «математические»

функция – СУММ

и далее в качестве аргументов укажите диапазоны суммирования и щёлкните по кнопке ОК.

В ячейку H2 введите выражение для доли продаж: формулу «Кол-во/Общий объем продаж» -D2/$G$36. Ячейки Н3:Н35 заполните, используя автозаполнение. В знаменателе формулы используется абсолютная ссылка на ячейкуG36, - чтобы избежать смещения ссылки на ячейку при копировании формулы в диапазон Н3:Н35.

Готовую таблицу выделите и поместите в буфер обмена. Откройте последовательно рабочий лист 2, затем рабочий лист3 и вставьте на каждый из них содержимое буфера обмена.

С помощью контекстного меню ярлыка листа Лист 1 переименуйте на «Январь», Лист 2 – на «Февраль», Лист 3 – на «Март».

Добавьте еще один лист с помощью контекстного меню области ярлыков и переименуйте его, дав ему имя «1 квартал». Переместите лист 4 мышкой на своё место по порядку.

В таблицах на листах «Февраль» и «Март» измените значения в столбцах «Кол-во продаж» и «Издержки» на вымышленные, но разумные данные.

Сделайте активным рабочий лист Январь и поместите в буфер обмена диапазон ячеек A1:С35. Сделайте активным рабочий лист 1 квартал и вставьте содержимое буфера обмена, сделав активной ячейку А1.

2. Консолидация данных

   1. Консолидация по категориям

Таблицу на листе «1 квартал» заполните объединенными данными из таблиц с листов Январь, Февраль и Март, используя операцию консолидации данных. Для этого:

Щелкните мышью в ячейке D1, сделав ее активной. С помощью команды ДАННЫЕ/КОНСОЛИДАЦИЯ откройте окно диалога «Консолидация». Из списка функций выберите «Сумма».

В строку «Ссылка» введите диапазоны ячеек для консолидации, для этого с помощью кнопки перехода на рабочий лист перейдите на рабочий лист и откройте лист Январь. Выделите диапазон ячеекD1:D35, с помощью той же кнопки вернитесь в окно Консолидация и установите флажок в строке «Подписи верхней строки». Щелкните по кнопке «Добавить».

Не закрывая окна Консолидация, таким же образом добавьте диапазоны ячеек D1:D35 с листов Февраль и Март. В заключении щелкните по кнопке ОК. Отрегулируйте если нужно ширину столбца.

Щелкните мышью в ячейке Е1, сделав ее активной. Откройте окно диалога «Консолидация». Удалите список прежних диапазонов из списка «Список диапазонов» кнопкой «Удалить».

В строку «Ссылка» введите диапазоны ячеек для консолидации, для этого с помощью кнопки перехода на рабочий лист перейдите на рабочий лист и откройте лист Январь. Выделите диапазон ячеек Е1:Е35, с помощью той же кнопки вернитесь в окно «Консолидация» и щелкните по кнопке «Добавить».

Не закрывая окна «Консолидация» таким же образом добавьте диапазоны ячеек Е1:Е35 с листов Февраль и Март. В заключении щелкните по кнопке ОК. Отрегулируйте если нужно ширину столбца.

Таким же способом заполните диапазоны ячеек F1:F35,G1:G35, Н1:Н35, сделав активными последовательно ячейкиF1,G1, Н1. Отрегулируйте если нужно ширину столбцов.

Сделайте активной ячейку F36 и откройте окно «Консолидация» В строке «Функция» оставьте Сумма.

Удалите список прежних диапазонов из списка диапазонов. Уберите флажок в строке «Подписи верхней строки» и установите флажок в строке «Значения левого столбца».

Добавьте диапазоны консолидации, для этого с помощью кнопки перехода на рабочий лист перейдите на лист и откройте лист Январь, выделите диапазон ячеек F36:G38. С помощью той же кнопки вернитесь в окно консолидации и щелкните по кнопке Добавить.

Так же добавьте диапазоны консолидации с листов Февраль и Март. В заключении щелкните по кнопке ОК. Отрегулируйте если нужно ширину столбца.

Самостоятельная работа

На листе «1 Квартал» выделите диапазон А1:H35 и скопируйте его в буфер обмена. Создайте новый лист и вставьте на него таблицу из буфера обмена. Отрегулируйте ширину столбцов. Созданному листу присвойте имя Список.

Вопросы для самоконтроля

1. Как войти в режим редактирования?

2. Как изменить ширину столбца, высоту строки?

3. Как выделить столбец, строку, диапазон ячеек?

4. Какие существуют форматы ячеек?

5. Как изменить шрифт, цвет и способ выравнивания текста?

6. Какие существуют способы адресации ячеек?

7. Как ввести формулу в ячейку?

8. Какими способами можно копировать данные?

9. Что такое консолидация данных?

10. Какие существуют способы консолидации?

Тема: «Подведение общих и промежуточных итогов. Создание отчета на основе сводной таблицы. Защита данных»

Цель:

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

• Выполнение вычислений и построение диаграмм на основе итоговых данных.

• Создание отчета на основе сводной таблицы.

• Защита данных.

Время проведения: 2 часа.

Вопросы для подготовки:

6. Подведение итогов. Работа с окном диалога ИТОГИ.

7. Назначение процедуры структура. Создание и удаление структуры. Создание структуры при подведение итогов.

8. Построение диаграммы на основе итоговых данных. Работа с мастером диаграмм.

9. Работа с мастером сводной таблицы. Редактирование сводной таблицы.

10. Защита книги, листа, ячейки

Литература:

9. Экономическая информатика/ Под ред. П.В. Конюховского и Д.Н. Колесова. – СПб: Питер, 2000. – 560 с.

10. Базовый курс Excel. ИзучаемMsOffice. М: изд-во Современная школа, 2007

11. Сергеев Александр Петрович. Использование Microsoft Office Excel 2007, - М: Диалектика, 2007

12. Грег Харвей. MicrosoftOfficeExcel2007 для "чайников". Полный справочник, - м: диалектика, 2008

13. Пол Мак-Федрис. Формулы и функции в MicrosoftOfficeExcel2007, - М: Вильямс, 2008

14. Сингаевская Галина Ивановна. Функции в MicrosoftOfficeExcel2007, - М: Диалектика, 2008

15. Джон Уокенбах. Microsoft Office Excel 2007. Библия пользователя, – М: Диалектика, 2008

16. Глушаков С.В. Microsoft Office 2007. Лучший самоучитель. М.:АСТ: АСТ МОСКВА; Владимир: ВКТ, 2008.

1. Подведение общих и промежуточных итогов

1. Создание структуры при подведении итогов

Откройте книгу Финансовый отчёт за первый квартал , созданную в лабораторной работе №1, создайте в ней новый лист, дав ему название Итоги.

Скопируйте на лист Итоги таблицу с листа Список. Требуется подвести общие и промежуточные итоги для фирм производителей (поле Фирма) по полям «Количество проданных», «Издержки», «Доход», «Прибыль».

• Сделайте активной ячейку А1. Примените команду Сортировать по возрастанию – вкладка ГЛАВНАЯ

• Сделайте активной ячейку А36 и примените команду ДАННЫЕ / ПРОМЕЖУТОЧНЫЕ ИТОГИ

• В окне «ИТОГИ» сделайте следующие установки:

Из списка «ПРИ КАЖДОМ ИЗМЕНЕНИИ В» выберите поле «Фирма»;

В списке «ОПЕРАЦИЯ» - сумма;

В списке «ДОБАВИТЬ ИТОГИ ПО» установите флажки слева от полей «Количество продаж», «Издержки», «Доход», «Прибыль»;

Щелкните по кнопке ОК.

• Выделите цветом значения итогов в различных фирмах

• Другим цветом выделите общий итог

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

На листе Итоги символы структуры отображаются слева от номеров строк. Имеется три уровня структуры. Щелчок по номеру уровня позволяет скрыть детали уровней с большими номерами и показать детали этого уровня и всех уровней с меньшими номерами.

Для того, чтобы показать все данные в таблице, нужно щелкнуть по самому большому номеру - 3. Чтобы скрыть детальные данные всех уровней структуры, нужно щелкнуть по номеру 1. Данные уровня 1 скрытыми быть не могут.

Исследуйте возможности работы со структурой таблицы. Скройте все детальные данные и отобразите только результаты подведения итогов.