MICROSOFTOFFICEEXCEL 2010

ТАБЛИЧНЫЙПРОЦЕССОР

 

Оглавление

Лабораторная работа № 1. Использование простых вычислений и абсолютной адресации 3

Основные понятия. 3

Задача. 4

Автозаполнение. 4

Добавление новых элементов в таблицу. 5

Контрольные вопросы: 7

Лабораторная работа № 2. Редактирование и создание сложных формул. 8

Мастер функций. 8

Использование функции ЕСЛИ.. 8

Использование функции СЧЕТЕСЛИ.. 10

Использование функции СУММЕСЛИ.. 11

Контрольные вопросы: 12

Лабораторная работа № 3. (ДОПОЛНИТЕЛЬНАЯ) Форматирование и защита таблицы 13

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

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

Подготовка таблицы к печати. 15

Защита таблицы.. 16

Рецензирование таблиц. 17

Использование имен. 18

Контрольные вопросы: 19

Лабораторная работа № 4. Работа с диаграммами. 20

Контрольные вопросы: 22

Лабораторная работа № 5. Изучение средств фильтрации данных. 23

Сортировка и фильтрация списков. 23

Подведение итогов. 24

Работа со списками. 24

Контрольные вопросы: 24

Лабораторная работа №6. (ДОПОЛНИТЕЛЬНАЯ) Использование текстовых функций и функций типа Дата/Время. 25

Использование текстовых функций. 25

Работа с информацией типа дата/время. 27

Контрольное задание. 30

 

Лабораторная работа № 1.
Использование простых вычислений и абсолютной адресации

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

Основные понятия

1. Вызовите MsExcel и ознакомьтесь с вкладками и разделами ленты, которые выведены на экран – рисунок 1. В Ms Excel есть возможность настройки ленты и панели быстрого доступа. Настройка ленты производится во вкладке Файл / Параметры / Настройки ленты.Настройка панели быстрого доступа возможна при помощи команды вкладка Файл / Параметры / Панель быстрого доступа.

Рисунок 1- Окна табличного процессора MicrosoftOfficeExcel 2010

2. Документ MsExcel называется рабочей книгой. Каждая создаваемая рабочая книга состоит из рабочих листов. Каждый рабочий лист представляет собой электронную таблицу.

3. Электронная таблица состоит из 1048586 строк и 16384 столбцов. На экране видна только небольшая ее часть. Столбцы обозначаются латинскими буквами, а строки нумеруются. Адрес одного столбца имеет вид А:А; L:L; AF:AF, а адрес одной строки – 1:1,3:3,10:10. На пересечении строк и столбцов находятся ячейки. Каждая ячейка имеет уникальный адрес, состоящий из обозначения столбца и номера строки. Например: Al, AA40, B4 и т.д. В ячейке могут храниться: число, текст или формула.

4. Именно благодаря тому, что в ячейке можно записывать формулы, электронные таблицы могут использоваться для выполнения расчетов. MsExcel по содержимому, которое пользователь вводит в ячейку, пытается определить, что в ней будет. Для отличия от текста формулу записывают, начиная со знака равно (=).

5. Например: =1,25*(A3+С3-B3)/Е12^2. Это означает, что нужно сложить содержимое ячеек A3 и С3, вычесть содержимое ячейки B3. Полученный результат следует умножить на коэффициент 1,25 и разделить на содержимое ячейки Е12, возведенное в квадрат. В приведенном примере использованы все арифметические операции и круглые скобки, которые меняют последовательность выполнения операций. Обращаем внимание, что целая часть от дробной отделяется не точкой, как это принято в большинстве языков программирования, а запятой.

6. Текущая ячейка обведена курсорной рамкой. Адрес текущей ячейки отображается в строке формул слева, а справа - содержимое этой ячейки.

7. В ячейку С3 введите слово Фамилия.

Задача

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

Рисунок 2 – Пример таблицы

9. Премия рассчитывается в размере 10% от зарплаты сотрудника. Рассчитайте размер премии для каждого сотрудника используя формулу. Например, в ячейке C2 запишите формулу: =В2*10%. Формулы для выполнения четырех арифметических действий удобно задавать двумя способами. 1 способ: ввести формулу полностью с клавиатуры (используется английская раскладка клавиатуры); 2 способ: ввести знак равенства,далее выбрать ячейки участвующие в формуле при помощи мышки, т.е. щелкнуть по ячейке, содержащей первый операнд (В2), нажать на клавиатуре на знак операции (*) и щелкнуть по ячейке, содержащей второй операнд или набрать его на клавиатуре (10%), затем нажать Enter. На рисунке 2 в строке формул показана формула для ячейки C2. Создайте ее первым способом.

Автозаполнение

10. Диапазон C3:C8заполните путем автозаполнения формулы из ячейки C2. 1 способ: Выделить ячейку С2, протащить маленький квадрат в правом нижнем углу ячейки C2 указателем в виде «Ë» по ячейкам С3 - С8; 2 способ: выделить блок ячеек, начиная с ячейки С2 до С8 включительно,воспользоваться сочетанием клавиш Ctrl + D (автозаполнение вниз).

11. Просмотрите формулы для всех сотрудников. Обратите внимание на автоматическое изменение адресов.

12. Содержимое ячейки D2рассчитывается по формуле =В2+C2.При ее создании воспользуйтесь вторым способом, рассмотренным в п.18.

13. Диапазон D3:D8заполняется путем автозаполнения формулы из ячейки D2.

