Синий] [>12000]## ###; [зеленый][>1000]## ###; [черный]0,00

Процессор электронных таблиц MS Excel

Лабораторная работа 1

ЦЕЛЬ: освоение основных режимов работы по построению электронной таблицы. Ввод чисел, текста, формул, копирование формул, редактирование и пересчет ячеек, сохранение таблицы на диске.

1. Запустите Excel. Обратите внимание на состав меню и панели инструментов.
2. Перемещайте курсор по панели инструментов и задерживайте его на некоторое время у каждого инструмента. Обратите внимание, что при этом появляется краткая подсказка о назначении инструмента.
3. Перемещаясь по меню, вызывайте контекстно зависимую помощь (F1) и научитесь ее использовать (если это вы еще не умеете делать. Приемы работы с файлами помощи во всех приложениях Windows однообразны).
4. Начиная с клетки А1, создайте электронную таблицу, показанную ниже. Сначала заполните строки 1,2 и колонку А. Все заголовки и фамилии вводите с первой позиции клетки. Колонку "Зарплата" заполните значениями в пределах от 50 000 руб до 20 млн. руб.
5. При заполнении клеток С3, D3 используются формулы:

С3 = В3 * С$2 (клетка С2 имеет смешанный адрес).

D3 =В3-C3 (здесь все адреса относительные).

1. Диапазоны клеток С4:С9 и D4:D9 заполняются путем копирования соответствующих формул. Для этого нужно выделить блок ячеек, начиная с ячейки В3 (клавиша Shift и стрелка вниз), и вызвать операцию Правка- Заполнить- Вниз или с использованием мыши путем "протаскивания". Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение некоторых адресов ячеек. Адрес какой ячейки не изменился? Почему? Обязательно найдите ответы на эти вопросы; при необходимости обратитесь к преподавателю.
2. Клетка В10 рассчитывается по формуле =СУММ(В3:В9). В клетки С10, D10 эта формула копируется (выделение блока, а затем Правка- Заполнить- Вправо или с использованием мыши путем "протаскивания").
3. Сохраните заполненную таблицу на магнитном диске под именем ZP1.XLS.
4. Поменяйте у нескольких сотрудников заработную плату. Для редактирования содержимого ячеек используйте клавишу F2 или дважды щелкните мышью на соответствующей ячейке. Обратите внимание на изменение вычисляемых ячеек.
5. Установите налог 15 %. Сравните полученные итоговые данные с предыдущими значениями.
6. Закройте таблицу без записи ее на диск.
7. Откройте созданную вами таблицу.
8. Сдайте работу преподавателю и закройте таблицу.

Вопросы к лабораторной работе 1

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

Лабораторная работа 2

ЦЕЛЬ: освоение операций открытия существующей таблицы, добавления колонок, добавления и удаления строк, редактирования формул.

1. Откройте таблицу ZP1.XLS.
2. Вставьте новые графы "Премия" и "Всего начислено", задайте формулы для их вычисления самостоятельно, отредактируйте все остальные формулы, руководствуясь смыслом задачи.

1. Удалите одну строку из таблицы (уволен сотрудник). Проверьте формулы итоговой строки, обратите внимание на изменение диапазонов в формулах.
2. Дополните таблицу еще тремя строками, включив их между, например, 5 и 6 строками (приняты три новых сотрудника). Заполните эти строки. Фамилии и заработную плату введите, а расчетные формулы скопируйте.
3. Вставьте перед колонкой "Налог" еще две колонки "Пенсионный фонд" и "Налог. база". Установите, что в пенсионный фонд удерживается в размере 1% от начисленной заработной платы и премии. Отчисления в пенсионный фонд не входят в налогооблагаемую базу. Внесите необходимые изменения во все формулы, зависящие от этого.
4. Измените алгоритм расчета подоходного налога с учетом прогрессивной шкалы налогообложения. Если налогооблагаемая база меньше 12 млн. руб., то принимается ставка 12%. Если больше 12 млн. руб.,- 20%. Формула должна использовать функцию ЕСЛИ:

=ЕСЛИ(условие;формула1;формула2)

Расчет по формуле 1 выполняется в том случае, если условие истинно, в противном случае для расчета используется формула 2.

