Фильтрация списков

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

Для применения автофильтра нажмите ту же кнопку, что и при сортировке - Сортировка и фильтр и выберите пункт Фильтр (конечно же, перед этим должен быть выделен диапазон ячеек).

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

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

Условное форматирование

Excel 2007 предоставляет еще более мощные и удобные инструменты условного форматирования.

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

Для применения условного форматирования служит кнопка Условное форматирование на панели Стили вкладки Главная, в ней можно ознакомиться с предложенными вариантами форматирования.

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

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

Построение диаграмм

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

Если не устраивает ни один из предложенных вариантов диаграмм, то необходимо воспользоваться кнопкой вызова окна группы «Диаграммы».

После этого надо указать диапазон данных для построения диаграммы. Если данные берутся из всей таблицы, то достаточно указать любую ячейку таблицы. Если надо выбрать лишь определенные данные из таблицы, то надо выделить этот диапазон. Во время выделения можно пользоваться кнопками Shift, Ctrl. Для взаимной замены данных на осях надо воспользоваться кнопкой «Строка/Столбе».

После вставки диаграммы в окне Excel 2007 появляется контекстный инструмент «Работа с диаграммами», содержащий три ленты «Конструктор», «Макет», «Формат». В ленте Конструктор можно изменить Макет диаграммы, Стили диаграмм. В ленте Макет задать Название диаграммы, Название осей, Расположение Легенды и т.д. В ленте Формат можно изменить Стили фигур, Стили WordArt.

5.3 Ход работы

1. Ознакомиться с теоретической справкой.

2. Выполнить задания своего варианта.

3. Подготовить отчёт.

5.4 Технология выполнения работы

Вариант 1

1. Составить и заполнить таблицу «Расходы» по предлагаемому образцу см.рис. 1. (Формат необходимых ячеек установить денежный).

2. Примените автосуммирование значений по каждой статье расходов и по каждому дню недели.

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

Вариант 2

Некая организация закупила для своих подразделений принтеры и сканеры. Общие результаты закупки отражены в следующей таблице.

Товар	Тип	Наименование	Цена	Кол-во	Сумма
Принтер	Струйный	Epson Stylus-3000	1572	8	12576
Принтер	Струйный	Epson Stylus-1500	467	3	1401
Принтер	Струйный	Epson Stylus-Photo 700	304	1	304
Принтер	Лазерный	HP Laserjet 4000	1275	1	1275
Принтер	Лазерный	HP Laserjet 5000	1688	2	3376
Принтер	Лазерный	HP Laserjet Color 8500	7358	1	7358
Сканер	Листовой	Paragon Page 630	43	2	85
Сканер	Листовой	Paragon Page Easy	52	1	52
Сканер	Планшетный	Paragon 800II EP	211	2	422
Сканер	Планшетный	Scan Express 6000 SP	85	3	255
Сканер	Планшетный	Paragon 1200 SP	201	1	201
Сканер	Планшетный	Scan Express A3 P	203	1	203

1. Создайте таблицу по приведённому выше образцу.

2. Для вычисления сумм вставьте необходимые формулы.

3. Установите фильтр на столбец Товар (оставить Сканер);

4. Установить условное форматирование по столбцу Цена(выделить желтым цветом сканеры цена которых превышает 200).

Вариант 3

Создайте таблицу: Командировочные затраты отдела №1

Место назначения	Стоимость проезда	Расходы на 1 ч/д	Количество человек	Количество дней	Сумма, руб.
Смоленск	100	200	2	7
Варшава	150	310	3	4
Оренбург	90	120	5	3
Рим	300	560	7	8
Лондон	290	470	4	6
Тверь	95	109	11	9

1. Заполните столбец «Сумма» по формуле:

Сумма = Стоимость проезда*Количество человек +Расходы на 1 ч/д*Количество человек*Количество дней.

2. Данные в столбцах «Стоимость проезда» и «Сумма» представьте в денежном формате.

3. С помощью соответствующей функции Excel подсчитайте, командировка в какой город потребует максимальной суммы выплат. Результат запишите в свободную ячейку столбца «Сумма, руб.».

Вариант 4

Создайте таблицу: Вычисление чистого дохода предприятия