14. Содержимое ячейки В10рассчитывается по формуле =СУММ(В2:В9).1 способ: ввести формулу самостоятельно; 2 способ: выделить диапазон ячеек В2:В10 и нажать на кнопку Автосуммированиенаходящейся во вкладке Главная/ разделе Редактирование. В ячейки С10 и D10 эта формула автозаполняется (1 способ: выделите блок C10:D10 ивоспользоваться сочетанием клавиш Ctrl + R(автозаполнениевбок);2 способ: протянуть вправо маленький черный квадратик в правом нижнем углу ячейки В10 с помощью мыши).

15. Сохраните таблицу в рабочей папке под именем Задача1.xls. Для этого выполните команду вкладка Файл /пунктСохранить.

16. Поменяйте заработную плату у ФИО1 - назначьте ему 2300 рублей.

Добавление новых элементов в таблицу

17. Рассмотрим операцию добавления строки и столбцов в таблицу. Для вставки строки надо выделитьстроку, перед которой должна быть вставлена строка, и выполнить команду вкладка Главная /разделЯчейки /кнопка . Для добавления столбца надо установить курсор в ячейку, перед которой должен быть вставлен столбец, и выполнить команду вкладка Главная /разделЯчейки /кнопка ..

18. Вставьте три строки перед «шапкой» таблицы. В нашем примере «шапка» таблицы это строка – Фамилия, Зарплата Премия, Всего.

Рисунок 3 - Пример выполнения задания

19. В ячейку А1 введите текст «Расчетная ведомость за январь», в ячейку А2 - текст «Ставка Налога», в ячейку A3 - «Удерж. ПФ», в ячейку В2 число 12%, в ячейку - С2 число 15%, в ячейку В3 - число 1%.

20. Создайте новые столбцыНалог иК выдаче.

21. Для столбца Налог задайте формулу, в которой используйте значение ячейки В2, поставив знак доллара перед обозначением столбца и перед номером строки ($В$2) как это показано на рисунке 3 (для создания формулы можно ввести знак равенства, щелкнуть по ячейке D5, ввести знак умножения, щелкнуть по ячейке В2 и нажать клавишу F4 ). Это будет абсолютная ссылка на ячейку. При автозаполнении формулы для всех сотрудников адрес этой ячейки останется без изменения.

При автозаполнении формул адреса ячеек в них изменяются, однако как в предыдущем случае иногда необходимо запретить такое изменение для этого в MsExcelимеется абсолютная адресация. Для того чтобы не допустить изменения адреса ячейки или диапазона необходимо установить знаки «$» и перед буквой столбца и перед номером ячейки, например $A$1 или $A$1:$D$1.

22. Формулы для вычисления столбцаК выдаче и строки Итого (суммирования по столбцу) создайте самостоятельно, руководствуясь смыслом задачи.

23. Удалите строку 10 из таблицы (уволен сотрудник). Для этого выделите строку и выберетеУдалить в контекстном меню строки.

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

Рисунок 4 - Вставка новых строк

25. Дополните таблицу еще тремя строками, включив их между 7 и 8 строками (приняты три новых сотрудника: фамилиями сокурсников). Заполните эти строки. Фамилии и заработную плату введите (рис.4), а расчетные формулы скопируйте путем тиражирования со строки 7.

26. Вставьте перед столбцом Налог еще два новых столбца Удерж. в ПФ и НОБ (налогооблагаемая база). Для этого выделите столбец, перед которым вставляете новый, и выполните команду вкладка Главная /разделЯчейки /кнопка или командуВставить в контекстном меню столбца.

Рисунок 5 - Итоговая таблица

27. Установите, что в пенсионный фонд удерживается 1% от начисленной заработной платы и премии. При этом в формулах ссылайтесь на ячейку ВЗ, вспомните про абсолютную адресацию.

28. Отчисления впенсионный фонд не входят в налогооблагаемую базу. Следовательно, необходимо внести изменения во все формулы, зависящие от этого. Таким образом, НОБ определяется как (Зарплата + Премия) - Удержано в ПФс сотрудника, т.е. Налог считаем не по столбцуВсего(D5), а по столбцу НОБ(F5) (рис.5).

29. Рассчитайте: К выдаче = Всего - Удерж. в ПФ – Налог.

30. Сохраните таблицу под прежним именем (Файл / Сохранить или кнопка Сохранить в Стандартной панели инструментов).

Контрольные вопросы:

1. Как Excel определяет, что вводится формула, а не текст?

2. Как ввести текст в ячейку рабочего листа?

3. Каковы способы перемещения содержимого ячеек?

4. Каковы способы копирования содержимого ячеек?

5. Каковы способы тиражирования содержимого ячеек?

6. Каковы способы редактирования содержимого ячеек?

7. Как добавить строку?

8. Как добавить столбец?

9. Каковы способы выделения несмежных ячеек, диапазонов?

10. В чем отличие относительной адресации от абсолютной?


Лабораторная работа № 2.
Редактирование и создание сложных формул.

Цели работы: редактирование формул, сортировка табличных данных, применение Мастера функций, функция ЕСЛИ.

Мастер функций

1. Откройте таблицу Задача1.xlsx.Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

2. Рассмотрим способ создания формул с помощью Мастера функций. Процессор электронных таблиц поддерживает несколько сотен функций. В предыдущей работе вы уже ознакомились с функцией СУММ. Запомнить все функции невозможно, да в этом и нет необходимости. Специальное инструментальное средство, называемое Мастером функций, позволяет в диалоговом режиме легко найти нужную функцию и сконструировать на ее основе формулу. Для создания формулы с помощью Мастера функций надо установить курсор в результирующую ячейку и выполнить команду Формулы / Библиотека формул / Вставить функцию или нажать кнопку Вставка функциислева от строки формул. В появившемся окне (рис.6) в списке сверху следует выбрать нужную категорию функций или Полный алфавитный перечень, а затем в списке снизу щелкнуть по нужной функции и проверить ее назначение в нижней части окна, после чего следует нажать кнопку ОК. В следующем окне требуется ввести адрес ячеек, по которым будет вычисляться выбранная функция. Можно ввести их вручную с клавиатуры, а можно, отодвинув окно, выделить нужные ячейки. Затем нужно нажать ОК в окне или Enter на клавиатуре.

