Создание таблиц листа, сводных таблиц

И диаграмм в среде табличного процессора

MS Excel

Цель выполнения части 1 лабораторной работы

Целью выполнения части 1 лабораторной работы является приоб-

ретение студентами практических навыков использования базовых

возможностей MS Excel 2010 для решения финансово-экономи-

ческих задач.

Технология работы с табличным процессором

Формулы и функции

Каждая ячейка книги MS Excel может содержать не только текст

или число, но и формулу.

Формула — это комбинация постоянных значений (констант), адресов,

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

в ячейку и определяет правила вычисления значения в ней. Формула

должна начинаться со знака =.

Формула представляет собой основное средство анализа данных.

С помощью формул можно осуществлять самые разнообразные

вычисления.

Элементы, следующие за знаком равенства, являются операнда­

ми, разделяемыми операторами вычислений. Формула, вводимая

в ячейку таблицы, может состоять из таких элементов, как:

ƒ операторы;

ƒ ссылки на ячейки и диапазоны ячеек;

ƒ числовые или символьные константы;

ƒ функции.

В MS Excel включено четыре типа операторов: арифметические,

текстовые, операторы сравнения и операторы ссылок.

Операторы — это математические символы, указывающие операции,

выполняемые над элементами формулы.

Во многих формулах используются ссылки на одну или несколько

ячеек. В ссылке указывается адрес ячейки или диапазона ячеек.

Существует четыре типа ссылок. Различить их помогает знак $.

Относительные ссылки — при копировании формулы ссылка

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

С21. При копировании формулы по столбцам будет изменяться

имя столбцаС, а при копировании по строкам — номер строки21.

Абсолютные ссылки — при копировании формулы ссылка на ячейку

не обновляется (например,$C$21).

Абсолютная строка — при копировании формулы ссылка на ячейку

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

С$21. При копировании формулы по столбцам будет изменяться

имя столбцаС, а при копировании по строкам номер строки21

изменяться не будет.

Абсолютный столбец — при копировании формулы ссылка на ячей-

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

$С21. При копировании формулы по столбцам имя столбцаС из-

меняться не будет, а при копировании по строкам номер строки21

будет изменяться.

Константы — это значения, которые в процессе вычисления формулы

не меняются.

Символьные константы при вводе в формулу должны заключаться

в кавычки. Например,=«Российская»&«Федерация».

Функция — это заранее созданная формула, которая выполняет операции

над заданными значениями (аргументами функции).

На вкладкеФормулы в группеБиблиотека функций собраны

все функции, имеющиеся в MS Excel 2010 (рис. 1.1, 1.2).

Рис. 1.1. Библиотека функций

Рис. 1.2. Другие функции

Структура функции начинается с указания имени функции, затем

вводится открывающая скобка, указываются аргументы, отделя-

ющиеся точкой с запятой, а затем — закрывающая скобка. Список

аргументов может состоять из чисел, текста, логических величин

