Работа со списками (базами данных)

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

 

Создание списка (базы данных)

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

Для создания списка с помощью формы (маски ввода):

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

2. Щелкните на любой из ячеек заглавной строки и выберите команду Данные / Форма.

3. В отрывшемся диалоговом окне, содержащем поля, название и количество которых соответствует созданным заголовкам столбцов введите данные в каждое поле. Ширина полей соответствует самому широкому столбцу заголовка. Для перехода между полями можно пользоваться указателем мыши, либо клавишами <Tab> – для перехода вниз и < Shift><Tab> – для перехода вверх.

4. Нажмите кнопку Добавить для помещения значений данной записи в список и введите следующую запись.

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

Поиск записей в списке

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

1. Установите курсор в любую ячейку списка и выберите команду Данные/ Форма.

2. Нажмите кнопку Критерии.

3. Введите критерии поиска а одно или несколько полей записи. Так, для поиска всех фамилий, начинающихся на букву А, достаточно в поле фамилии набрать А. для поиска записей с величиной оклада, большей 500000, в поле оклада следует ввести >500000.

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

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

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

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

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

¨ Расширенный фильтр для более сложных условий отбора.

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

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

Этап 1. Формирование диапазона условий по типу Критерий сравнения

1.Скопируйте все имена полей списка (рис.14) в другую область на том же листе, например установив курсор в ячейку J1. Это область, где будут формироваться условия отбора записей. Например, блок ячеек J1:O1 – имена полей области критерия, J2:O5 – область значений критерия.

Рис. 4.5

 

2.Сормируйте в области условий отбора Критерий сравнения – о сдаче экзаменов студентами группы 133 по предмету п1 на оценки 4 или 5. Для этого в первую строку после имен полей введите:

в столбец Номер группы - точное значение 133;

в столбец Код предмета – точное значение п1;

в столбец Оценка – условие - >3.

Этап 2. Фильтрация записей расширенным фильтром.

Произведите фильтрацию записей на том же листе:

· установите курсор в область списка (базы данных);

· выполните команду Данные, Фильтр, Расширенный фильтр;

· в диалоговом окне <<Расширенный фильтр >> с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установить флажок

Исходный диапазон: А1:G17

Диапазон условия: J1:O5

Поместить результат в диапазон:J6

· Нажмите кнопку ОК.

3.Придумайте собственные критерии отбора по типу Критерий сравнения и проведите фильтрацию на том же листе.

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

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

· В столбец Вид занятия введите точное значение – букву л;

· Переименуйте в области критерия столбец Оценка, например, на имя Оценка1;

· В столбец Оценка 1 введите вычисляемый критерий, например, вида

=G2>CPЗНАЧ($G$2:$G$17),

где G2 – адрес первой клетки с оценкой в исходном списке,

$G$2:$G$17- блок ячеек с оценками,

CPЗНАЧ – функция вычисления среднего значения.

Этап 2. Фильтрация записей с расширенным фильтром.

Произведите фильтрацию записей на новом листе:

· Установите курсор в область списка (базы данных);

· Выполните команду Данные, Фильтр, Расширенный фильтр;

· В диалоговом окне Расширенный фильтр с помощью мыши задайте параметры, например:

Скопировать результат в другое место: установите флажок

Исходный диапазон: А1:G17

Диапазон условия:J1:O5

Поместить результат в диапазон: перейдите на новый лист и щелкните мышью в любой ячейке

· Нажмите кнопку ОК.

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

 

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

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

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

1. Установите курсор в любую ячейку списка и выберите команду Данные/Сводная таблица.

2. В открывшемся диалоговом окне Мастер сводных таблиц отметьте опцию в списке или базе данных MS Excel.

3. Далее определите диапазон, с которым будет работать Мастер сводных таблиц. Щелкните на кнопке Далее.

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

5. Далее определяется место, в которое будет помещена разработанная сводная таблица.

6. После нажатия на кнопку Готово в указанном месте появляется таблица со сводными данными.

 

Задания на лабораторную работу

Задание 4.4.1

1. Построить таблицу по приведенной ниже форме и заполнить 5 строк ее условными данными. Для получения значений граф 5, 7 и 8 используйте расчетные формулы. Результаты округлить до двух знаков после запятой, используя функцию ОКРУГЛ.