1. Внимательно проанализируйте полученные результаты.
2. Вставьте перед колонкой фамилий новую графу "Табельный номер" и заполните (автоматически) ее значениями: 100, 101,102 и т.д.(пункт меню Правка- Заполнение-Прогрессия).
3. Сохраните исправленную таблицу на магнитном диске под именем ZP2.XLS
4. Отсортируйте таблицу в алфавитном порядке по фамилиям сотрудников (выделить фрагмент, вызвать пункт меню Данные - Сортировка). Выполните сортировку по еще нескольким полям.
5. Сохраните исправленную таблицу на магнитном диске.
6. Сдайте работу преподавателю и закройте таблицу.

Вопросы к лабораторной работе 2

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

Лабораторная работа 3

ЦЕЛЬ: форматирование таблицы.

1. Откройте таблицу ZP2.XLS.

1. Установите ширину колонок с учетом возможных их значений и ширины заголовков. Например, установите для колонки фамилий ширину 20 символов. Для изменения ширины колонки установите курсор на границу с названиями колонок (А,B,C,D и т.д.), нажмите и не отпускайте левую кнопку мыши и перемещайте появившуюся вертикальную линию влево или вправо.

1. Аналогичным способом измените высоту итоговой строки (сделайте ее "выше") и высоту строк, составляющих "шапку"(сделайте их "ниже").

1. Автоматически настройте высоту строки и ширину колонки (по самому “высокому” символу и “длинному” значению). Используйте для этого пункт меню Формат-Строка (Колонка) - Подгон высоты(ширины), предварительно выделив нужную область.

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

1. Установите формат графы С - целое число, а всех остальных граф - дробные числа с двумя разрядами после запятой (для учебных целей). Если при этом ширина некоторых граф окажется недостаточной - измените ее.

1. Вставьте строку с заголовком таблицы "Ведомость начисления заработной платы" самой первой, разместив ее, начиная с колонки А. Проследите, что происходит при этом с формулами. Они остались правильными? Проверьте все формулы.

1. Вставьте второй строку "за январь 1996 г."

1. Выделите блок, состоящий из строк 1 и 2, шириной от графы А до последней графы таблицы. Отцентрируйте заголовок в пределах этого блока (использовать инструмент, на котором нанесена буква "а" со стрелками слева и справа).

1. Установите для заголовка жирный шрифт размером 12 пунктов.

1. Выделите "шапку" таблицы. Установите для нее шрифт размером 8 или 9 пунктов. Замените в заголовке сокращения на полные слова. При необходимости измените ширину некоторых граф, чтобы таблица "смотрелась".

1. Для ячеек “Табельный номер“, “Всего начислено”, “Пенсионный фонд” задайте формат вывода текстов в несколько строк (Формат - Ячейка - вкладка Выравнивание - Переносить по строкам ).

1. Запишите таблицу под новым именем ZP3.XLS.

1. Войдите в режим Сервис- Параметры - вкладка Вид и сбросьте флажок у элемента Сетка (вертикальные и горизонтальные линии должны исчезнуть).

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

1. Измените цвет символов итоговой строки и заголовка. Измените цвет “шапки” и графы "Выдать", например сделайте ее светло-серой.

1. Придайте таблице вид, наилучший с вашей точки зрения. Посоветуйтесь по этому вопросу с другими студентами и преподавателем.

1. Сохраните таблицу на диске.

1. Выведите вашу таблицу на экран в режиме предварительного просмотра (инструмент, на котором изображена лупа) и научитесь изменять масштаб (увеличивать и уменьшать таблицу).

1. Войдите в режим Файл-Параметры страницы и изучите их. Поменяйте некоторые параметры (на свое усмотрение) и повторите просмотр таблицы перед выводом на печать. Обратите внимание на то, как повлияли ваши изменения на расположение таблицы на листе бумаги. Сохраните таблицу на диске.

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

1. Защитите от редактирования все ячейки таблицы, за исключением фамилий, размера заработной платы и процента премии. Защита выполняется в два этапа:

• снятие блокировки с тех ячеек, которые можно редактировать. Для этого нужно выполнить следующие действия: выделить блок, войти в пункт Формат - вкладка Защита, стереть "крестик" и нажать Enter или OK (предварительно все ячейки таблицы считаются заблокированными. Вы должны снять блокировку);
• установка защиты (пункт Сервис - Защитить данные). Задайте пароль - QSW.

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