(ИСТИНА или ЛОЖЬ), массивов, значений ошибок (например,

#Н/Д), ссылок и формул. Эти формулы, в свою очередь, могут со-

держать другие функции.

При задании аргументов нужно помнить следующие правила:

ƒ аргументы заключаются в круглые скобки;

ƒ аргументы отделяются друг от друга точкой с запятой;

ƒ число аргументов должно быть не более 255;

ƒ в качестве аргументов могут быть использованы ссылки, числа,

текст, арифметические или логические выражения, имена диапазо-

нов ячеек, функции.

Если в качестве аргумента используется функция, то такая функ-

ция называется вложенной. В этом случае сначала выполняются

функции «внутри» конструкции, а затем внешние функции.

Формула, приведенная на рис. 1.3, будет вычисляться в следу-

ющем порядке: к данным, хранящимся в ячейкеВ4, прибавляется

константа25, затем полученный результат делится на сумму данных,

хранящихся в ячейкахD5,E5 иF5, которая вычисляется с помощью

функции СУММ.

Рис. 1.3. Структура формулы

При определении приоритета арифметических операторов MS

Excel придерживается следующих правил:

ƒкруглые скобки — операторы, заключенные в круглые скобки,

выполняются в первую очередь;

ƒзнак числа — преобразование числа из положительного в отри-

цательное предшествует любой другой операции;

ƒпроценты — следующей выполняется операция вычисления

процентов;

ƒвозведение в степень — затем выполняется эта операция;

ƒумножение и деление — затем выполняются эти операции;

ƒсложение и вычитание — выполняются последними.

„Ввод и редактирование формул

Удобно использовать ввод функции с помощьюМастера функ-

ций, выполнив командыФормулы |Библиотека функций,

кнопка

, щелкнув по кнопке

в строке формул или нажав

комбинацию клавишShift+F3 на клавиатуре.

Задание 1.1. Вычислите на листеУпражнение 1 столбецНДС

и столбецСумма.

Выполнение в MS Excel

1. Введите исходные данные так, как это показано на рис. 4.

2. Введите в ячейкуD4 формулу=B4*C4*$B$1 и выполните двой-

ной щелчок поМаркеру заполнения (расположен в правом нижнем

углу активной ячейки), формула скопируется в ячейкиD5:D6.

3. Введите в ячейкуЕ4 формулу=B4*C4+D4 и скопируйте ее

в ячейкиЕ5:Е6. Результаты вычислений представлены на рис. 1.4.

Рис. 1.4. Результаты вычислений

Чаще всего формулы не имеют пробелов. Но если формулы длин-

ные, то использование пробелов и разрывов строк упростит чтение

формулы и не повлияет на результаты вычислений. Для задания раз-

рыва строки нажмите комбинацию клавишAlt+Enter на клавиатуре.

Задание 1.2. НаЛисте 3 введите в ячейкуВ3 формулу.

Выполнение в MS Excel

1. Введите исходные данные так, как это показано на рис. 1.5.

2. Для получения результата в ячейкуВ3 введите функцию:

=ЕСЛИ(B2>100000;B2*0,05;B2*0,04), используя разрывы строк

и пробелы.

Рис. 1.5. Использование пробелов

И разрывов строк в формулах

Создание таблиц листа

Основное назначение MS Excel — обработка числовой информа-

ции, однако представление данных в форме таблиц идеально подхо-

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

с данными, организованными в таблицы, в MS Excel 2010 сменилась

терминология.

База данных листа (диапазон) — это определенным образом орга-

низованная совокупность информации, которая состоит из строки

заголовка и находящихся под ней строк, содержащих числовые или

текстовые значения.

Таблица листа — это база данных рабочего листа, преобразованная

в специальный диапазон с помощью командВставка | Таблицы |

Таблица.

„Принципы построения списков

При созданииБазы данных листа следует придерживаться сле-

дующих правил:

ƒ следует размещать диапазон на одном листе;

ƒ каждый столбец диапазона должен содержать однотипные дан-

ные;

ƒ каждый столбец обязательно должен иметь заголовок;

ƒ строка диапазона (запись) должна содержать логически свя-

занные данные;

ƒ между диапазоном и другими данными листа необходимо оста-

вить одну или более пустых строк, один или более пустых столбцов;

ƒ · в самом диапазоне не должно быть пустых строк и столбцов;

ƒ в ячейках диапазона не должно быть пробелов в начале и в конце

данных;

ƒ перед вводом подписей столбцов ячейкам должен быть присвоен

текстовый формат;

ƒ формат заголовков столбцов должен отличаться от формата

строк.

Задание 1.3. Создайте базу данных листаСотрудники.

Выполнение в MS Excel

1. Переименуйте лист вСотрудники.

2. Введите информацию (см. Приложение 2).

Задание 1.4. Преобразуйте базу данных листаСотрудники в таб-

лицу.

Выполнение в MS Excel

1. Убедитесь, что диапазон не содержит пустых строк и столбцов.

2. Сделайте активной любую ячейкуБазы данных листа.

3. Выполните командыВставка | Таблицы | Таблица (или нажмите

комбинацию клавишCtrl+T на клавиатуре) (рис. 1.6).

Рис. 1.6. Определение диапазона данных,

Переводимых в таблицу

4. В окнеСоздание таблицы исправьте, если это необходимо, адрес

диапазона, нажмитеОК.

„Сортировка таблицы

Задание 1.5. Отсортируйте столбецФамилия отА доЯ.

Выполнение в MS Excel

1. Щелкните по стрелке в заголовке столбцаФамилия.

2. В раскрывшемся меню выберите командуСортировка от А до Я

(рис. 1.7).

Рис. 1.7. Выбор режима сортировки или фильтрации

„Фильтрация таблицы. Автофильтр

Под фильтрацией таблицы понимают отображение в ней только

тех строк, которые удовлетворяют заданному критерию.

Строки, отобранные при фильтрации, можно редактировать, фор-

матировать, выводить на печать, создавать на их основе диаграммы.

Задание 1.6. Отберите сотрудников, работающих в отделе02/5.

Выполнение в MS Excel

1. Щелкните по стрелке в заголовке столбцаНомер отдела.

2. В раскрывшемся меню поставьте галочку в ячейке перед отделом

02/5 (рис. 1.8), нажмитеОК.

Рис. 1.8. Фильтрация таблицы по номеру отдела

Результаты фильтрации таблицы представлены на рис. 1.9.

Рис. 1.9. Таблица, отфильтрованная по номеру отдела 02/5

„Работа со строкой итогов

Задание 1.7. Подведите итоги по столбцуЗарплата.

Выполнение в MS Excel

1. Сделайте активной ячейку в списке.

2. На вкладкеКонструктор в группеПараметры стилей таблиц

установите флажок в полеСтрока итогов (рис. 1.10).

Рис. 1.10. Элементы вкладки Работа с таблицами | Конструктор

3. В строкеИтог столбцаФамилия щелкните по стрелке и выбе-

рите из списка командуКоличество (рис. 1.11).

Рис. 1.11. Выбор режима подведения итогов

4. Создайте на листеСправочникДолжностей диапазон, приве-

денный на рис. 1.12.

Рис. 1.12. Справочник должностей организации

Задание 1.8. Преобразуйте диапазонСправочник должностей

в таблицу и определите среднюю заработную плату

по полюОклад.

Выполнение в MS Excel

1. Сделайте активной любую ячейку диапазона.

2. Выполните командыРабота с таблицами | Конструктор | Па-

раметры стилей таблиц и установите флажок опцииСтрока итогов.

3. В полеИтог столбцаОклад щелкните по стрелке и выберите

командуСреднее (рис. 1.13).

Рис. 1.13. Среднее значение оклада

„Использование формул в таблице

Задание 1.9. Создайте таблицу, приведенную на рис. 1.14, рас-

считайте отклонение от плана.

Выполнение в MS Excel

1. Создайте листПродажи.

2. Выделите ячейкиА1:С2.

3. Выполните командыВставка | Таблицы | Таблица.

4. Введите название полей:Месяц, План иФакт.

5. Введите данные, приведенные на рис. 1.14.

6. Сделайте активной ячейкуЕ1 и в качестве заголовка введите

Разница. MS Excel автоматически расширит таблицу.

7. В ячейкуЕ2 введите знак «равно».

8. Щелкните по ячейкеС2, введите знак «минус» и щелкните

по ячейкеВ2.

9. В ячейкеЕ2 должна получиться формула=[@Факт] — [@План].

10. НажмитеEnter. MS Excel автоматически скопирует формулу

во все строки таблицы (рис. 1.15).

Рис. 1.14. Данные по продажам

Рис. 1.15. Таблица продаж со вставленным столбцом формул

„Преобразование таблицы в диапазон

Задание 1.10. Преобразуйте таблицуСотрудники в диапазон.

Выполнение в MS Excel

1. Сделайте любую ячейку таблицы активной.

2. Выполните командыРабота с таблицами | Конструктор | Сер-

вис | Преобразование в диапазон.

„Расширенный фильтр

В условия отбора расширенного фильтра может входить несколько

условий, накладываемых на один столбец, или несколько условий,

накладываемых на несколько столбцов.

Задание 1.11. В диапазонеСотрудники отберите сотрудников,

работающих в отделе 02/5, имеющих семью и выс-

шее образование.

Выполнение в MS Excel

1. Вставьте четыре дополнительные строки над диапазоном

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

2. Введите условия для расширенного фильтра (рис. 1.16).

Рис. 1.16. Информация для Расширенного фильтра

3. Сделайте активной любую ячейку диапазона.

4. Выполните командыДанные | Сортировка и фильтр | Допол-

нительно.

5. В окнеРасширенный фильтр введите информацию так, как

это показано на рис. 1.17.

Рис. 1.17. Диалоговое окно Расширенный фильтр

6. НажмитеОК (результаты работыРасширенного фильтра при-

ведены на рис. 1.18).

Рис. 1.18. Результаты работы Расширенного фильтра

7. Выполните командыДанные | Сортировка и фильтр | Очистить,

чтобы восстановить диапазон после расширенного фильтра.

„Вычисление промежуточных итогов

MS Excel позволяет подводить как общие, так и промежуточные

итоги. КомандаПромежуточные итоги доступна только для диа-

пазонов данных.

Задание 1.12. В диапазонеСотрудники определите количество

сотрудников, работающих в каждом отделе.

Выполнение в MS Excel

1. Отсортируйте диапазон по столбцуНомер отдела.

2. Выполните командыДанные | Структура | Промежуточные

итоги.

3. В диалоговом окнеПромежуточные итоги (рис. 1.19) в поле

При каждом изменении в: введите из спискаНомер отдела.

Рис. 1.19. Настройка подведения промежуточных итогов

4. В полеОперация выберите из спискаКоличество.

5. В полеДобавить итоги по: установите флажок в поле того

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

6. Включите флажкиЗаменить текущие итоги, чтобы заменить

все промежуточные итоги на вновь созданные, а такжеИтоги под

данными, чтобы вставить строки, содержащие общие и промежу-

точные итоги под детальными данными.

7. НажмитеОК. Список с итогами см. в Приложении 3.

8. Чтобы удалить итоги в диалоговом окнеПромежуточные итоги,

щелкните на кнопкеУбрать все.

„Связи баз данных листа

Задание 1.13. На основе табельного номера создайте запрос к ба-

зам данных листа о сотрудниках.

Выполнение в MS Excel

1. На листеЗапросСотрудники введите информацию, представ-

ленную на рис. 1.20.

В ячейкуА4 введите табельный номер, все остальные графы будут

заполнены автоматически.

Рис. 1.20. Шаблон запроса информации о сотрудниках

Предварительно присвойте имена диапазонам баз данных листа.

2. На листеСправочникДолжностей выделите диапазон ячеек

(рис. 1.21).

3. Выполните командыФормулы | Определенные имена | При-

своить имя.

4. В диалоговом окнеСоздание имени в полеИмя: введитеСпра-

вочникДолжностей (см. рис. 1.21), нажмитеОК.

Рис. 1.21. Присвоение имени СправочникДолжностей диапазону данных

Присвойте имя диапазону данныхСотрудники на листеСотруд-

ники (рис. 1.22).

Создайте на отдельных листахСправочникОтделов, Проек-

ты и присвойте имена диапазонов данных:СправочникОтделов

(рис. 1.23) иПроекты (рис. 1.24).

Рис. 1.22. Присвоение имени диапазону Сотрудники

Рис. 1.23. Присвоение имени диапазону СправочникОтделов

Рис. 1.24. Присвоение имени диапазону Проекты

Для связи баз данных листа используйте функциюВПР() из ка-

тегорииСсылки и массивы.

5. В ячейкуA4 листаЗапросСотрудники введите табельный номер

интересующего вас сотрудника (например, 8).

6. Сделайте ячейкуВ4 активной и выполните командыФормулы

| Библиотека функций | Вставить функцию.

7. В окнеМастер функций шаг 1 выберите категориюСсылки

и массивы, нажмитеОК.

8. В окнеАргументы функции введите информацию так, как это

показано на рис. 1.25, и нажмитеОК. В ячейке отразится только

фамилия.

Рис. 1.25. Задание аргументов функции ВПР()

Для вывода полностью фамилии, имени и отчества отредактируйте

формулу (знак& (амперсант) позволяет складывать (склеивать)

текстовые данные, между которыми в кавычках выставляется знак

пробела):

=ВПР($A$4;Сотрудники;2;0)&»«&ВПР($A$4;Сотрудники;3;0)

&»«&ВПР($A$4;Сотрудники;4;0).

9. Введите в ячейкуС4 формулу:

=ВПР(ВПР($A$4;Сотрудники;9;0);СправочникОтделов;2;0).

10. Введите в ячейкуD4 формулу:

=ВПР(ВПР($A$4;Сотрудники;10;0);СправочникДолжнос-

Тей;2;0).

11. Введите в ячейкуE4 формулу:

=ВПР(ВПР($A$4;Сотрудники;11;0);Проекты;2;0).

Результаты запроса представлены на рис. 1.26.

Рис. 1.26. Результаты запроса к базе данных по сотрудникам

Создание сводных таблиц

Задание 1.14. По данным таблицыСотрудники создайте сводную

таблицуОбщее количество сотрудников органи-

зации.

Выполнение в MS Excel

1. Сделайте активной ячейку в диапазонеСотрудники.

2. Выполните командыВставить | Таблицы | Сводная таблица |

Сводная таблица (рис. 1.27).

3. Настройте сводную таблицу, перетаскивая поля так, как это

показано на рис. 1.27. Результаты представлены на рис. 1.28.

Задание 1.15. Определите, сколько человек работает в каждом

отделе.

Выполнение в MS Excel

1. Щелкните по стрелкеНомер отдела (Все) и выберите отдел

02/5 (рис. 1.29).

2. НажмитеОК. Результаты представлены на рис. 1.30.

Рис. 1.27. Настройка

Сводной таблицы

Рис. 1.28. Общее

Количество сотрудников

организации

Рис. 1.29. Настройка сводной таблицы по отделу 02/5

Рис. 1.30. Сводная таблица по отделу 02/5

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

Основной алгоритм действий при создании диаграмм:

ƒ определите данные, по которым будет построена диаграмма;

ƒ выделите диапазон ячеек, содержащий эти данные;

ƒ щелкните по вкладкеВставка и в группеДиаграммы выберите

тип диаграммы.

Перед созданием диаграммы убедитесь, что данные на листе рас-

положены в соответствии с типом диаграммы, который планируется

использовать.

Данные должны быть упорядочены по столбцам или строкам.

Необязательно столбцы (строки) данных должны быть смежными,

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

Задание 1.16. Используя данные, приведенные на рис. 1.31, пост-

ройте гистограмму с группировкой по 2006–2007 гг.

на листе с исходными данными.

Выполнение в MS Excel

1. Введите данные, приведенные на рис. 1.31.

Рис. 1.31. Исходные данные

2. Выделите диапазон данных от ячейкиА3 до ячейкиС15.

3. На вкладкеВставка в группеДиаграммы щелкните по кнопке

Гистограммы и в выпадающем меню выберитеГистограмма с груп-

пировкой (рис. 1.32).

Рис. 1.32. Выбор вида гистограммы

На экране появится гистограмма (рис. 1.33).

Рис. 1.33. Созданная гистограмма

Задание 1.17. Добавьте название диаграммы и подписи по ее осям.

Выполнение в MS Excel

1. В группеПодписи вкладкиРабота с диаграммами | Макет

щелкните по кнопкеНазвание диаграммы.

2. Выберите вариант расположения названия диаграммы (рис. 1.34).

Рис. 1.34. Выбор расположения названия диаграммы

3. В полеНазвание диаграммы с клавиатуры введите название

диаграммы (рис. 1.35).

4. В группеПодписи вкладкиРабота с диаграммами | Макет щел-

кните по кнопкеНазвание осей и введите название осей диаграммы.

Рис. 1.35. Введение названия гистограммы и подписей по осям