8.1Название	Доход	НДС	Зар.плата	Соц. страх	Чистый доход
Банк	1000000
Фабрика	990000
Кооператив	5900
Завод	7000000

1. Произвести все необходимые расчеты. Результаты поместить в соответствующие столбцы таблицы.

2. НДС – налог на добавленную стоимость, равный 21% от дохода предприятия; заработная плата составляет 39% от дохода предприятия; страховой взнос составляет 37% от заработной платы.

3. Подсчитайте чистый доход каждого предприятия по формуле: Чистый доход = Доход – НДС – Зар.плата – Соц. Страх.

Вариант 5

	A	B	C	D	E
1	Список товаров
2	Кондитерской фирмы «Мишка-Машка»
3
4	Вид изделия	Название изделия	Количество упаковок по 0,5 кг	Цена в рублях	Стоимость в рублях
5	Шоколад (100 гр)	Аленка	100	12
6	Конфеты	Весна	21	67
7	Карамель	Чебурашка	35	65
8	Карамель	Гусиные лапки	45	48
9	Конфеты	Мишка косолапый	34	120
10	Конфеты	Каракум	27	98
11	Итого

Вариант 6

1. Создайте таблицу Экологическая ситуация в Москве: загрязнение воздуха от стационарных источников.

2. Отформатируйте полученную таблицу.

3. Подсчитайте общие показатели в строке Всего в Москве.

4. Создайте еще одну строку таблицы Средний показатель и заполните ее.

5. Создайте строки Максимальный показатель и Минимальный показатель. Для заполнения этих строк воспользуйся Мастером функций (Вставка/Функции).

Экологическая ситуация в Москве: загрязнение воздуха от стационарных источников
Административный округ	Число источников выбросов вредных веществ в атмосферу (ед.)	Всего отходных вредных веществ от всех стационарных источников (т/год)	Из них поступает на очистные сооружения (т/год)	Из поступивших на очистные сооружения уловлено и обезврежено	Всего попадает в атмосферу
Центральный	4183	18137	13731	13381	4756
Северный	4625	60246	29699	28591	31654
Северо-Западный	1452	16653	9346	9177	7475
Северо-Восточный	3858	46171	41523	40589	5582
Южный	5500	148261	116988	112123	36137
Юго-Западный	811	10358	708	623	9735
Юго-Восточный	5617	121151	68310	66155	54996
Западный	2475	40802	8885	8543	32360
Восточный	6225	59308	25297	24333	34975
Всего в Москве

Вариант 7

Подготовьте таблицу по образцу (скопируйте все округа в предыдущих работах).

Выделите таблицу со строкой заголовка.

В меню Вставка выберите команду Диаграмма или выберите кнопку Диаграмма тип Гистограмма.

Найдите MAX, MIN территорию.

Административный округ	Территория в кв.км
Центральный	64,10
Северный	87,30
Северо-Западный	106,90
Северо-Восточный	102,31
Южный	130,60
Юго-Западный	106,50
Юго-Восточный	112,50
Западный	132,80
Восточный	151,00

Вариант 8

Подготовьте таблицу для начисления пени в соответствии с образцом.

Оплата коммунальных услуг задержана на				дней
Вид оплаты	Начисленная сумма	Пени	Всего к оплате
Квартплата
Газ
Электричество
Телефон

Пени высчитывается по формуле – 1% от начисленной суммы за каждый задержанный день.

Всего к оплате считается как сумма начисления плюс пени.

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

Вариант 9

	А	В	C L С	D	E
1	Ведомость начисления заработной платы
2	№ п/п	Фамилия	Оклад	Материальная помощь	Сумма к выдаче
3	1	Сидоров	1850
4	2	Петров	1000
5	3	Глухов	2300
6	4	Смирнов	950
7	5	Галкин	1100
8	6	Иванов	4500
9	7	Авдеев	3400
10	8	Горшков	2800
11		Всего:

1. Рассчитать графу Материальная помощь, выдавая её тем сотрудникам, чей оклад меньше1500 руб., для этого:

2. Выделить ячейку D3, вызвать Мастер функций, в категории Логические выбрать функцию ЕСЛИ.

3. В диалоговом окне функции указать следующие значения:

Логическое выражение	С3<1500
Значение_если_истина	150
Значение_если_ложь	0