1. Попробуйте удалить одну из строк и вставить одну строку в середину таблицы. Что при этом происходит?

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

1. Установите другой, например, зеленый цвет для ячеек, которые разрешено редактировать.

1. Сохраните таблицу под прежним именем (ZP3.XLS).

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 3

• Как изменить ширину графы?
• Как изменить высоту строки?
• Как можно выравнивать текст в ячейках?
• Как форматируются ячейки, содержащие числа?
• Что представляет собой формат числа "проценты"?
• Как устанавливается количество знаков в дробной части числа?
• Как устанавливается формат числа в виде "денег"?
• Как изменить размер шрифта?
• Как меняется цвет фона в ячейках?
• Как меняется цвет символов в ячейках?
• Как отключить вывод "сетки" на экран?
• Как провести горизонтальные и вертикальные линии в таблице?
• Зачем нужна защита ячеек от редактирования?
• Как защитить от редактирования ячейки?
• Как снять защиту от редактирования с ячеек?
• Что происходит, если делается попытка отредактировать защищенную ячейку?
• Что происходит, если делается попытка удалить строку, имеющую защищенные ячейки?
• Как можно разрешить редактирование содержимого некоторых ячеек защищенного рабочего листа?
• Можно ли изменять форматирование таких ячеек?
• Как записать таблицу на дискету?

Лабораторная работа 4

ЦЕЛЬ: освоение приемов работы с поименованными ячейками, установка примечаний к ячейкам.

1. Вызовите Excel.

1. Откройте рабочую книгу, созданную в лабораторной работе 3. Снимите защиту с листа (Сервис- Защита- Снять).

1. Таблица должна иметь приблизительно такой вид:

Имена диапазонов

1. Выделите ячейки, содержащие заработную плату сотрудников (без итоговой строки). Войдите в режим Вставка-Имя-Определить и присвойте выделенным ячейкам имя "Зарплата". Затем выполните аналогичные действия для всех остальных граф таблицы (имена придумайте самостоятельно, пробелы в именах не используйте).

1. Выделите две ячейки, содержащие заголовок "Налог" и его значение (ячейка ниже). Войдите в режим Вставка- Имя- Создать. Укажите, что имя создается на основе ячейки, расположенной над значением налога. Оно будет занесено в список имен. Обдумайте, когда рационально применять этот способ создания имени.

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

1. Проверьте таблицу в работе.

1. Удалите строку и проверьте, как изменились диапазоны строк в списке имен (Вставка- Имя- Определить), добавьте две новые строки и проанализируйте список имен (диапазоны должны поменяться автоматически).

1. Научитесь удалять имена из списка и изменять для них диапазоны.

1. Вставьте в конец таблицы список имен (режим Вставка -Имя - Вставить в таблицу) и изучите полученный результат.

1. Запишите таблицу под новым именем ZP4.XLS.

1. Что дает использование имен? Подумайте и запишите к себе в тетрадь ваши действия. При необходимости повторите пункты 5-12 для закрепления материала.

Примечания к ячейкам

1. К любой ячейке можно добавить комментарий, то есть пояснительный текст. Переместите курсор к ячейке, где записан процент премии. Затем войдите в режим Вставка-Примечание и запишите фразу "Всем сотрудникам выдается премия, пропорциональная заработной плате". Обратите внимание, что в правом верхнем углу ячейки появилась красная точка, которая свидетельствует о наличии примечания.

1. Самостоятельно сделайте примечания еще к 2-3 ячейкам. Обратите внимание, что из всех примечаний будет образован список.

1. Научитесь корректировать примечания и удалять их.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 4

• Что понимается под именем диапазона?
• Как присваивается имя некоторому диапазону?
• Как изменить имя?
• Как изменить диапазон ячеек, соответствующий некоторому имени?
• Что будет, если разным блокам дать одинаковые имена?
• Как удаляются имена?
• Чем отличается операция "определить" от операции "создать"?
• Как учесть имена в формулах?
• Как вставляется в таблицу список имен?

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

ЦЕЛЬ: освоение мастера функций.

1. Вызовите Excel.

1. Откройте рабочую книгу, созданную в лабораторной работе 4. Снимите защиту с листа ( пункт Сервис- Защита- Снять).

Функции

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