Рисунок 6 - Окно Мастера формул

Использование функции ЕСЛИ

3. Измените алгоритм расчета подоходного налога (G5) с учетом прогрессивной шкалы налогообложения (F5). Теперь пусть налог вычисляется по двум заданным формулам в зависимости от величины налогооблагаемой базы, условие расчета звучит так: «Если налогооблагаемая база меньше или равна 30 000 рублей, то принимается ставка 12% от НОБ, в противном случае налог равен – 3 600 плюс 15% от суммы, которая превышает 30 000 руб». В таком случае расчет налога должен использовать функцию ЕСЛИ:

ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь).

Формула ЕСЛИ имеет три составные части (рис.7), которые отделены друг от друга обязательным знаком « ;» (см. на строку формул):

Рисунок 7 - Функция ЕСЛИ

I-я часть: «логическое_выражение» - это условие задачи.

значение НОБ< = 30 000 руб.

II-я часть:содержит формулу расчета налога при условии, что I-я часть имеет значение ИСТИНА,

значение НОБ* ставку налога 12%

а III-я часть содержит формулу расчета налога при условии, что I-я часть имеет значение ЛОЖЬ.

(значение НОБ – 30 000) * ставку налога 15% + 3 600

4. Установите 1-2 сотрудникам заработную плату более 30 000. Проверьте правильность расчета налога. Верните исходное значение зарплаты сотрудников.

5. В 1999 были установлены новые ставки подоходного налога, см. таблицу 1.

    Таблица 1
Размер совокупного облагаемого дохода Ставка налога в федеральный бюджет Ставка налога в бюджеты субъектов РФ
до 30 000 3% 9%
от 30 001 до 60 000 3% 2700+ 12% с суммы > 30 000
от 60 001 до 90 000 3% 6300 + 17% с суммы > 60 000
от 90 001 до 150 000 3% 11400 + 22% с суммы > 90 000
от 15 001 до 300 000 3% 24600 + 32% с суммы > 150 000
от 300 001 и выше 3% 72600 + 42% с суммы >300 000

Измените формулу расчета налога в основной таблице. Используйте функцию ЕСЛИ несколько раз, вкладывая их друг в друга. В синтаксисе (правило написания формулы) функции ЕСЛИ есть уточнение «До 7 функций ЕСЛИ могут быть вложены друг в друга в качестве значений аргументов «значение_если_истина» и «значение_если_ложь», чтобы конструировать более сложные проверки». Если возникнут трудности, ограничьтесь 3 строчками таблицы. Налог будет высчитываться следующим образом:

ЕСЛИ значение НОБ <= 30000,

тозначение Налог = значениеНОБ* (3% + 9%),

иначе ЕСЛИ значение НОБ <= 60000,

то значение Налога = 2700 + (НОБ - 30000) * 12% + 3% * НОБ и т.д.

6. Поставьте курсор в ячейку расчета налога для первого сотрудника. Нажмите кнопку Вставка функциислева от строки формул. В диалоговом окне выберите логическую функцию ЕСЛИ. Затем в диалоговом окнеЕСЛИ в первом поле следует создать условие задачи (значение НОБ<=30000), во втором поле - следует создать формулу расчета налога, если условие верно (Налог = значение НОБ*(3%+9%)), в третьем - если условие неверно. Для этого в третьем поле поставьте курсор и нажмите кнопку Панели Форматирование, выберите функцию ЕСЛИ для проверки следующего условия значение значение НОБ <= 60000, и т.д.

7. Вставьте перед столбцом Фамилия новую графу Таб.номер и заполните (автоматически) ее значениями: 100, 101,102 и т.д. Для этого, установите табельный номер первого сотрудника равным 100. Растиражируйте ячейку А5 на весь диапазон А5:А13, щелкните на появившейся в правом нижнему углу от диапазона кнопке , и выберите пунктЗаполнить.

8. Сохраните исправленную таблицу под именем Задача2.xls (команда Файл / Сохранить как…).

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

10. Установите курсор на ячейку рядом с Минимальное, выполните команду вкладка Формулы /разделБиблиотека формул /кнопкаВставить функциюили нажмите кнопку Вставка функции, выберите категорию статистические функции, затем в списке функций найдите функцию МИН. Когда на экране будет запрос аргументов, задайте диапазон или запишите имя диапазона. В диапазон входят только значения зарплат сотрудников, строкаИтого для подсчета минимального значения не включается в диапазон.

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

12. Растиражируйте полученные формулы вправо во все остальные столбцы. В результате всех выполненных действий таблица приобретет вид, показанный на рис.8.

Рисунок 8 - Итоговая таблица

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

14. Запишите Ваше задание в рабочую папку под именем Задача2.xls.

Использование функции СЧЕТЕСЛИ

15. Создайте новую книгу под названием Задача2А.xls.

16. На листе1 в новой книге создайте таблицу по образцу:

Рисунок 9 - Пример таблицы

17. Заполните таблицу произвольно символами р, в, б, к (что соответствует отметкам о днях работы, выходных, больничных или командировках);

