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

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

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

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

Основное назначение 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

Дата добавления: 2014-11-08