После этого таблица должна иметь приблизительно такой вид:

1. Установите курсор на ячейку "Минимальная зарплата", войдите в пункт Вставка- Функция, выберите категорию Статистические функции, затем в списке функций найдите функцию МИН. Когда на экране будет запрос аргументов, задайте диапазон или запишите имя диапазона (если его не помните, можете повторно вызвать пункт Вставка- Имя- Вставить для выбора имени из списка). Запишите аналогичным образом формулы для вычисления других значений.

1. Скопируйте формулы, полученные для графы "Зарплата", вправо для остальных граф и просмотрите их.

1. Проведите с таблицей несколько экспериментов. Меняйте некоторые значения заработной платы и наблюдайте за изменением статистических величин.

1. Защитите лист.

1. Запишите рабочую книгу на диск под именем ZP5.XLS.

1. Проанализируйте категории функций и их состав. Обратите внимание на богатый выбор функций. Самостоятельно опробуйте некоторые из них.

1. Закройте рабочую книгу без записи ее на диск.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 5

• В чем состоит назначение Мастера функций?
• Как вызвать Мастер функций?
• Какие категории функций доступны?
• Какая (абсолютная или относительная) адресация имен?
• Как вставить имя в функцию?

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

ЦЕЛЬ: освоение приемов работы с большой таблицей.

1. Откройте рабочую книгу, созданную в лабораторной работе 5. Снимите защиту с листа ( пункт Сервис- Защита- Снять).

1. Вставьте в таблицу 20-30 новых строк. Внесите в них информацию путем "заполнения вниз" из строк, которые уже содержат данные. На повторение имен и значений не обращайте внимания - нам нужно получить таблицу из такого количества строк, чтобы она не умещалась на экране полностью.

1. Заполните графу "Табельный номер" значениями, возрастающими по арифметической прогрессии.

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

1. Листайте таблицу вперед и обратите внимание, что "шапка" в определенный момент становится невидимой. При перемещении вправо невидимыми становятся фамилии. Очевидны неудобства при работе с такими таблицами.

1. Установите курсор на заработную плату первого сотрудника. Войдите в пункт меню Окно и выберите пункт Зафиксировать подокна. После этого на экране появятся линии, которые будут отмечать постоянно видимые области (в нашем случае это “шапка” ведомости, табельные номера и фамилии сотрудников).

1. Листайте таблицу. Обратите внимание, что перемещается только та ее часть, которая находится правее и ниже положения курсора при фиксации подокон.

1. Отмените фиксацию подокон.

1. Таблицу можно разбить на два самостоятельных окна. В каждом окне можно выводить разные участки таблицы. Разбиение может быть выполнено по вертикали или по горизонтали.

1. Вертикальное разбиение. Установите курсор на букву D (на сером фоне - название колонки) и щелкните один раз левой кнопкой мыши. Затем войдите в пункт Окно- Разбить. Обратите внимание на появление двух окон с самостоятельными линейками прокрутки. Опробуйте их в работе.

1. Горизонтальное разбиение выполняется лишь с тем отличием, что нужно выделить не колонку, по которой пойдет разбиение, а строку.

1. Снимите разбиение.

1. Защитите лист.

1. Запишите рабочую книгу на диск под именем ZP6.XLS.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 6

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

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

ЦЕЛЬ: освоение приемов работы c листами рабочей книги.

1. Вызовите Excel.

1. Откройте рабочую книгу, созданную в лабораторной работе 5. Снимите защиту с листа ( пункт Сервис- Защита- Снять ), если она установлена.

1. Рабочая книга состоит из листов. Внизу экрана видны ярлычки листов, а левее самого левого ярлыка - стрелки, при помощи которых можно перемещаться по листам, если они не видны. Научитесь перемещаться по листам рабочей книги.

1. Ярлычку можно присвоить любое имя. Для этого нужно дважды щелкнуть по нему левой кнопкой мышки, а затем записать его новое имя. Назовите ваш лист "Январь".

1. Удалите все остальные листы рабочей книги. Для этого сделайте удаляемый лист активным, войдите в пункт Правка и удалите лист.

1. В рабочую книгу можно вставлять новые листы. Вставьте новый лист "1-й квартал". Для этого воспользуйтесь пунктом Вставка.