18. В ячейках, выделенных цветом, подсчитайте количество рабочих дней, больничных, командировочных и выходных используя функцию СЧЕТЕСЛИ;

19. Вид окна мастера функций при подсчете рабочих дней для Иванова будет следующим:

Рисунок 10 - Окно функции СЧЕТЕСЛИ

20. С помощью маркера автозаполнения скопируйте функцию на диапазон Q4:Q8

21. Аналогично рассчитайте остальные значения;

22. Лист1 переименуйте в Табель.

Использование функции СУММЕСЛИ

23. На листе 2 создавайте таблицу, примерно такого содержания:

Рисунок 11 - Таблица на листе 2

24. В столбцеЕ рассчитайте сумму заработной платы для сотрудников (кол-во смен * тариф);

25. Используя функцию СУММЕСЛИ вычислим количество смен, отработанных рабочими по разрядам. Для этого:

· Установите курсор в ячейку В16;

· Вызовите Мастер функции СУММЕСЛИ;

· Введите следующие аргументы в окне Мастера;

26. Чтобы рассчитать количество смен по другим разрядам необходимо дополнить функцию и скопировать ее маркером автозаполнения.

27. Воспользовавшись этой же функцией СУММЕСЛИ, рассчитайте общую сумму выплат по разрядам тоже с помощью автозаполнения.

28. Переименуйте Лист2 в Разряды.

Контрольные вопросы:

1. Как отсортировать таблицу целиком?

2. Сколько вложений в саму себя поддерживает функция ЕСЛИ?

3. Сущность функции СЧЕТЕСЛИ и СУММЕСЛИ.

4. Различие функций СЧЕТЕСЛИ И СУММЕСЛИ.

Лабораторная работа № 3. (ДОПОЛНИТЕЛЬНАЯ)
Форматирование и защита таблицы

Цели работы: форматирование таблицы, защита таблицы от редактирования, подготовка таблицы к печати, освоение приемов работы с поименованными ячейками, примечания в ячейках.

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

1. Откройте таблицу Задача2.xlsx и сразу сохраните ее под новым именем Задача3.xls.

2. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников.

3. Добавьте пустую строку перед строкой «шапка» таблицы. «Шапка» таблицы это строка с заголовками столбцов таблицы: Таб.номер, Фамилия, Зарплата и т.д.

4. Сделайте две строки 2:2 (так обозначается вся вторая строка в таблице) и 3:3, содержащие процент подоходного налога В2 и отчисления в пенсионный фонд В3, невидимыми. Для этого выделите их и выполните вкладкаГлавная /разделЯчейки / /Скрыть или отобразить /Скрыть строки,либо выделить столбцы/строки и нажав на выделенном диапазоне правой кнопкой мыши выбрать команду Скрыть.

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

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

7. Форматирование можно выполнять для выделенных ячеек с помощью команды Формат / Ячейки или кнопками панели инструментов Форматирование. Вид таблицы, который может получится у Вас после форматирования Вашей таблицы, показан на рис. 13. Проведите пошаговое форматирование таблицы.

8. Установите курсор в ячейку, в которой записан текст «Расчетная ведомость за январь». Выделите диапазон ячеек, начиная с этой ячейки до последнего столбца таблицы (A1:I1). Отцентрируйте заголовок в пределах этого диапазона (кнопка Объединить и поместить в центревкладка Главная/ раздел Выравнивание). Установите для заголовка жирный шрифт размером 12 пунктов, цвет – красный (используйте инструменты панели Форматирование).

9. Замените в «шапке» таблицы сокращения на полные слова.

10. Выделите диапазон А5:I5 и выведете окно Формат ячейки1 способ:воспользоваться сочетанием клавиш Ctrl+ 1.2 способ:щелкнуть на кнопке внизу любого из разделов: Шрифт, Выравнивание; Число. Выберите в окневкладкуВыравнивание / По горизонтали -По центру (аналог в разделе Выравнивание )и По вертикали -По центру(аналог в разделе Выравнивание ), установите флажок Переносить по словам(аналог в разделе Выравнивание ). Установите для «шапки» шрифт, размером 8 или 9 пунктов, цвет - красный. Измените цвет фона «шапки», например, сделайте ее светло-серой (кнопка Цвет заливкираздел Шрифт /вкладка Главная,либов окнеФормат ячейки /вкладкаЗаливка) При необходимости измените ширину некоторых столбцов, чтобы таблица "смотрелась". Для изменения ширины столбца установите курсор на границус названиями колонок (A,B,C,D и т.д.), курсор примет вид: «‘||’», и, не отпуская левую кнопку мыши, перемещайте границу столбца влево или вправо. Для автоматического выравнивания столбцов выделите необходимые столбцы и щёлкните дважды на одной из границ выделенных столбцов когда курсор примет вид «‘||’», аналогичное действие можно производить и со строками. Если Вы видите в ячейке значки , необходимо увеличить ширину столбца, так как ее недостаточно для отображения данных в ячейке.

11. Увеличьте высотуитоговой строки. Измените цвет символов итоговой строки на красный.

12. Выровняйте данные в ячейках. При выравнивании данных обычно соблюдают следующие правила:

13. столбцы, содержащие текст, выравниваются по левой границе;

14. столбцы, содержащие числа, выравниваются по правой границе;

15. текст в «шапке» документа выравнивается по центру.

16. Выравнивание можно выполнить кнопками вовкладке Главная / разделе Выравнивание или командой в окне Формат ячейки вкладка Выравнивание.

