Учебно-методическое пособие для студентов вузов Павлодар (стр. 41 из 76)

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

=G5>СРЗНАЧ($E$5:$E$14)

Примечания:

- используемая в условии формула должна ссылаться либо на заголовок столбца (например, «Продажи»), либо на соответствующее поле в первой записи. В приведенном примере G5 ссылается на соответствующее поле (столбец G) первой записи (строка 5) списка;

- при использовании заголовка столбца в формуле условия вместо ссылки или имени диапазона, в ячейке будет выведено значение ошибки #ИМЯ? или #ЗНАЧ!. Эту ошибку можно не исправлять, так как она не повлияет на результаты фильтрации.

Задание

1 Откройте файл Оргтехника.

2 Вставьте новый лист и назовите его Полный ассортимент.

3 Объедините на этом листе данные с листов Факсы и Ксероксы и отредактируйте содержимое листа следующим образом (рисунок 35)

Рисунок 35 – Вид рабочего листа Полный ассортимент

4 Скопируйте содержимое листа Полный ассортимент на 9 листов, которые будут иметь названия соответствующие подпунктам задания (например, лист «а», лист «b» и т.д.):

a) с помощью команды Данные —Фильтр — Автофильтр отфильтруйте базу данных: оставьте только персональные ксероксы;

b) с помощью команды Данные —Фильтр — Автофильтр отфильтруйте базу данных: оставьте только профессиональные и профессиональные плюс факсы;

c) с помощью команды Данные —Фильтр — Автофильтр отфильтруйте базу данных: оставьте10 наиболее доходных видов оргтехники;

d) с помощью команды Данные —Сортировка отсортируйте базу данных по виду и названию оргтехники;

e) отсортируйте базу данных по названию и количеству в порядке возрастания;

f) отсортируйте базу данных по модели, названию и стоимости;

g) произведите фильтрацию — выведите все товары, у которых количество меньше 300;

h) отфильтруйте базу данных — оставьте только те товары, у которых стоимость больше 4000 тысяч тенге и цена больше 5000 тысяч тенге;

i) оставьте в базе данных только факсы, у которых название начинается на букву «П» и сумма больше 1000000 тысяч тенге.

5 Сохраните рабочую книгу.

Контрольные вопросы

1 Сколько таблиц может содержать база данных в Excel?

2 Что такое запись? Строка?

3 Какие основные операции можно проводить с данными в базе данных?

4 Какое средство в Excel служит для упорядочения ячеек?

5 Можно ли упорядочить названия месяцев не по алфавиту, а в их логическом порядке?

6 Что такое фильтрация базы данных?

7 В каких случаях удобнее применять автофильтр, чем расширенный фильтр?

8 Расскажите порядок фильтрации базы данных по трем и более критериям отбора?

Лабораторная работа №22

Составление консолидированных отчетов. Ввод промежуточных итогов. Сводные таблицы

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

Краткие теоретические сведения

В случае необходимости подвести итог по данным БД в Excel можно использовать средство Итоги. Для подведения промежуточных итогов следует обязательно отсортировать список по столбцу, для которого необходимо подвести промежуточный итог. Затем указать ячейку в списке с данными и выбрать команду Данные - Итоги. В окне Итоги необходимо:

- выбрать столбец, содержащий группы, по которым необходимо подвести итоги, из списка При каждом изменении в. Это должен быть тот столбец, по которому проводилась сортировка списка;

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

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

Подведение «вложенных» итогов. Чтобы «вложить» или вставить итог для групп, находящихся внутри уже существующих групп, следует:

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

- вставить автоматические итоги для первого столбца, содержащего группы суммируемых данных. Этот столбец при сортировке списка должен быть указан в поле Сортировать по. (В примере, приведенном на шаге 1, первым столбцом должен быть столбец регионов.);

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

- выделить ячейку в списке;

- выбрать команду Данные – Итоги;

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

- снять флажок Заменить текущие итоги, а затем нажать кнопку OK.

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

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

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

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

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

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

Чтобы ввести примечание к ячейке, надо выделить ее, а затем выбрать команду Вставка - Примечание. После этого в окно примечания ввести текст и щелкнуть в любом месте за пределами ячейки. В верхнем правом углу ячейки появится красный треугольник. Для просмотра примечания достаточно указать мышью на ячейку с красным треугольником. Excel автоматически отобразит окно с примечанием. Для изменения примечания следует выделить ячейку, а затем выбрать команду Вставка - Изменить примечание. Для удаления примечания следует выбрать команду Правка – Очистить - Примечания.

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

Источниками данных для сводных таблиц являются списки или базы данных, созданные на листах Excel.

Сводная таблица создается с помощью мастера сводных таблиц, используемого для размещения и объединения анализируемых данных. Чтобы начать создание сводной таблицы, надо выбрать команду Данные - Сводная таблица. (Если сводная таблица создается на основе списка на листе Excel, то предварительно можно отметить любую ячейку внутри списка. Это позволит Excel автоматически определить область, которую занимает список)

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

Пример: Внесем на рабочий лист Excel следующие данные (рисунок 36)

Рисунок 36 – Вид рабочего листа

После этого отметим любую ячейку внутри базы данных и вызовем команду Данные - Сводная таблица. На экране появится окно мастера сводных таблиц. Мастер сводных таблиц работает в 3 шага:

На 1-м шаге выбираем источник данных для сводной таблицы. В нашем случае это будет список или база данных Excel.