1. Скопируйте содержимое листа "Январь" в новый лист. Для этого войдите в пункт Правка- Переместить / Скопировать, внизу открывшегося окна установите "крестик", обозначающий копирование, а не перемещение. Затем укажите лист, перед которым будет сделана вставка ("1-й квартал"), и выполните операцию.

1. Измените имя нового листа на "Февраль" и внесите соответствующую поправку в название таблицы.

1. Сделайте активным лист "Февраль". Измените значение колонок "А" и "В" - вместо табельных номеров и фамилий запишите ссылки на соответствующие ячейки листа "Январь". Формула будет выглядеть, например, следующим образом:

=Январь!b3 - для первой фамилии;

Январь!b4

..........

1. Действуя, как указано в пункте 8, на основе листа "Февраль" создайте новый лист "Март".

1. На листе "1-й квартал" создайте новую таблицу следующего вида.

1. Графы С,D,Е,F содержат суммы соответствующих (по смыслу) ячеек ведомостей начисления заработной платы за январь, февраль и март. Для получения значений из других таблиц пользуйтесь следующими формулами:

=СУММ(Январь:Март!с3)
- суммирование значений ячеек С3 трех листов.

1. Для всех граф задайте новую размерность “тыс. руб.” и установите числовой формат, округляющий полученное в ячейке значение до тысяч.

Для формирования сложных форматов в пункте меню Формат- Ячейки следует выбрать вкладку Число - Пользовательский и в поле Код набрать соответствующий шаблон. Ниже приведены некоторые шаблоны:

0 - цифровой шаблон, используется для того, чтобы слева и справа от десятичного разделителя вывести не менее определенного количества цифр;

# - цифровой шаблон, аналогичен предыдущему, но незначащие нули не выводятся;

“__” - отображение любого символа;

“пробел” - может использоваться как разделитель разрядов, если пробел введен между цифровыми шаблонами. Например, применение шаблона # ###,00 приведет к тому, что число 1234, записанное в заданную ячейку, будет выглядеть на экране в виде: 1 234,00. Кроме того,пробел может использоваться как округляющий фактор: 1 пробел в конце шаблона округляет до тысяч, 2 пробела - до миллиона и т.д.

1. Для графы “Всего начислено” установите формат вывода значений, превышающих 12 млн., - синим цветом, значений от 1 млн. до 10 млн - зеленым цветом. Измените несколько раз зарплату и убедитесь в том, что условное форматирование работает.

Для проверки положительных, отрицательных и текстовых значений используется формат

[положительные]; [отрицательные]; [нулевые];[текст]

Для форматирования в зависимости от некоторого условия используют несколько другой формат. Его поясним на примере.

синий] [>12000]## ###; [зеленый][>1000]## ###; [черный]0,00

В данном случае числовые значения, большие 12000, выводятся синим цветом, меньшие 12000, но большие 1000, - зеленым, а остальные - черным. Обратите внимание, что текст при этом выводится обычным форматом.

1. Защитите листы рабочей книги. Сохраните рабочую книгу под именем ZP7.XLS.

1. Внесите несколько поправок в ведомости начисления заработной платы. Проследите, как изменяются при этом итоговые данные.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 7

• Что понимается под рабочей книгой?
• Что понимается под листом рабочей книги?
• Сколько рабочих листов имеется в новой рабочей книге?
• Как задать количество листов в новой рабочей книге?
• Как вставить новый рабочий лист?
• Как удалить рабочий лист?
• Как меняется активный лист?
• Как можно использовать данные из одного рабочего листа на другом рабочем листе?
• Как использовать данные из разных листов в формулах?
• Как округлить содержимое ячейки до тысяч, миллионов?
• Как задать разный цвет шрифта в ячейке (в зависимости от какого-либо условия)?

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

ЦЕЛЬ: освоение приемов построения диаграмм.

1. Откройте таблицу, созданную в лабораторной работе 7. Снимите защиту с рабочего листа.
2. Вставьте новый лист для будущей диаграммы и сделайте его активным.

Создание диаграммы