17. Установите для диапазона ячеек С6:D22 формат числа - целое (окно Формат ячейки / вкладка Ячейки / Число / Числовой - 0 разрядов, также можно воспользоваться кнопками на ленте: вкладка Главная / раздел Число,в списке выбрать «Числовой» и воспользоваться кнопкой для увеличения знаков после запятой или для уменьшения знаков после запятой), а всех остальных столбцов - числа с двумя разрядами после запятой. Для этого выделите фрагмент таблицы и выполните окноФормат ячейки /вкладкаЯчейки / Число. Для столбца.«К выдаче» примените Денежный формат. Для этого можно воспользоваться и кнопкой Денежный форматвкладка. Если при этом ширина некоторых столбцов окажется недостаточной, измените ее.

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

19. Используя кнопку Границывкладка Главная / раздел Шрифт или окно Формат ячейки / вкладка Граница, расчертите таблицу горизонтальными и вертикальными линиями. Используйте жирные, тонкие линии и двойные линии. Перед нанесением границ фрагмент таблицы нужно выделить.

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

20. Выполните условное форматирование. Необходимо обеспечить вывод суммы подоходного налога, рассчитанного по прогрессивной шкале, другим цветом, например, синим, т.е. сравнивать нужно значение налогооблагаемой базы, а окрашивать в цвет - значение налога. Для этого установите курсор на ячейку с налогом первого сотрудника (H6), выберите команду вкладка Главная/разделСтили /кнопкаУсловное форматирование /нажмитеСоздать правило /выберете пунктИспользовать формулу для определения форматируемых ячеек, сформируйте следующее условие: значение НОБ первого сотрудника > 30000. Кроме того, в условии адрес не должен быть абсолютным, т.е. не должно быть знаков $ (рис.9). Затем нажмите кнопку Формат…, в окне Формат ячеек выберите вкладку Шрифт и задайте нужный цвет и начертание символов, нажмите ОК, и еще раз ОК.

Рисунок 12 - Окно условного форматирования

21. Установите заработную плату первого сотрудника равной 35 000 и убедитесь, что значение налога окрасилось установленным цветом.

22. Скопируйте путем протаскивания формулу для всех сотрудников. Установите еще нескольким сотрудникам соответствующую заработную плату и проверьте действие условного форматирования.

Подготовка таблицы к печати

23. Подготовьте таблицу к выводу на печать. Выведите таблицу на экран в режиме предварительного просмотра, нажав на кнопку Предварительный просмотрна панели быстрого доступа или выполнив команду Файл / Печать).Измените Масштаб с помощью кнопки (по размеру страницы), она находится в правом нижнем углу. Вернитесь в обычный режим, нажав на вкладку Главная.

Рисунок 13 – Окно параметры страницы

Рисунок 14 – Установка полей

24. В внизу настройки печати выберете ссылку Параметры печати. В диалоговом окне Параметры страницы выберите вкладку Страница (рис.10): поменяйте ориентацию листа бумаги, увеличьте распечатку по отношению к оригиналу в 1,5 раза (150%). Выберите вкладку Поля (рис.11): измените отступы от границ листа, проведите центрирование таблицы. Выберите вкладку Колонтитулы (рис.12): создайте верхний колонтитул, указав в нем свою фамилию, и повторите просмотр таблицы перед выводом на печать, нажав на кнопку Предварительный просмотр. Обратите внимание на то, как повлияли ваши изменения на расположение таблицы на листе бумаги.

Рисунок 15 - Вставка колонтитулов

25. Сохраните таблицу.

Защита таблицы

26. Заключительным этапом разработки таблицы является ее защита от случайных и преднамеренных изменений. Это очень важный раздел работы, который часто недооценивается пользователями, его следует выполнить особенно внимательно. Можно защитить всю таблицу или ее часть, причем при защите таблицы может быть установлен пароль.

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

28. Защитите от редактирования все ячейки таблицы за исключением фамилий, размера заработной платы и табельного номера. Для этого предварительно установите другой, например, зеленый цвет для ячеек, которые разрешено редактировать А6:С16. Защита выполняется в два этапа: 1 этап: сброс флажка защиты с тех ячеек, которые можно редактировать. Выделите диапазон ячеек А6:С16, откройте окно Формат ячейки/вкладкаЗащита, снимете флажок и нажмите Enter или ОК (предварительно все ячейки таблицы считаются защищаемыми). 2 этап: защита таблицы: защитите таблицу с паролем (команда Сервис / Защита / Защитить лист). Задайте пароль - QSW.

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

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

31. Сохраните таблицу под прежним именем (Задача3.xls).

32. Снимите защиту с листа, выполнив команду вкладка Рецензирование/разделИзменение/кнопкаСнять защиту листа. Таблица должна быть похожа на таблицу, показанную на рисунке 13.

Рисунок 16 - таблица

33. Обратите внимание, что строки номер 2 и 3 скрыты. Для их вывода нужно выделить две строки, между которыми есть скрытые строки, и выполнить команду:вкладка Главная /разделЯчейки / /Скрыть или отобразить /Отобразить строки,либо выделить строки или нажмите на выделенном диапазоне правой кнопкой мыши и выберете команду Отобразить.

34. Изменим условие задачи. Предположим, что все сотрудники получают премию, равную 25% от заработной платы. Процент премии запишем в ячейке В4, значение - в ячейке С4. Самостоятельно исправьте прежние формулы в столбце Премия. Не забудьте об абсолютной адресации (см. практическую работу№2)! Т.е. формула расчета премии для первого сотрудника выглядит так: =C6*$C$4. И эту формулу растиражируйте для всех сотрудников.

Рецензирование таблиц