Номенклатурный номер	Наименование продукции	Кол-во (шт.)	Цена (тыс.руб.)	Сумма (тыс.руб.)	% скидки	Сумма скидки (тыс.руб.)	Стоимость с учетом скидки (тыс.руб.)
	Монитор
	Клавиатура
	Дискета
	Принтер
	Сканер

2. Рассчитать процент скидки (гр. 6) по каждому наименованию продукции, исходя из того, что процент скидки назначается в зависимости от последней цифры номенклатурного номера: 1-5%, 2-7%, 3-10%, 4-15%, 5-20%. Для расчета использовать функцию ПРОСМОТР (или ЕСЛИ), а для определения последней цифры номенклатурного номера функцию ОСТАТ.

3. Ввести текущее значение даты и времени между названием таблицы и ее шапкой.

4. Выполнить обрамление всей таблицы, убрать сетку.

5. Используя Мастер функций, определить максимальное значение графы 6.

6. По данным граф 2, 5 и 8 таблицы построить гистограмму с легендой, заголовком, названием осей.

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

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

Задание 4.4.2

Используя операции с матрицами, решить систему уравнений:

Задание 4.4.3

С помощью инструмента Подбор параметра:

а) решить уравнение ;

б) определить экстремумы функции при условии .

Задание 4.4.4

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

на отрезке [-1; 3] с шагом 0,2.

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

 

Задание 4.4.5

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

2. Оформите название таблицы командой Объединить и поместить в центре.

3. Заполните данные по оценкам с использованием функции получения случайных чисел в пределах от 5 по 10 включительно (СЛЧИС() и ЦЕЛОЕ). Применив спецвставку, оставьте в этих ячейках только их значения.

4. Выполните подсчет общего балла командой Автосуммирование.

5. Выполните подсчет средних значений по каждому абитуриенту и экзамену с помощью функции СРЗНАЧ. ( Нажать f(x), выбрать функцию СРЗНАЧ, ввести формулу СРЗНАЧ(С8:F8))

6. В столбце Сообщение о зачислении используйте функцию ЕСЛИ для сообщения "Зачислен", если сумма баллов больше проходного, и сообщения "Отказать" в обратном случае. (Нажать f(x), выбрать функцию ЕСЛИ, ввести условие ЕСЛИ(G8>=E5,"Зачислен";"Отказать")

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

8. Примените шрифтовое оформление, заливку.

9. Поменяв оценки у какого-нибудь абитуриента, убедитесь в работоспособности всех ваших формул.

10.Разбейте рабочий лист на печатные страницы.

 

 

Задание 4.4.6

1. Создать таблицу, содержащую сведения о сотрудниках предприятия. Названия колонок: «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Стаж работы», «Зарплата», «Надбавка», «Премия», «Всего начислено», «Пенсионный фонд», «Налогооблагаемая база», «Налог», «Выплатить». Колонки «Фамилия», «Должность», «Отдел», «Дата поступления на работу», «Зарплата» заполнить произвольными данными, при этом в колонке «Зарплата» использовать значения от 3000 до 10 000 руб, в колонке «Должность» использовать 5-6 названий, в колонке «Отдел» использовать 3-4 названия. Значения в остальных колонках рассчитать по формулам:

Стаж работы =(Текущая дата – Дата поступления на работу)/365. Результат округлить до целого.

0, если стаж работы меньше 5 лет;

Надбавка = 5% от зарплаты, если стаж работы от 5 до 10 лет;

10% от зарплаты, если стаж работы больше 10 лет.

Премия =20%(Зарплата + Надбавка).

Всего начислено = Зарплата + Надбавка + Премия.

Пенсионный фонд = 1% от «Всего начислено».

Налогооблагаемая база = Всего начислено – Пенсионный фонд.

12% от Налогооблагаемой базы, если она меньше 1000 руб;

Налог = 20% от Налогооблагаемой базы, если она больше 1000 руб.

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

В таблице должно быть не менее 10 строк.

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

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

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

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

6. Выполнить сортировку данных по: отделам; фамилиям; отделам и фамилиям; отделам, должностям и фамилиям; отделам, должностям, зарплатам, фамилиям.

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

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

ü с зарплатой от 3000 до 4000 руб.;

ü со стажем работы меньше 7 лет или с зарплатой меньше 4000 руб.;

ü со стажем работы от 5 до 10 лет и с зарплатой от 4000 руб. до 5000 руб.;

ü зарплата которых выше средней (СРЗНАЧ);

ü зарплата которых выше средней, а стаж работы от 5 до 15 лет.

9. Создать сводную таблицу суммарных выплат по отделам, внутри отделов – по фамилиям. Для этого в макете поместить поля «Отдел» и «Фамилия» в область «Строка», а поле «Выплатить» - в область «Данные».

10. Скопировать лист со сводной таблицей. Внести изменения в копию, чтобы данные по каждому отделу выводились на отдельной странице (для этого поле «Отдел» переместить в область «Страница»).

11. Создать еще одну копию первой сводной таблицы. Изменить копию так, чтобы подчитывалось количество сотрудников в отделах (для этого удалить из области «Данные» поле «Выплатить» и поместить туда поле «Фамилия»).

 

4.5 Дополнительные упражнения и задания*

Задание 4.5.1 Выполните упражнения 4.1-4.3.

Упражнение 4.1 Создание и анализ графика .

1) Ключевой момент: табличное задание функции. Необходимо выбрать диапазон (например, от 0^о до 180^о) и шаг измерения аргумента (например, ∆x=5^о). (Лучше ∆x=10^о, т.е. от 0^о до 360^о, в соседних столбцах результат пересчитается автоматически)