1. Щелкните левой кнопкой мыши по инструменту Мастер диаграмм, затем, нажав левую кнопку мыши, выделите пространство для построения диаграммы не менее 2/3 экрана.
2. Перейдите к следующему шагу, на котором задайте данные для построения диаграммы. Вы можете ввести их в виде диапазонов, отделяя точкой с запятой диапазоны, а можете выделить в таблице. При этом, если диапазоны не являются смежными, при выделении нужно удерживать клавишу Ctr l. Выделите на листе, содержащем таблицу, два диапазона: фамилии 5-6 сотрудников и их заработную плату.
3. Перейдите к следующему шагу: выбору типа диаграммы. Для этого щелкните по кнопке со знаком “Шаг>“. Выберите тип - Гистограмма. На следующем шаге выберите формат диаграммы из числа предложенных. Затем задайте:

название диаграммы - “Январь 1996 г.”

название оси Х - “Сотрудники”

название оси Y - “Зарплата”

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

Редактирование диаграммы

1. Дважды щелкните по диаграмме для начала ее редактирования. Отредактируйте название диаграммы - установите шрифт Times New Roman Cy r, полужирный, размером 12 пунктов. Для этого выделите название, нажмите правую кнопку мыши и выберите режим Форматирование. Научитесь перемещать название диаграммы.
2. Аналогично отредактируйте названия осей Х, Y и легенду.
3. Затем форматируйте ось Х и ось Y. Опробуйте различные виды засечек на шкалах, стили, цвета и толщину осей. Проведите несколько экспериментов с расположением меток.
4. Отформатируйте область построения диаграммы, придав ей наиболее понравившиеся вам цвет и рамку.
5. Отформатируйте гистограмму. Для этой цели щелкните левой кнопкой мыши на пространстве диаграммы (но не на сетке), затем щелкните правой кнопкой и выберите соответствующий пункт из появившегося дополнительно меню. Сделайте разного цвета данные разных сотрудников. Обратите внимание, на увеличение легенды. Вставьте метки данных, округлив их до тысяч при помощи пользовательского формата. Проведите несколько экспериментов с изменением зазора между столбцами диаграммы.
6. Отформатируйте сетку. Для этого установите указатель мыши на одну из линий сетки и щелкните один раз левой, а затем один раз правой кнопками мыши. В появившемся меню выберите соответствующий пункт. Проведите несколько экспериментов со стилем, цветом и толщиной линий сетки, с минимальным и максимальным значениями по оси Y. Поменяйте место пересечения осей.
7. Вставьте вторую ось Y и установите для нее шкалу, в 5 раз меньшую шкалы на главной оси. Активизируйте инструмент Мастер диаграмм и, удерживая клавишу Ctrl, выделите значения подоходного налога и постройте второй график в виде линии. Обратите внимание на расположение графиков относительно друг друга.
8. Закончите редактирование диаграммы. Перейдите на лист, где расположена таблица и поменяйте несколько значений. Вернитесь на лист с диаграммой и изучите, как отразились на графике ваши изменения в таблице.
9. Опробуйте различные типы диаграмм и их разные подвиды. Для этой цели нужно щелкнуть левой кнопкой мыши на “столбике”, затем правой кнопкой вызвать контекстное меню и выбрать в нем нужный пункт.
10. Скопируйте созданную диаграмму на другой лист через буфер обмена.

Примерный вид диаграммы представлен ниже:

1. Задайте для листов диаграмм подходящие по смыслу названия.
2. Сохраните рабочую книгу с листами - диаграммами.
3. Сдайте работу преподавателю.

Вопросы к лабораторной работе 8

• Какое положение диаграмма может занимать в рабочей книге?
• Какие типы диаграмм доступны в Excel?
• Как задать данные для диаграмм?
• Сформулируйте, что понимается под термином “серия”. Каково максимальное количество серий на графике?
• Когда возникает необходимость в использовании двух осей У на диаграмме?
• Продемонстрируйте основные приемы форматирования диаграммы.

 

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

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

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

Можно использовать автоматическое структурирование выделенной области (Данные - Структура - Автоструктура). В этом случае MS Excel выполнит группировку без участия пользователя, что редко приводит к желаемым результатам. Поэтому рекомендуется использовать ручное структурирование.

Для ручного структурирования целесообразно создать пользовательскую панель инструментов Структурирование, поместив на нее элементы управления структурой.

1. Загрузите таблицу, созданную в предыдущих работах.

1. После колонки “Фамилия” вставьте колонку “Наименование подразделения” и заполните ее произвольными данными; например: “Бухгалтерия”, “Транспортный отдел”, “Отдел кадров” и т.п.