35. К ячейке, содержащей значение процента премии, сделайте примечание (рис.14): выполните команду вкладкаРецензирование /разделПримечание /кнопкаПримечание. Обратите внимание, что в правом верхнем углу ячейки появился красный треугольник, который свидетельствует о наличии примечания.

Рисунок 17 – Установка примечания

36. Создайте примечания еще к двум-трем ячейкам.

37. Научитесь корректировать примечания и удалять их. Для этого используйте правую кнопку мыши и пункты появляющегося при этом контекстного меню: Изменить примечание, Удалить примечание, Отобразить примечание, Скрыть примечание.

38. Сохранить таблицу под новым именем Задача4.xls и еще под одним имеем Задача4А.xls. Файл Задача4.xls закрываем (этот файл будет использоваться в дальнейших практических работах), а мы продолжаем работу с файлом Задача4А.xls.

Использование имен

39. Отдельным ячейкам и диапазонам ячеек можно назначить имена. Тогда формулы становятся более понятными, а значит, вероятность ошибок в них снижается. Кроме того, имена помогают строить более информативные таблицы, например, используя сценарии. Особенно удобно использовать имена для обозначения констант, например, ставок подоходного налога, нормативов отчислений в пенсионный фонд и т.п. Существует несколько способов назначения имен. Рассмотрим два из них: задание имени при помощи диспетчера имен и создании имени при помощи строки слева от строки формулы. Есть и недостаткипри работе с формулами, которые содержат имена. Такие формулы при копировании не перестраиваются.

40. Выполните присваивание имени ячейке. 1 способ: для этого выделите ячейки, содержащие заработную плату сотрудников (без названия столбца и итоговой строки). Выполните команду вкладка Формула/разделОпределенные имена/кнопкаДиспетчер имени / Создать присвойте выделенным ячейкам имя: Зарплата;область применения:Книга;диапазон оставьте неизменным.

41. Выполните аналогичные действия для других столбцов вашей таблицы. Имена диапазонам придумайте самостоятельно, пробелы в именах не используйте.

42. 2 способ (более простой): для этого нужно выделить нужный диапазон или ячейку, а затем в самой левой части строки формул, где обычно высвечиваются координаты расположения курсора, ввести имя.

43. Создайте имя ячейки. Для этого установите курсор в ячейку С2 и выделите эту ячейку и ячейку, содержащую текст Ставка Налога. Выполните команду вкладка Формула/разделОпределенные имена/кнопкаДиспетчер имени / Создать.

44. Аналогично создайте имя для процента премии и ставки удержания в пенсионный фонд.

45. Выполните подстановку имен в формулы. Для этого установите курсор на итоговую строку колонки Зарплата, содержащую формулу суммирования. Выполните команду вкладка Формула/разделОпределенные имена/выпадающий списокПрисвоить имена/выберите пунктПрисвоитьимена. Проверьте, как изменилась формула. Обратите внимание на замену диапазона ячеек на имя. Если формула не поменялась - проверьте правильность указания диапазонов для соответствующих имен.

46. Аналогичную работу выполните для столбцов, где вычисляются Премия и Налог. Обратите внимание на изменение формул.

47. Удалите любую строку из диапазона 6:6 –14:14 и проверьте, как изменились диапазоны строк в списке имен окна (команда вкладка Формула/разделОпределенные имена/кнопкаДиспетчер имени), верните строку и проанализируйте список имен (диапазоны должны поменяться автоматически).

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

49. Вставьте в конец таблицы, например, начиная с 23:23 строки, список созданных имен, выполнив команду вкладка Формула/разделОпределенные имена/выпадающий списокИспользовать в формуле/выберите пунктВставить имена.

50. Сохраните таблицу под именем Задача4А.xls.

Контрольные вопросы:

1. Как выполнить форматирование ячейки?

2. В чем заключается Условное форматирование?

3. Как создать собственный колонтитул?

4. Как защитить только отдельные ячейки?

5. Как скрыть строки, столбцы?

6. Как создать примечание?

7. Для чего используются Имена диапазонов?

8. Каковы способы создания Имен диапазонов?

 

 

Лабораторная работа № 4.
Работа с диаграммами.

Цели работы: Освоение приемов построения диаграмм.

1. Известно, что графическая информация воспринимается лучше, чем табличная, и намного лучше, чем словесная. Поэтому графика широко используется для анализа экономических данных. Процессор электронных таблиц MsExcel позволяет строить на основе табличных данных разнообразные диаграммы. Для этой цели предназначен специальный раздел Диаграммы /во вкладке Вставка (рис. 15).

Рисунок 18 – Вставка диаграмм

2. Откройте рабочую книгу Задача4.xlsх. Снимите защиту с рабочего листа, если это необходимо. Замените фамилии в задаче на свои(ю) фамилии(ю) и фамилии рядом сидящих сокурсников. Изучите приемы построения диаграмм.

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

4. Выберите вкладку Вставка/ раздел Диаграммы/ выберете - Гистограмма.

5. Перенесите Диаграмму на новый лист.

6. У Вас появится гистограмма Зарплаты по сотрудникам, Заметьте что при активации диаграммы становиться доступным три новые вкладки Конструктор, Макет, Формат (рис. 16).

Рисунок 19 – Вкладка Конструктор

Рисунок 20 - Диаграмма

7. Проведите редактирование диаграммы. Щелчок правой кнопкой мыши на любом элементе диаграммы (рис.17) активизирует контекстное меню этого элемента. Используя команды такого меню можно отформатировать элемент диаграммы.

8. Щелчком кнопки мыши выделите диаграмму для начала ее редактирования. Отредактируйте Заголовок диаграммы - установите шрифт TimesNewRoman, полужирный, размером 14 пунктов. Для этого выделите название, выберете вкладку Главная/ разделШрифти выберете необходимые параметры.