I способ: Правка/Заполнить/Прогрессия (арифметическая, по столбцам, шаг 5, предельное значение 180)

II способ: Ввести в ячейку В4 нулевое значение, в ячейку В5 – значение 5, выделить обе ячейки, «ухватитесь» мышью за маркер заполнения (в уголке черный крестик) и, не отпуская левой кнопки мыши, проведите указатель до тех пор вниз, пока около указателя не появится число 180 (т.е. В40). Отпустите кнопку мыши, табл. аргументов будет построена.

2) Построение значения функции (аргумент задается в радианах, а не в градусах)

I способ: В ячейку С4 ввести значение в градусах * .

II способ: Воспользоваться соответствующей функцией Excel (f_e).

− Выделить ячейку С4 и ввести латинскими буквами и с помощью функций =sin(радианы(2*В4)). Нажать Enter. (В С4 должен быть 0)

− Возвратиться в ячейку С4, и маркером заполнения заполнить таблицу аргументов (т.е. до С40). (Должны быть числа)

3) Заполнить столбец значений функции , также не забывая про радианы и ссылки на ячейку В4: (D4: D40)

=0,5*(sin(радианы(В4))+cos(радианы(В4))). Нажать Enter. (D4=0,5; D40=-0,5)

4) Строим график функции

− Выделить диапазон ячеек С4:С40 /Запустить Мастер диаграмм /Вкладка нестандартные / Гладкие графики / Далее.

− Появляется Источник данных диаграммы /уточняем Диапазон данных (должно быть $С4:$С40)

− Переход на вкладку Ряды

− Имена по столбцам не давали, это поле оставить свободным.

− В поле Ряд нажмем кнопку Добавить (справа), чтобы одновременно свернуть диалоговое окно (красн. ↑)

− Проводим мышью при нажатой левой кнопке по столбцу D4:D40 и возвращаемся назад (красная ↑)

− Далее такую операцию проделываем с полем ввода Подписи оси Х, иначе по оси Х будут откладываться неверные данные (В4:В40)

− Нажмите Далее / Параметры диаграммы / Заголовок диаграммы (ввести)

− Название осей координат (не обязательно)

− Установить основные линии сетки

− В подписях данных установим «значения»

(!) Если таблица данных небольшая, можно разместить её под графиком, если таблица большая, лучше представить ее а отдельном листе.. Нажав «Далее», разместите диаграмму на отдельном листе.

5) Отделка диаграммы (по желанию)

1 способ: Вызвать диалоговое окно форматирования.

(!) Вернуться назад, нажать внизу Лист 1 (2 и т.д.) <=> диаграмма 1 (2 и т.д.)

2 способ: Двойной щелчок левой кнопки мыши на форматируемом элементе или одинарный щелчок на этом элементе правой кнопки, затем выбрать пункт контекстного меню.

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