1. Дополните таблицу колонками “Прочие начисления”, “Прочие удержания”. Заполните их произвольными данными.

1. Дополните таблицу колонкой “Всего удержано”.

1. При необходимости исправьте формулу для “Выплатить”:

Выплатить= Всего начислено - Всего удержано.

1. Выделите всю область таблицы и выполните автоматическое структурирование (команда Данные-Структура-Автоструктура). Научитесь сворачивать и разворачивать полученную структуру до разных уровней (кнопки “+” и “-”).

1. Удалите созданную структуру (Данные-Структура-Отменить структуру).

1. Удалите строку “Итого”.

1. Отсортируйте таблицу по подразделениям, а внутри подразделений - по фамилиям.

1. Подведите промежуточные итоги по подразделениям, используя формулу суммирования. Для этого выделите всю таблицу и выполните команду Данные-Итоги. Изучите полученную структуру и формулы для подведения промежуточных итогов. Отметьте недостатки.

1. Создайте пользовательскую панель инструментов Структурирование. Для этого выполните следующие действия:

• Вызовите Вид- Панели инструментов.
• Введите новое имя панели Структурирование и нажмите кнопку Создать.
• Выберите Настройка- Категории-Утилиты.
• В разделе Кнопки выберите Символы структуры документа и перетащите на новую панель.
• Выберите категорию Данные.
• Перетащите на новую панель кнопки Разгруппировка, Группировка, Показать детали, Скрыть детали.
• Нажмите на кнопку Закрыть.

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

1. Сохраните отформатированную таблицу в своем каталоге.

1. Сдайте работу преподавателю.

Вопросы к лабораторной работе 9

• Что такое структура таблицы на рабочем листе?
• Перечислите способы создания структуры, сформулируйте достоинства и недостатки каждого.
• Как вручную можно сгруппировать столбцы (строки)?
• Как можно подвести итоги по данным в таблице?
• В чем смысл группировки (разгруппировки) данных?

 

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

ЦЕЛЬ: графическое отображение зависимостей ячеек друг от друга. Решение обратной задачи. Построение таблиц зависимости от одного и двух аргументов.

1. Откройте таблицу, созданную в лабораторной работе 7. Снимите защиту с рабочего листа.

Отображение зависимостей

1. Проверьте правильность вашей таблицы. Для этой цели удобно использовать панель инструментов Зависимости ( пункт меню Сервис- Зависимости- Панель инструментов). Для определения назначения инструмента задерживайте на нем указатель мыши.

1. Установите курсор на ячейку, содержащую процент премии и щелкните по инструменту Трассировка зависимых ячеек -появятся стрелки к ячейкам, которые зависят от процента премии. Уберите стрелки.

1. Установите курсор на итоговую сумму, выдаваемую всем сотрудникам, и щелкните по инструменту Трассировка влияющих ячеек -появятся стрелки, показывающие зависимость данной ячейки от других.

1. Самостоятельно отобразите все зависимости в вашей таблице и внимательно рассмотрите их. При необходимости внесите в таблицу исправления и сохраните исправленную таблицу.

1. Уберите все стрелки. Отключите панель инструментов Зависимости.

Решение обратной задачи

Исходные данные содержат ячейки, куда информация вводится. В рассматриваемом примере исходной информацией являются: “Зарплата сотрудников” (данные колонки С) и “Процент премии ” (условно взятый одинаковым для всех сотрудников).Результатомвычислений в электронных таблицах являются ячейки, содержащие формулы. Изменение исходных данных ведет к изменению результатов. Однако бывают случаи, когда нужно получить нужный результат в зависимости от некоторого параметра. Например, нужно определить: при каком проценте премии на руки всем сотрудникам будет выдаваться заданная сумма? Такая задача называется обратной. Здесь в качестве аргумента выступает процент премии, а в качестве функции - итоговая сумма, выдаваемая на руки. Решим поставленную задачу.

1. Определите адрес ячейки, в которой вычисляется итоговая сумма, выдаваемая всем работникам.

1. Используя режим работы Сервис-Подбор параметра, определите: при каком проценте премии общая сумма, выдаваемая работникам, будет составлять некоторую круглую величину (например, 10000000)? Проведите самостоятельно несколько подобных расчетов.