9. Аналогично отредактируйте Названияосейи Легенду. Затем отформатируйте ось X и ось Y.

10. Отформатируйте Область диаграммы. Щелкните левой кнопкой мыши в Области диаграммы, затем щелкните правой кнопкой и выберите соответствующий пункт из появившегося контекстного меню. Измените заливку диаграммы, а также в пункте Свойства выберетеНе перемещать и не изменять размеры.Проанализируйте полученный результат.

11. Добавьте Вертикальные линии сетки. Для этого выделите диаграмму и выполните команду вкладка Макет / раздел Оси / выпадающий список Сетка / Вертикальные линии / Основные линии.

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

13. Опробуйте различные типы диаграмм и их разные подвиды. Для этого выберете вкладку Конструктор / раздел Тип / кнопкаИзменить тип диаграммы. Выберете гистограмму со столбцами в виде пирамид.

14. Измените макет диаграммы с помощью команды: вкладка Конструктор / раздел Макет.Посмотрите как меняется диаграмма.

15. Постройте круговую объемную диаграмму по столбцамК выдаче и Фамилия. Отредактируйте ее.

16. Удалите из Диапазона данныхдиаграммы свою фамилию. Для этого выполните вкладка Конструктор / раздел Данные / кнопкаИзменить данные.

17. Сотрите все данные в поле Диапазон данных для диаграммы. Далее с помощью мышки выделите несмежные диапазоны (без своей фамилии) для этого необходимо зажать кнопку Ctrl, . Нажмите кнопку ОК (рисунок 18).

Рисунок 21 - Изменение данных в диаграмме

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

19. Сохраните рабочую книгу под именем Задача5.xls.

Контрольные вопросы:

1. Для чего используется диаграммы в работе пользователя?

2. Можно ли построить диаграмму при помощи Мастера Диаграмм, используя менее 4 шагов?

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

4. Как выделить диаграмму для ее редактирования?

5. Как можно изменить Тип диаграммы?

6. Каким образом можно войти в режим форматирования столбца на диаграмме?

7. Как удалить легенду диаграммы?

8. Как можно вращать и переворачивать объемное изображение?

9. Как можно изменить количество значений использованных для построения диаграммы?

10. Можно ли создать объемную круговую диаграмму с помощью Мастера Диаграмм?

11. Как “вырезать” сектор из объемной круговой диаграммы?

Лабораторная работа № 5.
Изучение средств фильтрации данных.

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

Сортировка и фильтрация списков

1. Откройте файл Сотрудники_издательства.xls.

2. Установите курсор в любой ячейке внутри списка. Выберите пункт меню Данные - Сортировка. Отсортируем список сразу по трем ключам: по отделам, внутри отдела по должностям, для одинаковых должностей сортировка продолжится по фамилиям, как показано на рисунке 19.

Рисунок 22 - Сортировка

3. Самостоятельно отсортируйте список по отделам (по возрастанию) далее по величине зарплат (по убыванию).

4. С помощью встроенного фильтра отобразите список сотрудников транспортного отдела, как показано на рисунке 20.

Рисунок 23 - Пример фильтрации

5. Можно последовательно применять сразу несколько фильтров. Выведите на экран список корректоров редакторского отдела

6. Для того чтобы отобразить список сотрудников, принятых на работу после 01.01.2000 года выполните следующее:

· Снимите предыдущие фильтры

· Раскройте список фильтрации в столбце Дата приема на работу

· Выберите пункт Условие

· Наберите условие, как показано на рисунке 21.

Рисунок 24 - Фильтрация

7. Используя логическую связкуИ отобразите список сотрудников, родившихся в период между 1980 и 1990 годами.

Подведение итогов

8. Установите курсор на любой ячейке внутри списка.

9. Подсчитаем количество сотрудников по отделам. Для этого выберите пункт Данные – Итоги.

10. В появившемся окне отметим нужные нам параметры для подсчета количества сотрудников по отделам. Пример показан на рисунке 22.

Рисунок 25 - Окно промежуточные итоги

11. Самостоятельно подсчитайте сумму заработной платы по отделам.

Работа со списками

12. Откройте файл Итоги_садовод.xls и выполните приведенное там задание.

Контрольные вопросы:

1. Как задать автофильтр?

2. Можно ли пользоваться ? (знаком вопроса) и * (звездочкой) для задания шаблона поиска записи в БД?

3. Как создать собственный фильтр для фильтрации данных?

4. Как снова отобразить все записи БД после фильтрации?

5. При каких условиях удобнее использовать Расширенный фильтр?

6. На какие три области опирается Расширенный фильтр?

7. Как задается условие ИЛИ в автофильтре, расширенном фильтре?

8. Можно ли скопировать полученное после фильтрации подмножество данных в другое место для дальнейшего анализа или вырезать и вставить отфильтрованные данные на другой рабочий лист?

Лабораторная работа №6. (ДОПОЛНИТЕЛЬНАЯ)
Использование текстовых функций и функций типа Дата/Время

Цель работы: научиться использовать текстовые функции Excel, изучить возможности функций работы с данными типа Дата/Время.

Использование текстовых функций

Система Excel 2010 содержит 24 функции в категории Текстовые.

