 |
Выбор элементов списка с помощью Автофильтра
|
|
|
|
В Microsoft Excel можно использовать для фильтрации данных как команду автофильтр, так и команду расширенный фильтр.
· Установить курсор в любую ячейку списка, задать команду фильтр из меню данные, а затем выбрать пункт автофильтр.
· Нажать на кнопку со стрелкой в том столбце, по которому надо фильтровать данные.
· Выбрать необходимый элемент из списка.
Для восстановления всех записей списка необходимо выбрать пункт показать все команды фильтр из меню данные или же в раскрывающемся списке автофильтра выбрать пункт все. Для отмены фильтрации необходимо повторно выбрать пункт автофильтр команды фильтр из меню данные.
Фильтрация списка с использованием сложных критериев (расширенный фильтр)
Данный вид фильтрации может быть применен в тех случаях, когда на столбец нужно наложить более двух условий, а также в том случае, когда результат фильтрации требуется скопировать в другое место.
В данном случае должна быть выполнена следующая процедура:
1. Создать область критериев таким образом, чтобы она не мешала дополнению и расширению списка. Область критериев представляет собой минимум 2 строки, в первой из которых содержатся названия полей из заглавной строки списка, а в остальных строках указываются критерии поиска.
· Для выборки записей, попадающих в некоторый диапазон значений поля (пересечение), в интервал критериев должно быть включено несколько столбцов с названием этого поля. Например, для выборки данных, удовлетворяющих условию 1500<Сумма≤8000 в области критериев должно быть два столбца с именем Сумма.
| Сумма | Сумма |
| >1500 | <=8000 |
· Если на экран надо вывести записи, удовлетворяющие нескольким условиям по одному полю (объединение), то ввод условий производится в разные строки одного столбца.
|
|
|
| Получатель |
| Васильев |
| Иванов |
2. Установить курсор в любую ячейку списка и задать команду фильтр из меню данные, а затем выбрать пункт расширенный фильтр.
· Включить параметр фильтровать список на месте, если результат фильтрации будет располагаться на том же месте, где и сам список или параметр скопировать результат в другое место, если результат нужно поместить в целевую область. Целевую область также следует располагать так, чтобы избежать конфликтов с частями таблицы, выделенными под список и критерии. В первой строке целевой области следует привести имена полей, содержимое которых нужно увидеть в найденных записях (порядок и количество полей может быть произвольным).
· В поле диапазон критериев указать диапазон тех ячеек, где размещается область критериев.
· Если требуется поместить результат в целевую область, то в поле поместить результат в диапазон следует указать диапазон, содержащий заголовок целевой области.
Функции баз данных
Достаточно часто, для того чтобы создать формулы, результаты действия которых отвечают определенным условиям фильтрации, используются функции Excel, предназначенные для работы с базами данных электронной таблицы.
| Функция | Описание функции |
| БДСУММ | Суммирует числа в поле столбца записей БД, удовлетворяющих условию |
| БСЧЕТ | Подсчитывает количествочисловыхячеек в выборке из заданной БД |
| БСЧЕТА | Подсчитывает количествонепустыхячеек в выборке из заданной БД |
| ДМАКС | Возвращает максимальное значение среди выделенных фрагментов БД |
| ДМИН | Возвращает минимальное значение среди выделенных фрагментов БД |
| ДСРЗНАЧ | Возвращает среднее значение выбранных фрагментов БД |
Консолидация
1. Откройте файл RASHOD.xls с сетевого диска COMMON.
2. Построить таблицу из двух столбцов. В первом – виды расхода, во втором – общая сумма по каждому из расходов.
|
|
|
3. Построить таблицу из двух столбцов. В первом – список получателей, во втором – общая сумма полученных денег каждым получателем.
4. Построить таблицу и диаграмму, иллюстрирующую количество и итог выплат по каждому месяцу.
5. Вычислить количество полученных зарплат и общую сумму
Иванова И.И., Казакова С.С. и Пескова В.А..
6. Построить таблицу из двух столбцов. В первом – виды расхода, во втором – общее количество выплат по каждому из расходов.
7. Построить таблицу из двух столбцов. В первом – список получателей, во втором –количество сумм, полученных каждым получателем.
8. Для каждого вида расхода вычислить максимальное и минимальное значение выданной суммы.
9. Построить таблицу из трех столбцов. В первом – день недели, во втором – общая сумма, выплаченная в каждый из дней, в третьем – количество выплат.
10. По данным предыдущего пункта построить гистограмму.
Сводные таблицы
Сводные таблицы – мощное средство получения обобщенной информации из списка.
Задачи для самостоятельного решения
1. Откройте файл rashod.xls с сетевого диска COMMON.
2. Присвойте имя (например, Ведомость) списку. Присвойте имена столбцам списка.
3. Вычислите общую сумму, полученную Казаковым С.С., результат поместите в ячейку F2.
4. Вычислите общую сумму по полю «сумма», результат поместите в ячейку F3.
5. Вычислите количество зарплат, результат поместите в ячейку F4.
6. Вычислите общую сумму по всем видам расхода за март месяц, результат поместите в ячейку F5.
7. Вычислите общую сумму, полученную АО ИНВЕСТ, результат поместите в ячейку F6.
8. Вычислите общую сумму по накладным расходам, результат поместите в ячейку F7.
9. Вычислите общую сумму расходов за январь, результат поместите в ячейку F8.
10. Размер суммы, полученной Голубевым А.Л. за март месяц.
11. Количество выплат АО Престиж и ТОО Надежда за февраль месяц;
12. Количество зарплат, от 1000$ до 5000$ в летние месяцы.
13. Общую сумму выплат по накладным расходам за апрель месяц.
14. Размер суммы, полученной ЗАО БИН и АО Оргсинтез за май и апрель.
15. Скопируйте в любую область рабочего листа данные о накладных расходах и зарплате за июль.
16. Скопируйте в любую область рабочего листа данные о дате, сумме и получателях зарплаты за февраль и март. Для этого предварительно создайте шапку новой таблицы.
|
|
|
17. Скопируйте в любую область рабочего листа данные о материалах.
Прогнозирование
Если имеется возможность периодически наблюдать значение некоторой величины, нас может интересовать, какое значение эта величина примет в следующий момент времени. Например, зная ежедневный курс доллара, как предсказать, какое значение этот курс будет иметь завтра?
В Excel имеется несколько инструментов для прогнозирования, в основе которых применяются различные математические модели:
§ скользящее среднее (в качестве прогноза принимается среднее значение наблюдаемой величины в нескольких последних измерениях) может быть вычислено с помощью функции с именем СРЗНАЧ или надстройки Скользящее среднее;
§ линейный прогноз (к полученным значениям величины приближается прямая линия, на основании которой и рассчитывается прогноз) выполняется с помощью функции с именем ТЕНДЕНЦИЯ или надстройки Регрессия;
§ нелинейный прогноз (принимается, что значение величины изменяется нелинейно) может быть получен с помощью функции с именем РОСТ;
§ экспоненциальное сглаживание (принимается усредненное значение наблюдений, в которое значения последних наблюдений входят с большим весом по сравнению с весом старых наблюдений) выполняется с помощью надстройки Экспоненциальное сглаживание.
|
|
|
