Сделай Сам Свою Работу на 5

Работа с базами данных в Microsoft Excel





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

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

Таблицы Excel

Удобным средством для организации базы данных являются таблицы Excel.

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

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



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

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

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

В таблицу легко добавляются новые строки и столбцы.

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

I способ:

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

2. На вкладке Вставка в группе Таблицы щелкнуть по кнопке Таблица. Можно воспользоваться клавишами быстрого доступа Ctrl+L или Ctrl+T.

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

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



II способ:

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

2. На вкладке Главная в группе Стили открыть список Форматировать как таблицу.

3. Выбрать нужный стиль таблицы.

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

Выделение строк и столбцов таблицы

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

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

Для выделения нескольких несмежных столбцов надо выделить один из них, а затем выделять остальные при нажатой клавише Ctrl.

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

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

Для выделения нескольких несмежных строк надо выделить одну из них, а затем выделять остальные при нажатой клавише Ctrl.

Добавление и удаление строк и столбцов в таблицах



Чтобы добавить новую строку или новый столбец в конец таблицы, можно воспользоваться одним из следующих способов:

- ввести данные в эту строку или столбец: Excel автоматически расширит таблицу;

- для добавления пустой строки в конец таблицы в последней ячейке последнего столбца нажать клавишу Tab;

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

Добавить новые строки в конец таблицы или новые столбцы слева и (или) справа от таблицы можно, выбрав на вкладке Работа с таблицами – Конструктор в группе Свойства команду Изменить размер таблицы и указав новый диапазон данных для таблицы.

Чтобы вставить новые строки в середину таблицы, надо:

1. Выделить несколько строк таблицы, над которыми надо вставить новые (количество вставленных строк будет равно количеству выделенных).

2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить строки таблицы сверху или в контекстном меню выделенных строк в списке Вставить выбрать команду Вставить строки таблицы сверху.

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

Чтобы вставить новые столбцы в середину таблицы, надо:

1. Выделить несколько столбцов таблицы, слева от которых надо вставить новые (количество вставленных столбцов будет равно количеству выделенных).

2. На вкладке Главная в группе Ячейки в списке Вставить выбрать команду Вставить столбцы таблицы слева или в контекстном меню выделенных столбцов в списке Вставить выбрать команду Вставить столбцы таблицы слева.

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

Вычисляемые столбцы

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

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

В вычисляемый столбец можно включать формулы, отличающиеся от формулы столбца, которые становятся исключениями.

Исключения вычисляемого столбца создаются при выполнении любого из следующих действий.

- ввод в ячейку вычисляемого столбца данных, не являющихся формулой;

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

- копирование в вычисляемый столбец данных, не соответствующих формуле столбца;

- ввод новой формулы в вычисляемый столбец, который уже содержит одно или несколько исключений;

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

В формулах вычисляемых столбцов используются структурированные ссылки на табличные данные. Структурированная ссылка имеет следующие составные части:

- имя таблицы;

- указатель специального элемента таблицы, с помощью которого можно ссылаться на конкретные части таблицы, например, на строку;

- указатель столбца.

Имя присваивается таблице при создании.

Указатель специального элемента начинается с символа # и заключается в квадратные скобки.

Указатель столбца представляет собой заключенный в квадратные скобки заголовок столбца.

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

Пример структурированной ссылки:

Таблица4[[#Эта строка];[Должность]]

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

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

- отредактировать формулу в любой ячейке столбца;

- скопировать другую формулу в любую ячейку столбца.

Чтобы удалить вычисляемый столбец, надо на вкладке Главная в группе Ячейки в списке Удалить выбрать команду Удалить столбцы таблицы.

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

Для добавления к таблице итоговой строки надо:

1. Выделить любую ячейку таблицы.

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

3. В строке итогов щелкнуть по ячейке столбца, для которого нужно рассчитать итог, и из раскрывающегося списка выбрать нужную функцию.

Форма данных

Форма – это такой режим базы данных, в котором записи выводятся на экран по одной.

Форма данных выводится на экран с помощью кнопки Форма. Этой кнопки нет среди ленточных команд, поэтому сначала ее необходимо добавить на панель быстрого доступа:

1. Щелкнуть мышью по кнопке раскрытия списка на панели быстрого доступа.

2. Выбрать строку Другие команды.

3. В поле Выбрать команды из выбрать вариант Команды не на ленте.

4. Выбрать в списке команду Форма.

5. Щелкнуть по кнопке Добавить.

Чтобы вывести на экран форму данных, надо поместить курсор в любое место таблице и щелкнуть по кнопке Форма на панели быстрого доступа.

В Microsoft Excel форма данных представляет собой диалоговое окно, в котором поочередно выводятся отдельные полные записи списка.

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

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

С помощью кнопок команд в диалоговом окне формы данных можно выполнять следующие операции:

- добавить новую запись: щелкнуть по кнопке Добавить и ввести данные в поле ввода;

- удалить выведенную на экран запись: щелкнуть по кнопке Удалить;

- отменить изменения в выведенной на экран записи: щелкнуть по кнопке Вернуть;

- перейти к предыдущей или последующей записи: с помощью кнопок Назад и Далее, кроме того, для перехода к другим записям можно использовать полосу прокрутки;

- найти запись по заданным критериям: с помощью кнопки Критерии.

Поиск записей по заданным критериям

После выбора в диалоговом окне формы данных кнопки Критерии Microsoft Excel изменяет форму данных таким образом, что вводимые в поля данные интерпретируются как условия поиска, называемые критериями сравнения.

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

Чтобы найти удовлетворяющие критериям записи, следует щелкать по кнопкам Далее и Назад. Чтобы выйти из режима поиска, надо щелкнуть по кнопке Правка.

Сортировка данных в таблице

В процессе сортировки таблицы Microsoft Excel переупорядочивает строки в соответствии с содержимым одного или более столбцов. Сортировка по нескольким столбцам используется для группировки данных с одинаковыми значениями в одном столбце и последующей сортировки данных другого столбца в группах с одинаковыми значениями.

Таблицы можно сортировать в возрастающем (от 0 до 9, от А до Я, от A до Z) или убывающем (от 9 до 0, от Я до А, от Z до A) порядке.

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

- на вкладке Главная в группе Редактирование в списке Сортировка и фильтр выбрать команду Фильтр;

- на вкладке Данные в группе Сортировка и фильтр щелкнуть по кнопке Фильтр;

- нажать клавиши Ctrl + Shift + L.

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

Отсортировать таблицу по нескольким столбцам можно одним из двух способов.

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

II способ:

1. На вкладке Главная в группе Редактирование в списке Сортировка и фильтр выбрать команду Настраиваемая сортировка или в контекстном меню любой ячейки таблицы выбрать команду Сортировка, затем ‑ Настраиваемая сортировка.

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

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

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

5. Щелкнуть по кнопке ОК.

После сортировки таблицы на копках раскрытия списка в заголовках столбцов, по которым проводилась сортировка, появляются маленькие вертикальные стрелки.

Фильтрация данных в таблице

Фильтрация – это отбор записей, удовлетворяющих заданным условиям. Фильтрация выводит на экран нужные записи, временно скрывая остальные.

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

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

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

В Microsoft Excel имеется несколько способов фильтрации данных в таблицах.

Фильтр по выделенному

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

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

2. В контекстном меню этой ячейки выбрать команду Фильтр.

3. Выбрать нужный вариант фильтра.

Автофильтр

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

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

2. В открывшемся списке выбрать значения, которые нужно использовать в качестве условия отбора, т.е. установить флажки рядом с этими значениями и снять флажки у остальных значений.

3. Щелкнуть по кнопке ОК.

В этом же списке есть команда для снятия фильтра.

Пользовательский автофильтр

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

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

2. В зависимости от типа данных столбца выбрать команду Текстовые фильтры, Числовые фильтры или Фильтры по дате.

3. В открывшемся списке выбрать нужный оператор сравнения или команду Настраиваемый фильтр.

4. В открывшемся диалоговом окне Пользовательский автофильтр ввести условие отбора.

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

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

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

5. Щелкнуть по кнопке ОК.

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

Для фильтрации списка по более сложным условиям используется расширенный фильтр.

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

Для использования расширенного фильтра сначала надо подготовить блок условий:

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

2. В верхнюю строку блока условий скопировать имена полей, для которых надо задать условия отбора. Если для одного поля задается несколько условий, то имя этого поля копируется несколько раз.

3. В следующих строках (под строкой с именами полей) ввести условия отбора в соответствии со следующими правилами:

- каждое условие вводится в столбце с именем того поля, для которого задается условие;

- если искомые записи должны удовлетворять нескольким условиям одновременно, то эти условия вводятся в одной строке (соответствует логическому оператору И);

- если искомые записи должны удовлетворять хотя бы одному из условий, то эти условия вводятся в разные строки (соответствует логическому оператору ИЛИ).

Примеры блоков условий:

1. Фамилии сотрудников начинаются на букву С:

Фамилии
С*

2. Стаж работы больше 5 лет:

Стаж работы
>5

3. Оклад от 12000 до 20000 руб.:

Оклад Оклад
>12000 <20000

4. Стаж работы меньше 5 лет или оклад меньше 15000 руб.:

Стаж работы Оклад
<5  
  <15000

5. Техники конструкторского отдела, у которых либо стаж работы больше 5 лет, либо оклад больше 12000 руб.:

Отдел Должность Стаж работы Оклад
конструкторский техник >5  
конструкторский техник   >12000

Чтобы применить расширенный фильтр

1. Подготовить блок условий.

2. Выделить любую ячейку из таблицы.

3. На вкладке Данные в группе Сортировка и фильтр выбрать команду Дополнительно.

4. В поле Исходный диапазон ввести ссылку на диапазон таблицы.

5. В поле Диапазонусловий ввести ссылку на блок условий.

6. Чтобы показать результат фильтрации, скрыв ненужные строки в списке, установить переключатель Обработка в положение Фильтровать список на месте.

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

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

7. Щелкнуть по кнопке ОК.

В качестве условия отбора можно использовать значение, вычисляемое при помощи формулы.

Синтаксис условия отбора в этом случае:

= Ссылка Оператор сравнения Выражение

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

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

Пример: блок условия для отбора данных о сотрудниках, зарплата которых превышает среднюю:

 
=G50>СРЗНАЧ(Таблица4[Оклад])

Функции для работы с базами данных

В Microsoft Excel имеются функции для работы с базами данных. Каждая из этих функций использует три аргумента: база данных, поле, критерий:

- База данных − это диапазон ячеек, в которых располагается база данных;

- Поле определяет столбец, используемый функцией; аргумент Поле может быть задан как адрес ячейки, содержащей имя поля, как текст с названием поля (столбца) в кавычках или как число, задающее положение столбца в списке;

- Критерий − ссылка на диапазон ячеек, задающих условие отбора.

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

Дана база данных:

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

Используется функция БСЧЕТА() с аргументами:

- база данных ‑ A1:F8;

- поле ‑ A1 (адрес ячейки с заголовком столбца Фамилии);

- критерий ‑ H1:H2 (диапазон ячеек, содержащих условие отбора).

2. Вычислить сумму выплат всех инженеров.

Используется функция =БДСУММ(A1:F8; F1; J1:J2).

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

Подготавливается таблица:

В ячейку M1 вводится формула

=БСЧЕТА(A1:F8; A1; H1:H2).

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

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

Подготавливается таблица:

В ячейку P1 вводится формула

= БДСУММ (A1:F8; F1; J1:J2).

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

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

Подготавливается таблица:

В ячейку R1 вводится формула =БДСУММ (A1:F8; E1; H1:H2).

В диалоговом окне команды Таблица данных в поле Подставлять значения по строкам в дается ссылка на ячейку H2, в поле Подставлять значения по столбцам в − ссылка на ячейку E1.

Практическая работа 9

Работа с базами данных в Microsoft Excel

1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Столбцы таблицы: Фамилия, Отдел, Должность, Дата найма.

Заполнить таблицу произвольными данными (10 строк); в столбце Отдел использовать 3−4 названия (например, бухгалтерия, отдел кадров, транспортный отдел, конструкторский отдел), в столбце Должностьиспользовать 5−6 названий (например, техник, инженер, экономист, водитель и т. д.).

2. Добавить к таблице вычисляемые столбцы: Стаж работы, Оклад, Надбавка, Премия, Всего начислено, Пенсионный фонд, Налог, Выплатить.

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

Стаж работы рассчитывается как частное от деления разности между текущей датой и датой найма на количество дней в году;

Надбавка составляет 5% от оклада для работников со стажем от 5 до 10 лет и 10% от оклада для работников со стажем более 10 лет;

Остальные столбцы рассчитываются по следующим формулам:

Премия = 20% (Оклад + Надбавка);

Всего начислено = Оклад + Надбавка + Премия;

Пенсионный фонд = 1% Всего начислено;

Налог = 13%(Всего начисленоПенсионный фонд);

Выплатить = Всего начисленоПенсионный фондНалог.

3. Добавить к таблице еще 5 строк.

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

5. Назначить всем столбцам, содержащим денежные величины, финансовый формат.

6. Присвоить рабочему листу имя Сведения о сотрудниках. Это же название можно использовать в качестве заголовка таблицы.

7. Используя форму данных, добавить в таблиц еще 5 записей.

8. Используя форму данных, выполнить поиск записей по следующим критериям:

- заданная должность;

- заданный отдел;

- стаж работы больше заданного;

- заданная должность и оклад меньше заданного;

- заданный отдел и стаж работы больше заданного.

9. Отсортировать таблицу по фамилиям.

10. Отсортировать таблицу по отделам (от А до Я), а внутри отделов – по стажу работы сотрудников (от максимального к минимальному). Использовать способ многократной сортировки от менее значимых к более значимым столбцам.

11. Отсортировать таблицу по отделам, внутри каждого отдела – по должностям, а для одинаковых значений поля Должность – по фамилиям. Порядок сортировки по всем столбцам ‑ от А до Я. Использовать диалоговое окно команды Настраиваемая сортировка.

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

13. Используя автофильтр, отобрать данные:

- о сотрудниках двух заданных отделов;

- о сотрудниках, занимающих одну из трех заданных должностей;

- о сотрудниках заданного отдела, занимающих одну из двух заданных должностей.

14. Используя пользовательский автофильтр, отобрать данные:

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

- о сотрудниках со стажем работы больше заданного;

- о сотрудниках, стаж работы которых находится в заданном диапазоне;

- о сотрудниках с окладом выше среднего;

- о 5-ти сотрудниках с наибольшими премиями;

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

- о сотрудниках, нанятых в прошлом году;

- о сотрудниках, нанятых во 2-м квартале;

- о сотрудниках заданного отдела со стажем работы выше среднего.

15. Используя расширенный фильтр, отобрать данные о сотрудниках с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Отдел, Оклад.

16. Используя расширенный фильтр, отобрать данные о сотрудниках, у которых либо стаж работы больше заданного, либо оклад меньше заданного. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж работы, Оклад.

17. Используя расширенный фильтр, отобрать данные о сотрудниках со стажем работы в заданном диапазоне и с окладом в заданном диапазоне. В отфильтрованную таблицу включить поля Фамилия, Должность, Стаж работы, Оклад.

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

19. Используя расширенный фильтр, отобрать данные о сотрудниках, у которых стаж работы либо меньше 2 лет, либо от 10 до 15 лет. В отфильтрованную таблицу включить поля Фамилия, Отдел, Стаж, Оклад.

20. Используя расширенный фильтр, отобрать данные о сотрудниках:

- оклад которых выше среднего;

- оклад которых выше среднего, а стаж работы от 5 до 15 лет;

- оклад которых выше среднего, а стаж работы – меньше среднего;

В условиях отбора использовать функцию СРЗНАЧ.

В отфильтрованные таблицы включить поля Фамилия, Стаж работы, Оклад.

21. Используя функции базы данных, вычислить:

- количество сотрудников со стажем работы больше заданного;

- количество сотрудников с окладом меньше среднего;

- количество сотрудников заданного отдела с заданной должностью;

- сумму окладов всех сотрудников с заданной должностью;

- сумму надбавок всех сотрудников заданного отдела со стажем больше 10 лет;

- наименьшую премию среди сотрудников со стажем работы от 5 до 15 лет;

- наименьший налог среди сотрудников с окладом больше среднего.

22. Используя таблицу подстановки и функции базы данных, создать таблицы, отображающие:

- количество сотрудников, занимающих каждую должность;

- сумму выплат, окладов, надбавок и премий в каждом отделе;

- сумму выплат, «всего начислено» и налогов по каждой должности;

- количество различных должностей по отделам.

Создание сводных данных

Суммирование данных списка

Под списком понимается набор строк, содержащий однотипные данные и удовлетворяющий следующим условиям:

- первая строка диапазона данных содержит заголовки столбцов;

- столбцы содержат однотипные данные;

- отсутствуют пустые строки и столбцы.

Для быстрого суммирования данных списка и разбиения списка на группы программа Microsoft Excel предлагает процедуру вставки автоматически рассчитанных итогов.

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

При автоматическом подведении итогов строки промежуточного и общего итогов вставляются в список, который структурируется.

Microsoft Excel рассчитывает промежуточные итоги с помощью выбранной итоговой функции. Можно одновременно вывести на экран промежуточные итоги, рассчитанные разными способами.

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

Подведение промежуточных итогов

1. Отсортировать список по столбцу, по которому надо образовать группы.

2. Выделить любую ячейку в списке.

3. На вкладке Данные в группе Структура выбрать командуПромежуточные итоги.

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

5. В поле Операция выбрать одну или несколько функций, необходимых для подведения итогов:

- сумма – сумма чисел (используется по умолчанию для подведения итогов по числовым полям);

- количество − количество записей (используется по умолчанию для подведения итогов по нечисловым полям);

- среднее – среднее чисел;

- максимум – максимальное число;

- минимум – минимальное число.

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

6. В поле Добавить итоги по:выбрать столбцы, по которым необходимо подвести итоги.

7. Если нужно разместить итоговые строки под данными, включить режим Итоги под данными, иначе – выключить этот режим.

8. Чтобы вставить разрывы страницы после каждой группы, включить режим Конец страницы между группами.

9. Если нужно заменить ранее вычисленные итоги новыми, включить режим Заменить текущие итоги.

10. Щелкнуть по кнопке ОК.

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

Группы можно разделить на подгруппы и вставить вложенные уровни промежуточных итогов для подгрупп. Для этого надо:

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

2. Выполнить операцию подведения итогов, указав имя столбца, по которому данные надо разделить на группы.

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

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

Чтобы удалить итоговые строки, надо на вкладке Данные в группе Структура выбрать командуПромежуточные итоги и в открывшемся диалоговом окне щелкнуть по кнопке Убрать все.

Структурирование рабочих листов

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

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

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

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

Рис. 11

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

При структурировании рабочего листа на экран выводятся панели с элементами управления структурой: слева ‑ для структуры, созданной из строк, и сверху – для структуры из столбцов. На панелях изобража­ются лини, объединяю­щие группы, называе­мые линейками уровня. На этих линиях распо­ложены кнопки Скрыть детали:

Щелчок по такой кнопке скрывает все строки группы, оставляя только итоговую строку. После этого кнопка Скрыть детали заменяется кнопкой Отобразить детали , щелчок по которой возвращает на экран скрытые строки. Кроме того, для управления структурой можно использовать команды меню: на вкладке Данные в списке Структура кнопки Отобразить детали и Скрыть детали.

 








Не нашли, что искали? Воспользуйтесь поиском по сайту:



©2015 - 2024 stydopedia.ru Все материалы защищены законодательством РФ.