Среди них операции объединения в строку данных разного типа (можно использовать символ & или функцию СЦЕПИТЬ), функции преобразования типа (ЗНАЧЕН, КОДСИМВ, СИМВОЛ, ТЕКСТ), поиска подстроки в строке (ПОИСК, НАЙТИ), замены части строки на другую(ЗАМЕНИТЬ, ПОДСТАВИТЬ, СЖПРОБЕЛЫ), выделения подстроки из строки (ЛЕВСИМВ, ПРАВСИМВ, ПСТР), замены регистра букв (ПРОПИСН, ПРОПНАЧ, СТРОЧН) и пр. Описания функций и их аргументов можно увидеть в диалоговом окне задания аргументов функции, подобном примеру на рисунке 26.

Если Вы знаете имя функции, можно воспользоваться всплывающим списком, который появится, если после символа равно (=) написать первые буквы имени функции (рисунок 26).

Рисунок 26 -Контекстный всплывающий список функций

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

Пример использования одной из текстовых функций приведен далее. Чтобы объединить текст из нескольких ячеек в одно значение, можно воспользоваться символом конкатенации (амперсанд &) или функцией СЦЕПИТЬ (рисунок 27).

Рисунок 27 - Использование функции ТЕКСТ

В первом случае функция в ячейке B1 будет выглядеть, как =A1 & " " & A2& " " & A3, во втором случае функция в ячейке B2: =СЦЕПИТЬ(A1; " "; A2; " "; A3).

Для преобразования данных разного типа при объединении их в текстовую строку следует пользоваться функцией ТЕКСТ(<значение>; <формат>).

 

13. Создайте новый файл и назовите его Задача6.xls

14. Введите данные, показанные на рисунке 28, на лист 1.

15. В соответствующие ячейки введите формулы, как на рисунке 28 и сохраните результат.

Рисунок 28 - Пример использования текстовых функций

Другая задача, более сложная: предположим, в столбце A написаныфамилия, имя и отчество группы людей, а в столбце B нужно получить ихфамилию и инициалы (рисунок 29).

Рисунок 29 – Вид задания 2

16. Для решения этой задачи сначала в ячейку C1 напишем формулу поиска первого пробела в тексте ячейки A1: =ПОИСК(" ";A1;1).

17. В ячейку D1 напишем формулу поиска второго пробела в строкеячейки A1: =ПОИСК(" ";A1;C1+1).

18. В ячейку B1 напишем формулу получения фамилии с инициалами:

=СЦЕПИТЬ(ПСТР(A1;1;C1); ПСТР(A1;C1+1; 1); ". ";ПСТР(A1;D1+1; 1);".").

19. В этой формуле объединяются в одну строку фрагменты:

1) с первого символа по первый пробел /ПСТР(A1;1;C1) – выделитьподстроку из A1 с позиции 1, количество символов C1/;

2) один символ после пробела;

3) точка и пробел;

4) один символ после второго пробела;

5) точка.

20. Далее копируем за один прием ячейки B1, C1 и D1 в строки 2 – 5.

21. Получаем результат, показанный на рисунке 29.

Работа с информацией типа дата/время

Ранее было показано, что некоторые операции по работе с датамиможно выполнять с использованием автозаполнения (см. рисунок 30).

Рисунок 30 – Функции Дата/Время

Количество функций для работы с информацией типа дата/время всистеме Excel более двух десятков.

Например, чтобы показать на листе Excel текущую дату, можно использовать функцию СЕГОДНЯ(), чтобы получить не только текущую дату, но и время, следует использовать функцию ТДАТА(). По умолчанию для первой функции задан формат Дата в формате операционной системы, однако, можно задать и формат с показом даты и времени (время будет равно00:00). Аналогично и для второй функции.

С использованием формата ячеек для типа дата/время можно показать даже название дня недели и месяца, как показано на рисунке 30.

Ранее было также показано, как объединить в одну строку данныетекстовые и даты/времени (см. рисунок 28), в этом случае можно использовать все форматы даты/времени, показанные на рисунке 30.

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

Рисунок 31 -Использование различных форматов и функции РАЗНДАТ при вычислении разности двух дат

На практике встречается задача представить стаж работника, как количество целых лет + месяцев + дней, эту задачу можно решить в системеExcel с использованием недокументированной функции РАЗНДАТ (сохранена для совместимости со старыми версиями), как это показано на рисунке 31.

Т. к. в справке Excel нет данных по этой функции, ниже приведеноописание ее синтаксиса:

Рисунок 32 - Синтаксис функции РАЗНДАТ

22. В файле Задача6.xlsперейдите на Лист2.

23. С помощью функции РАЗНДАТ() посчитайте сколько лет, месяцев и дней прошло с даты начала Великой отечественной войны, окончания ВОВ, с вашей даты рождения и еще 3 даты значимых событий (выберите их самостоятельно).

24. Сохраните файл.

 

В системе Excel присутствует 4 функции для определения количества рабочих дней в интервале и, наоборот, даты отстоящей вперед или назад на заданное количество рабочих дней: ЧИСТРАБДНИ, ЧИСТРАБДНИ.МЕЖД и РАБДЕНЬ, РАБДЕНЬ.МЕЖД.

Для примера приведем синтаксис 2-й функции (в описании синтаксиса в квадратных скобках – необязательные аргументы):

ЧИСТРАБДНИ.МЕЖД(нач_дата, кон_дата, [выходной], [праздники])

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

Рисунок 33 - Использование функции ЧИСТРАБДНИ.МЕЖД

Таблица 1

Значения аргумента выходной для функций ЧИСТРАБДНИ.МЕЖД и РАБДЕНЬ.МЕЖД

Контрольное задание

а) Выполнить вариант задания в таблице, соответствующий номеру Вашего компьютера, с использованием функций для работы с текстовой информацией:

б) Выполнить вариант задания в таблице, соответствующий номеру Вашего компьютера, с использованием функций для работы с информацией типа дата/время: