Практическое занятие № 2
Использование статистических, математических и текстовых функций электронных таблиц
Цель занятия:
Приобретение практических навыков работы с табличным процессором
Microsoft Excel
Задание 1. Настройка экрана и инструментария MS Excel
1. На Рабочем столе создайте папку PRIVATE для записи в нее всех создаваемых документов.
2. Загрузите табличный процессор Excel.
3. Поэкспериментируйте, изменяя вид и состав экрана, убрав линейки, линии прокрутки, статусную строку (Сервис — Параметры – вкладка Вид)
4. Переместите панели, используя мышку и свободное пространство панели. Попробуйте включить и отключить флажки Крупные значки, Всплывающие подсказки. Настройте состав и вид пиктографического меню по вашему усмотрению. Восстановите первоначальный вид экрана, оставив только панели Стандартная, Форматирования и Мастера подсказок.
5. Проделайте еще раз эксперимент с изменением состава Панелей инструментов с помощью контекстного меню (щелчок правой кнопкой на любой пиктограмме Панели инструментов). Восстановите первоначальный вид экрана.
6. Увеличьте максимально размер рабочего поля (самой таблицы), убрав с экрана все панели инструментов, статистическую строку, линии прокрутки, заголовок окна Excel, Панели задач и т. п. Обратите внимание на команду Вид — Во весь экран. Что она делает? Как вернуться к первоначальному виду экрана?
7. Если любую панель перетянуть за свободное место в центр окна, то она станет плавающей. Попробуйте сделать плавающей панель Форматирование. Для возврата в исходное положение - двойной щелчок по заголовку или по промежутку между пиктограммами на плавающей панели.
8. Добавьте в панель инструментов следующие кнопки:
- автоматическое суммирование значений (Вставка),
- назначение цвета шрифта выделенному тексту (Формат),
- копирование и вставка формата ячеек и объектов (Правка),
- сортировка выделенных строк по возрастанию (Данные),
- закрепление и снятие закрепленных областей (Окна и справки),
- назначение цвета выделенной области (Рисование)
9. Удалите некоторые из вставленных кнопок.
10. Создайте новую кнопку (Вид — Панели инструментов — Настройка) Для этого нужно:
- выбрать Категорию, в которой может содержаться нужная кнопка, и выделить ее в списке;
- перетащить его из окна на нужную панель (оставив пока появившееся окно для назначения макроса без внимания).
11. Повторите процедуру для создания кнопки. Отредактируйте рисунок на кнопке:
- откройте окно Настройка (Вид — Панели инструментов — Настройка или контекстное меню на Панели инструментов);
- щелчок правой кнопкой на редактируемой кнопке - Изменить значок кнопки;
- отредактировать значок и нажать кнопку Закрыть. Какие еще есть возможности изменения значка кнопки?
12. Уменьшите размер текстовой кнопки «Тип шрифта»: окно Настройка - щелчок по кнопке - установка курсора на ее левую или правую границу - сжатие (или растяжка). Размер каких еще кнопок можно изменить таким образом?
13. Создайте в графическом редакторе Paint изображение для кнопки размером 1 на 1 см и скопируйте его в Буфер обмена, вставьте его на нужную кнопку панели инструментов (окно Настройка — щелчок правой кнопкой по созданной пиктограмме - Вставка значка кнопки).
Задание 2. Построение простой электронной таблицы
1. Создайте свою папку. Запустите Excel.
2. Начиная с клетки А1, создайте электронную таблицу по образцу. Сначала заполните строки 1, 2 и столбец А. Все заголовки и фамилии вводите с первой позиции клетки. Колонку «Заработная плата» заполните значениями в пределах от 5000 руб доруб.
3. При заполнении клеток СЗ, D3 используются формулы:
СЗ = ВЗ×С$2
D3 = B3×C3
4. Диапазоны клеток СЗ:С10 и D3:D910 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек и вызвать операцию Правка – Заполнить – Вниз или с использованием мыши путем «протаскивания».
5. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не изменился? Почему? Обязательно найдите ответы на эти вопросы, при необходимости обратитесь к преподавателю.
6. Клетка В11 рассчитывается по формуле =СУММ (ВЗ:В10). В клетки С11, D11 эта формула копируется. Сохраните заполненную таблицу в папке под именем ZP1 .XLS.
7. Поменяйте у нескольких сотрудников заработную плату. Для редактирования содержимого ячеек используйте клавишу F2 или дважды щелкните мышкой на соответствующей ячейке. Обратите внимание на изменение вычисляемых ячеек.
8. Установите налог 15%. Сравните полученные итоговые данные с предыдущими значениями.
9. Вставьте новые графы «Премия» и «Всего начислено» после графы «Зарплата». Самостоятельно задайте формулы для их вычисления, исходя из того, что премия составляет определенный процент от зарплаты, а «Всего начислено» - это «Зарплата» + «Премия». Отредактируйте формулу Выплатить.
10. Удалите одну строку из таблицы (сотрудник уволен). Проверьте формулы итоговой строки, обратите внимание на изменение диапазонов в формулах.
11. Дополните таблицу еще тремя строками, включив их между, например, 5 и б строками (приняты три новых сотрудника). Заполните эти строки. Фамилии и зарплату введите, формулы — скопируйте.
12. Вставьте перед колонкой «Налог» еще две колонки «Пенсионный фонд» и «Налогооблагаемая база». Установите, что в пенсионный фонд удерживается в размере 1 % от начисленной зарплаты и премии. Отчисления в пенсионный фонд не входят в налогооблагаемую базу, т. е. «Налогооблагаемая база» вычисляется как «Зарплата» + «Премия» - «Пенсионный фонд». Внесите необходимые изменения во все формулы.
13. Измените алгоритм расчета подоходного налога с учетом прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше определенной величины (например,, то принимается ставка 12%, если больше - 20%. Формула должна использовать функцию ЕСЛИ. Как изменится формула, если шкала будет задана так: до%, отдо%, больше%?
14. Проанализируйте полученные результаты.
15. Вставьте перед колонкой «Фамилия» новую графу «Табельный номер» и заполните ее значениями: 100, 101, 102 и т. д. (Правка — Заполнить — Прогрессия).
16. Под строкой «Итого» вставьте еще две строки для вычисления среднего и максимального значения начисленной и выданной зарплаты. Для этого воспользуйтесь встроенными статистическими функциями МАКС и СРЗНАЧ, укажите диапазон для работы этих функций.
17. Задайте следующие имена для диапазонов ячеек (Вставка — Имя — Присвоить).
Зарплата - для столбца с начисленными заработными платами;
Премия — для столбца с премиями;
Налог - для столбца с налогами;
Пенсионный фонд - для столбца с отчислениями в пенсионный фонд;
18. Выделите всю таблицу и выполните команду Вставка — Имя — Применить. Укажите все имена из списка. Проверьте изменения в формулах.
19. Вместо адресов ячеек в формулах должны появиться имена диапазонов, например формулы в столбце Всего Начислено должны иметь вид - Зарплата - Премия.
20. Сохраните таблицу в своей папке под именем ZP1 .XLS.
Задание 3. Форматирование таблицы
1. Откройте таблицу ZP1 .XLS, созданную в предыдущей работе.
2. Установите с помощью мыши ширину колонок с учетом возможных их значений и ширины заголовков. Например, установите для колонки фамилий ширину 20 символов.
3. Измените высоту итоговой строки (сделайте ее «выше») и высоту строк, составляющих «шапку» (сделайте их «ниже»).
4. Отцентрируйте названия колонок и значения в строках шапки таблицы. Для этого выделите блок и щелкните на инструменте «центрировать».
5. Установите формат графы С — целое число, а всех остальных граф - дробные числа с двумя разрядами после запятой. Если при этом ширина некоторых граф окажется недостаточной - измените ее.
6. Вставьте строку с заголовком таблицы «Ведомость начисления заработной платы» самой первой, разместив ее начиная с колонки А. Проследите, что происходит при этом с формулами? Они остались правильными? Проверьте все формулы.
7. Вставьте второй строку «за январь 2008 г.»Выделите блок, состоящий из строк 1 и 2, шириной от графы А до последней графы таблицы. Отцентрируйте заголовок в пределах этого блока (использовать инструмент, на котором нанесена буква «а» со стрелками слева и справа). Установите для заголовка жирный шрифт размером 12 пунктов.
9. Выделите «шапку» таблицы. Установите для нее шрифт размером 14 пунктов. Замените в заголовке сокращения на полные слова. При необходимости измените ширину некоторых граф.
10. Для ячеек «Табельный номер», «Всего начислено», «Пенсионный фонд» задайте формат вывода текстов в несколько строк (Формат—Ячейка - вкладка Выравнивание - Переносить по строкам).
11. Запишите таблицу под новым именем ZP3.XLS.
12. Отмените сетку на экране (Сервис - Параметры - вкладка Вид и сбросьте флажок у элемента «сетка»).
13. Расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные и тонкие линии.
14. Измените цвета символов итоговой строки и заголовка. Измените цвета шапки и графы «Оплатить», например, сделайте ее светло-серой.
15. Выведите вашу таблицу на экран в режиме предварительного просмотра (инструмент, на котором изображена лупа) и измените масштаб таблицы.
16. Войдите в режим Файл — Параметры Страницы. Поменяйте некоторые параметры (на свое усмотрение) и повторите просмотр таблицы перед выводом на печать. Обратите внимание на то, как повлияли ваши изменения на расположение таблицы на листе бумаги. Сохраните таблицу на диске.
17. Попробуйте удалить одну из строк и вставить одну строку в середину таблицы. Что при этом происходит?
18. Сохраните таблицу под прежним именем (ZP3.XLS).
Задание 4. Использование статистических, математических и текстовых функций
1. Составьте таблицу следующего вида. Введите в таблицу заголовок.
2. Заполните ячейку В4. Затем протащите мышь вправо до ячейки F4 включительно. Клетки автоматически заполнятся месяцами.
3. Заполните остальные ячейки.
Задача 1. Рассчитать выручку от проката машин на 4 часа, 1 день, одну неделю.
4. Выделите ячейки B5:G5. Выполните щелчок на кнопке Сумма на панели инструментов Стандартная.
5. Скопируйте формулу из ячейки G5 в ячейки G6:G7.
6. Выделите ячейки В5:В8. Выполните щелчок на кнопке Сумма.
7. Скопируйте формулу из ячейки В8 в ячейки C8:G8. Расчет процента
8. Установите курсор на ячейку Н5. Щелкните на кнопке Процент на панели Форматирование для задания формата ячейки.
9. Наберите формулу =G5/G8 и, не нажимая клавишу ENTER, нажмите клавишу F4. Формула примет вид =G5/$G$8, т. е. относительный адрес G8 заменится на абсолютный $G$8 (не меняющийся при копировании формулы в другие ячейки). Нажмите клавишу ENTER. Появится величина в процентах.
10. Скопируйте формулу из ячейки Н5 в ячейки Н6:Н8.
11. Присвойте ячейке G8 имя Всего: установите курсор на ячейку G8, выполните команду Вставка — Имя — Присвоить, введите имя ячейки Всего, щелкните на кнопке ОК.
1 2. Очистите ячейки Н5:Н8 для расчета процентов по другой формуле.
13. Введите имя ячейки в формулу расчета процента: установите курсор на ячейку Н5 и введите формулу =G5/Bcero, скопируйте формулу в Нб:Н8.
14. Быстрый переход к ячейке по ее имени: откройте список в поле Имя в строке Формул, щелкните на имени ячейки. Для перехода к ячейке по ее имени можно также выполнить команду Правка — Перейти (или нажать клавишу F5) и выбрать имя ячейки.
Контроль взаимосвязи ячеек при расчетах
15. Выведите панель инструментов Зависимости. Для этого выполните команду Сервис — Зависимости — Панель зависимостей.
16. Установите курсор на ячейку G8. Щелкните на кнопке Влияющие ячейки. Щелкните на кнопке Зависимые ячейки.
Влияющие ячейки — это ячейки, на которые ссылается формула в текущей ячейке. Зависимые ячейки — это ячейки, содержащие формулы, которые ссылаются на текущую ячейку.
17. Удалите все стрелки, щелкнув на кнопке Убрать все стрелки на панели Зависимости.
18. Сохранить таблицу под именем ТаблицаЗ. xlc. Рабочему листу, на котором находится таблица, присвойте имя Задача 1.
19. Следующему листу присвойте имя Задача 2 (если в рабочей книге только один лист, щелкните правой кнопкой мыши по ярлычку этого листа и выполните команду Добавить — Лист).
8. Очистите ячейки Е14, D15, D16 и введите формулы функций через Мастер функций.
Функция РАНГ
Функция РАНГ определяет ранг (номер) элемента в общей совокупности.
9. В ячейку С4 введите формулу =PAHr($D4;$D$4:$D$ 12), где D4 - содержит число, для которого определяется ранг, a D4:D12 - массив чисел, среди которого определяется ранг.
10. Скопировать формулу в ячейки С5:С12. Функция ТЕНДЕНЦИЯ
11. Выполнить подготовительные операции: в ячейки В20:В22 введите соответственно значения из ячеек Е12, Е9, Еб, в ячейки С20:С24 введите годы: 2
12. В ячейку В23 введите формулу =ТЕНДЕНЦИЯ(В20:В22;С20:С22; С23). Скопируйте формулу из ячейки В23 в ячейку В24.
13. Задайте в ячейках В23:В24 формат целых чисел. Для этого следует: выделить нужные ячейки; выполнить команду Формат —Ячейки, выбрать вкладку Число, выбрать категорию Числовой.
14. Сохраните таблицу. Третьему листу рабочей книги присвойте имя Задача 3.
Задача 3. Муниципалитет города с кодом 10 ввел налог на лиц старше 18 лет в размере 10%. Определить величину налога. Составить таблицу следующей формы (на листе Задача 3):
Функция ЕСЛИ
1 5. Введите заглавие, шапку, цифровые и текстовые данные.
16. В Е4 введите формулу =ЕСЛИ (И (В4=10; С4>18); D4*0,1; 0). Формула означает, что если код города равен 10 и возраст старше 18 лет, то сумма налога определяется умножением дохода на величину налога. В противном случае сумма налога равна 0. Скопируйте формулу из ячейки Е4 в Е5:Е6.
Задание 6. Построение и редактирование диаграмм
Приведенная ниже таблица использует упрощенную модель определения основных финансовых показателей (себестоимость и прибыль) работы. Таблица заполняется следующим образом. В строку Реализовано значения вводятся в соответствии с таблицей. Строки Доход, Себестоимость и Прибыль заполняются в соответствии с нижеприведенными формулами.
Формулы для определения месячных показателей: Доход = Цена товара*Реализовано
Себестоимость = Постоянные издержки + Переменные издержки*Реализовано
Прибыль = Доход - Себестоимость
Рабочему листу с таблицей дайте имя Пример. Сохраните созданную таблицу под именем Dohod. xls. Построение диаграмм
1. Внедренная диаграмма. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы – круговая объемная. Для удобства построения установить масштаб просмотра
Решение задачи:
- выделить диапазон ячеекА7:D8 (поданным, содержащимся в этих
- ячейках, должна быть построена диаграмма); S щелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная,
- переместить курсор мыши в поле таблицы;
- укажем область таблицы для размещения диаграммы в диапазоне ячеек А13:Е23. Для этого нужно поместить курсор в ячейку А13, нажать левую клавишу мыши и, не отпуская ее, переместить курсор мыши в ячейку Е23, после чего отпустить левую клавишу;
- в проявившемся окне Мастер диаграмм - шаг 1 из 5 щелкнуть на клавише Шаг>;
- в проявившемся окне Мастер диаграмм - шаг 2 из 5 щелкнуть значок Круговая в нижнем ряду пиктограмм и на клавише Шаг>,
- в проявившемся окне Мастер диаграмм - шаг 3 из 5 щелкнуть значок с номером формата 7 и на клавише Шаг>,
- в проявившемся окне Мастер диаграмм - шаг 4 из 5 щелкнуть на клавише Шаг>;
- в проявившемся окне Мастер диаграмм - шаг 5 из 5 щелкнуть на клавише Закончить, после чего в выделенной области появится диаграмма, показанная на рисунке.
В предыдущих примерах перед вызовом Мастера Диаграмм выделялся диапазон данных с информацией, на основе которой строилась сама диаграмма. Возможна и обратная последовательность действии.
2. Построить внедренную диаграмму, отражающую объем реализации по месяцам, тип диаграммы — график. Для удобства построения установить масштаб просмотра 75%.
Решение задачи:
- Sщелкнуть на кнопке Мастер диаграмм панели инструментов Стандартная:
- укажем область таблицы для размещения диаграммы в диапазоне F2 : J18;
- в проявившемся окне Мастер диаграмм - шаг 1 из 5 необходимо задать диапазон ячеек с данными. Это можно сделать вручную, т. е. в поле Интервал набрать формулу: =$A$7:$D$8, или при помощи мыши (клавиатуры) выделить диапазон ячеек А7 : D8, после чего в поле Интервал появится приведенная выше формула. Затем надо щелкнуть на клавише Шаг>;
- в проявившемся окне Мастер диаграмм - шаг 2 из 5 щелкнуть значок Графике верхнем ряду пиктограмм и на клавише Шаг>;
- в проявившемся окне Мастер диаграмм - шаг 3 из 5 щелкнуть значок с номером формата 10 и на клавише Шаг>, S в проявившемся окне Мастер диаграмм - шаг 4 из 5 щелкнуть на клавише Шаг>,
- в проявившемся окне Мастер диаграмм - шаг 5 из 5 щелкнуть на клавише Закончить.
3. Построить диаграмму, отражающую объем реализации по месяцам на отдельном листе, тип диаграммы - гистограмма.
Решение задачи:
- выделить диапазон ячеек A7:D8;
- нажать клавишу F11. В результате диаграмма будет помещена на отдельном листе — листе диаграмм. Имя листа по умолчанию Диаграмма, каждый последующий лист диаграмм будет содержать слово Диаграмма, но номер будет увеличиваться с шагом 1. Переименуйте этот лист, дав ему имя Гистограмма
4. Построить диаграмму, отражающую объем реализации по месяцам, тип диаграммы - круговая объемная.
Решение задачи:
- выделить диапазон ячеек А7 : D8;
- выбрать пункт меню Вставка/команда Диаграмма, а в открывшемся подменю выполнить команду На новом листе;
- выполнить необходимые действия.
Переименуйте этот лист, дав ему имя Круговая. 5. Для внедренной диаграммы (объемная круговая) изменить тип на плоскую круговую. Решение задачи:
- выбрать диаграмму (т. е. щелкнуть мышкой внутри внедренной диаграммы, чтобы по ее контуру появились черные квадратики). При этом должна появиться панель инструментов Диаграмма (если она не появилась, ее нужно открыть, используя пункт меню Вид команда Панели инструментов. В открывшемся диалоговом окне щелкнуть на строке Диаграмма и на клавишу ОК);
- на панели инструментов Диаграмма открыть список кнопки Тип диаграммы
- в открывшемся списке щелкнуть на изображении круговой диаграммы.
6. Для внедренной линейчатой диаграммы изменить Автоформат. Решение задачи:
- перейти в режим редактирования внедренной диаграммы;
- выполнить команду Автоформат пункта меню Формат,
- в диалоговом окне Автоформат в разделе Форматы выбрать
- значок под номером 2;
- щелкнуть на клавише ОК или нажать клавишу ENTER.
7. Удаление внедренной диаграммы. Решение задачи:
- выбрать внедренную линейчатую диаграмму;
- нажать клавишу DEL.
РЕДАКТИРОВАНИЕ ДИАГРАММ
Редактирование диаграмм (внедренных и на отдельном листе) производится по одним правилам. Сначала надо или перейти на лист с нужной диаграммой, или перейти в режим редактирования внедренной диаграммы (при этом меняется набор пунктов меню и команды в них). Затем необходимо произвести редактирование или форматирование нужного элемента диаграммы (они называются также термином объект). Для этого можно использовать два способа:
- двойной щелчок мыши на нужном элементе (в результате открывается диалоговое окно форматирования);
- выделить нужный элемент (щелчок мыши на требуемом элементе диаграммы), выбрать пункт меню Формат, команда Выделенная.. Полное название команды зависит от имени выбранного элемента диаграммы.
8. Для диаграммы на листе Гистограмма изменить тип диаграммы на гистограмму и установить вариант Автоформата 2 (с разноцветными полосами).
9. Изменение названия диаграммы. Название диаграммы представляет собой текстовое поле. Зададим новый текст: «Объем реализации за 1 - й квартал».
10. Добавление подписей данных на диаграмму. Добавить подписи данных к столбцу диаграммы Февраль.
Решение задачи:
- щелкнуть на одном из столбцов диаграммы. При этом на каждом из столбцов появится квадратик - признак выделения всех столбцов;
- в контекстном меню - Формат рядов включить подписи данных.
11. Форматирование текста на диаграмме. Оформить текст названия диаграммы шрифтом вида Times New Roman, стиль - жирный (Bold), размер 14 пунктов. Текст обвести рамкой.
12. Построение комбинированных диаграмм. Построить смешанную внедренную диаграмму, отражающую доход и себестоимость за 1 - й квартал. Построенная смешанная диаграмма представляет собой комбинацию из диаграммы и графика.
