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

Работа 1.

Создание и форматирование таблицы «Наличие товара на складе».

1. Книга «ТОВАР»

2. Лист «БАЗА», в колонтитулы поставить дату создания базы (от руки), свою фамилию и группу, календарную дату работы, название книги и листа.

3. Таблица на 25 наименований товара, которые должны повторяться, т.е. всего 6 наименований.

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

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

 

 

Рисунок 1.

 

• В ячейке С1 введена функция СЕГОДНЯ(),

• «Дата поступления на склад» (последние 3 месяца перед выполнением работы) вводится с клавиатуры и имеет формат Даты и времени;

• «Срок хранения» = текущая «Дата» - «Дата поступления на склад». Имеет формат целого числа, шрифт оформляется различным цветом (Условное форматирование) в зависимости от срока хранения;

• «Количество» и «Стоимость за единицу (р)» вводятся с клавиатуры;

• «Стоимость партии (у.е.)» = «Количество» * «Стоимость за единицу (у.е.)»;

• «Стоимость партии (р)»= «Стоимость партии (у.е.)»* «Стоимость одного (у.е.)»;

• «Стоимость партии (валюта)»= «Стоимость партии (р)»/ «Стоимость единицы (валюта) в руб.», вычисляется в зависимости от курса валюты на текущий день; формат столбцов по стоимости партии – денежный с указанием вида валюты.

Значение поля «Реализация» определяется в зависимости от значения поля «Срок хранения» по таблице:

Срок хранения	Реализация
менее		оставить
менее		реализовать
		списать

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

 

 

Рисунок 2.

 

 

Рисунок 3.

 

Условное форматирование шрифта по значению.

 

Рисунок 4.

 

 

Рисунок 5.

 

 

 

Рисунок 6.

 

 

Рисунок 7.

 

Работа 2.

Книга «СОТРУДНИКИ»

 

1. Лист «БАЗА», в колонтитулы поставить дату создания базы (от руки), свою фамилию и группу, календарную дату работы, название книги и листа.

2. Таблица на 25 фамилий.

Рисунок 8.

 

3. Перед названием таблицы должна быть указана текущая дата, оформленная в формате пользователя «ДДДД ДД ММММ ГГГГ» и дата последнего изменения (ячейка которой имеет примечание).

4. В отдельной ячейке указать значение минимальной заработной платы 1100

Рекомендации к выполнению работы.

Для определения пользовательского формата необходимо выбрать в Формате ячейки все форматы, найти более подходящий, например: ДД.ММ.ГГГГ и изменить его на ДД ДДДД ММММ ГГГГ.

 

Рисунок 9.

 

 

Таблица заполняется следующим образом:

 

№	ФИО	Дата приема на работу	Стаж работы	Должность	Коэффициент	Оклад	Надбавка	Доход	Налог	Начислено
	√	√		√	√
…
									Итого:

 

• «Дата поступления на работу» вводится с клавиатуры и имеет формат Даты и времени;

• «Стаж работы» = (текущая «Дата» - «Дата поступления на работу»)/365,2. Имеет числовой формат с одним знаком после десятичной запятой; оформляется различным цветом в зависимости от значения:

• «Должность» вводится с клавиатуры, наименования должностей должны повторяться;

• «Коэффициент» вводится с клавиатуры, значения от 3 до 10

• «Оклад» = «Минимальный оклад» * «Коэффициент»;

• «Надбавка» вычисляется в зависимости от Стажа работы при помощи вложенной функции ЕСЛИ, формат Процентный;

Сколько лет, до	Надбавка (%)
	10%
	25%
	55%
	80%
	100%

Если

Знач. Стажа работы < 3 лет,

Тогда

надбавка – 10%

Иначе

Если Знач. Стажа работы < 5 лет,

Тогда надбавка – 25%

Иначе

Если Знач. Стажа работы < 8 лет,

Тогда надбавка – 55%

Иначе

Если Знач. Стажа работы < 3 лет,

Тогда надбавка – 80%

Иначе надбавка – 100%

 

 

 

Рисунок 10.

 

· «Доход» = «Оклад» + «Оклад»* «Надбавка»

· «Налог» вычисляется от значения Дахода из таблицы

 

Доход	Налог
менее		13%
менее		17%
менее		21%
свыше		25%

 

· «Начислено» = «Доход» - «Доход»* «Налог»; формат Денежный, шрифт оформляется различным цветом (Условное форматирование).

Выноской отметить наименьшее значение столбца «Начислено».

Подобрать значение Минимальной з/п так, что бы итоговое значение равнялось 555000 р. Для этого надо выделить ячейку с итоговым значение, команда СЕРВИС, подкоманда Подбор Параметра.

 

 

Рисунок 11.

 

 

Рисунок 12.

 

Второй параметр «Значение» набираем с клавиатуры без указания формата, т.е. без «р.», В качестве изменяемой ячейки указываем ячейку где стоит значение минимальной заработной платы.

 

 

Работа 3.

1. Книга «Студенты»

2. Лист «БАЗА», в колонтитулы поставить дату создания базы (от руки), свою фамилию и группу, календарную дату работы, название книги и листа.

3. В ведомости должны указываться даты начала сессии и окончания.

Ведомость сессии группы ЭУП09-03, 1 курс, 1 семестр
	Сессия с	06.06.09	по	27.06.09

 

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

Итак, «шапка» таблицы:

№

ФИО студента

Математика

История России

Физика

Иностранный язык

Средний балл

Срок сдачи последнего экзамена

Стипендия

 

Для начисления стипендии надо создать таблицу условий:

 

Условие	Стипендия
Срок сдачи последнего экзамена позже последнего дня сессии		0р.
Есть хотя бы одна		0р.
Есть хотя бы одна		0р.
Средний балл меньше	4,5	1 000р.
Средний балл меньше		5 000р.
		10 000р.

 

Рисунок 13.

 

 

 

Рисунок 14.

 

 

Рисунок 15.

 

В аргументах функции ИЛИ перечисляем все оценки приравнивая их 2.

 

 

Рисунок 16.

 

При нажатии команды ОК возникает ситуация «Ошибка в формуле т.к. не закончена функция ЕСЛИ

 

 

Рисунок 17.

 

Отвечаем ОК

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

 

 

Рисунок 18.

 

откроется диалоговое окно втрой функции ЕСЛИ где в качестве первого параметра записан оператор ИЛИ

 

 

Рисунок 19.

 

В качестве второго параметра введем адрес ячейки второго значения стипендии «0р.», закрепим этотадрес. В качестве третьего параметра из строки формул вызовем новый оператор ЕСЛИ

 

 

Рисунок 20.

 

В окне нового ЕСЛИ в качестве первого параметра вызываем функцию ИЛИ и сравниваем оценки по предметам с 3, возвращаемся в этот оператор ЕСЛИ и задаем вторым параметром адрес третьего «0р.» (закрепляем) из таблицы условия начисления стипендии.

Третьим параметром вызываем функцию ЕСЛИ (из строки формул) и сравниваем «Средний балл» первого студента с ячейкой условия начисления стипендии, где находится значение 4,5 (закрепляем её).

 

 

Рисунок 21.

 

Третьим параметром вызываем функцию ЕСЛИ (из строки формул) и сравниваем «Средний балл» первого студента с ячейкой условия начисления стипендии, где находится значение 5 (закрепляем её). Второй параметр – адрес ячейки «5000р.» закрепленный, третий параметр – адрес ячейки «10000р.» закрепленный.

 

 

Рисунок 22.

СОРТИРОВКИ

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

 

Рисунок 23.

 

Копируем и на лист 2 в ачейку А2 через команду меню ПРАВКА – Специальная ставка- значения и форматы чисел – ОК

 

 

Рисунок 24.

 

А далее её копируем и вставляем столько раз, сколько необходимо сделать сортировок.

Для сортировки выделяется таблица, далее команда Данные – Сортировка

 

Рисунок 25.

 

Остальное в EXCEL.

Сортировка По ключу должности (База Сотрудники)

Смотрите базу СОТРУДНИКИ, лист Сортировки…

Выборки

Автофильтр

Команда ДАННЫЕ – подкоманда Автофильтр включает для каждого поля данных возможность выбора:

 

 

Рисунок 26.

 

Выбираем полностью всю информацию о студентах сдавших математику на 2.

 

 

Рисунок 27.

Результат выборки необходимо скопировать и вставить ниже,

 

№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Дибров О.Я.					3,0	26.06.06
	Зинко О.Л.					3,0	26.06.06

 

а основную таблицу «развернуть», указав «ВСЕ».

 

 

Рисунок 28.

 

Что бы выбрать всю информацию о студентах, имеющих средний балл от 3,2 до 4,5,выбираем в указателе среднего балла команду Условие,

 

 

Рисунок 29.

 

задаем условие:

.

Рисунок 30.

 

Получим свернутую таблицу, которую копируем и вставляем ниже.

 

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

Команда Расширенный фильтр имеет некоторые дополнительные возможности:

· допускается задавать три и более условий для конкретного столбца;

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

· допускается задавать вычисляемые условия.

Окно команды Расширенного фильтра:

 

 

Рисунок 31.

 

Задание отбора строк в отдельном диапазоне листа зависит от поставленной задачи (см. каждую книгу Базы).

При указании диапазона условий в его «шапке» (это же таблица! У неё должна быть «шапка») копируются соответствующие поля «шапки» исходной таблицы! Например: для выбора данных по четырем студентам копируем название поля ФИО и ниже копируем четыре фамилии из таблицы (должно быть полное соответствие с данными таблицы). Диапазон условия:

ФИО студента

Ианченко И.И.

Зинко О.Л.

Она Р.Д.

Полюс А.Э.

 

Рисунок 32.

 

В EXCEL таблице это выглядит так:

 

Выбрать данные на фамилии Иванченко И.И., Зинко О.Л., Она Р.Д., Полюс А.Э.
	Диапазон условий
	ФИО студента
	Ианченко И.И.
	Зинко О.Л.
	Она Р.Д.
	Полюс А.Э.
Результат выборки:
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Зинко О.Л.					3,0	26.06.06
	Ианченко И.И.					4,5	30.06.06
	Она Р.Д.					5,0	26.06.06
	Полюс А.Э.					4,5	25.06.06

Рисунок 33.

Итоги

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

Например: Вычислить суммарное начисление по каждой должности. (База Сотружники).

Прежде чем вычислять суммарное Начисление по каждой Должности, сортируем всю таблицу по Должности(см. Базу Сотрудники)

 

 

Рисунок 34.

 

 

Рисунок 35.

 

Вычислить: сколько студентов сдали Математику на 2, 3, 4, 5.(Оценки по математике необходимо сортировать.)

 

 

Рисунок 36.

Результат промежуточных итогов

 

 

Рисунок 37.

 

Обратите внимание, что EXCELещё и структурирует список, нажимая на знак ─ можно свернуть итоговую таблицу и построить график. (См. итоги в базе «Товары»)

Вычислим суммарное количество по каждому наименованию товара. Таблицу сортируем по наименованиям товара.

 

 

Рисунок 38.

Результат промежуточных итогов:

 

 

Рисунок 39.

 

1. СОРТИРОВКА

 

База «Студенты»

1.1.1. По алфавиту ФИО;

1.1.2. По убыванию оценок по Математике и возрастанию оценок по Информатике;

1.1.3. По убыванию оценок по Математике и возрастанию оценок по Информатике и убыванию среднего балла.

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

База «Товары»

1.2.1. По возрастанию срока хранения;

1.2.2. По убыванию значений «Количество» и возрастанию значений «Стоимость партии ($)»;

1.2.3. По убыванию стоимости партии в евро и по возрастанию количества.

База «Сотрудники»

1.3.1. По возрастанию даты приема на работу;

1.3.2. По должности (по алфавиту);

1.3.3. По списку должностей. (Сортировка по ключу).

2. Выборки

Фильтр (Автофильтр)

База «Студенты»

2.1.1.1. Выбрать все строки, где математика сдана на 3;

2.1.1.2. Выбрать все строки, где средний балл выше 4,2;

2.1.1.3. Выбрать всех студентов, у которых 3 балла по математике и 4 балла по информатике;

2.1.1.4. Выбрать все данные о студентах, у которых средний ниже 3,75 и балл по математике выше 4.

База «Товары»

2.1.2.1. Выбрать товары, которые необходимо срочно продать;

2.1.2.2. Выбрать товары, количество которых ниже _____________;

2.1.2.3. Выбрать все данные о товарах, поступивших на склад с ____________ по _____________;

2.1.2.4. Выбрать все данные о товарах, которые стоимостью ниже ___ и сроком хранения выше ___;

База «Сотрудники»

2.1.3.1. Выбрать все данные о сотрудниках, поступивших на работу в _______ году;

2.1.3.2. Выбрать все данные о сотрудниках, оклад которых свыше ________;

2.1.3.3. Выбрать все данные о сотрудниках, отработали свыше _________ лет и имеют начисление заработной плата ниже __________;

2.1.3.4. Выбрать все данные о сотрудниках, стаж работы которых от ___ до __ лет;

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

База «Студенты»

2.2.1.1. Выбрать все данные о студентах, сдавших все на ___;

2.2.1.2. Выбрать все данные о студентах, имеющих хотя бы одну ___;

2.2.1.3. Выбрать все данные о студентах, имеющих срок сдачи ______;

2.2.1.4. Выбрать все данные о студентах, имеющих средний балл ниже среднего балла по группе.

База «Товары»

2.2.2.1. Выбрать все данные о товаре, поступившем после __________ стоимостью свыше ____;

2.2.2.2. Выбрать все данные о товаре стоимостью ниже средней всего поступившего товара;

База «Сотрудники»

2.2.3.1. Выбрать все данные о сотрудниках (на три ФИО);

2.2.3.2. Выбрать все данные о сотрудниках, имеющих надбавку свыше ____, и оклад ниже _____;

2.2.3.3. Выбрать все данные о сотрудниках, имеющих стаж работы свыше среднего по всей организации.;

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

 

3. ТАБЛИЦЫ ПРЕДВАРИТЕЛЬНЫХ ИТОГОВ

База «Студенты»

3.1.1. Вычислить количество студентов по каждой отметке за математику.

3.1.2. Вычислить количество студентов, получающих стипендию.

База «Товары»

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

3.2.2. Вычислить суммарное количество по каждому наименованию товара.

База «Сотрудники»

3.3.1. Вычислить количество всех работающих по каждому значению надбавки.

3.3.2. Вычислить количество всех работающих по каждой должности.

3.3.3. Вычислить суммарную заработную плату по каждой должности.

3.3.4. Вычислить минимальную заработную плату по каждой должности

3.3.5. Вычислить минимальный стаж работы по каждой должности.

 

 

Рисунок 40.

 

 

 

Рисунок 41.

 

 

Рисунок 42.

 

 

Рисунок 43.

 

 

 

Рисунок 44.

 

 

Рисунок 45.

 

 

Рисунок 46.

 

 

 

Рисунок 47.

 

 

Специальная вставка

 

Рисунок 48.

 

 

Сортировка по ключу

 

 

Рисунок 49.

 

 

Рисунок 50.

 

 

 

Рисунок 51.

 

 

Рисунок 52.

 

 

Фильтры

 

 

Рисунок 53.

 

 

 

Рисунок 54.

 

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

 

 

Рисунок 55.

 

 

Рисунок 56.

 

 

 

Рисунок 57.

 

 

 

 

Рисунок 58.

 

Основная база
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Иванов И.И.					3,8	26.06.06
	Петров П.П.					5,0	26.06.06
	Сидоров С.С.					5,0	27.06.06
	Полянский А.П.					3,5	30.06.06
	Дибров О.Я.					3,0	26.06.06
	Ианченко И.И.					4,5	30.06.06
	Сидорюк А.А.					4,3	25.06.06
	Максим О.О.					3,3	27.06.06
	Зинко О.Л.					3,0	26.06.06
	Интегра А.Л.					3,8	27.06.06
	Полюс А.Э.					4,5	25.06.06
	Семенова У.Г.					4,0	26.06.06
	Караваев Р.Щ.					4,5	26.06.06
	Она Р.Д.					5,0	26.06.06
	Ним Л.А.					4,5	30.06.06
					СРЗНАЧ	4,1
Выбрать данные на фамилии Иванченко И.И., Зинко О.Л. И Она Р.Д.
	Диапазон условий
	ФИО студента
	Ианченко И.И.
	Зинко О.Л.
	Она Р.Д.					Результат
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Ианченко И.И.					4,5	30.06.06
	Зинко О.Л.					3,0	26.06.06
	Она Р.Д.					5,0	26.06.06
Выбрать данные по всем студентам, Получившим хотябы одну 3
	Диапазон условий
		Математика	История России	Физика	Иностранный язык
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Иванов И.И.					3,8	26.06.06
	Полянский А.П.					3,5	30.06.06
	Дибров О.Я.					3,0	26.06.06
	Сидорюк А.А.					4,3	25.06.06
	Максим О.О.					3,3	27.06.06
	Интегра А.Л.					3,8	27.06.06
	Семенова У.Г.					4,0	26.06.06
	Караваев Р.Щ.					4,5	26.06.06
Выбрать данные по всем студентам, Получившим все 5
		Математика	История России	Физика	Иностранный язык
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Петров П.П.					5,0	26.06.06
	Сидоров С.С.					5,0	27.06.06
	Она Р.Д.					5,0	26.06.06
Выбрать данные по всем студентам, имеющим ср. балл выше среднего по группе
	Условие
	ЛОЖЬ
№	ФИО студента	Математика	История России	Физика	Иностранный язык	Средний балл	Срок сдачи последнего экзамена	Стипендия
	Петров П.П.					5,0	26.06.06
	Сидоров С.С.					5,0	27.06.06
	Ианченко И.И.					4,5	30.06.06
	Сидорюк А.А.					4,3	25.06.06
	Полюс А.Э.					4,5	25.06.06
	Караваев Р.Щ.					4,5	26.06.06
	Она Р.Д.					5,0	26.06.06
	Ним Л.А.					4,5	30.06.06

 

 

Рисунок 59.

 

Промежуточные итоги: на ленте ДАННЫЕ команда Промежуточные итоги

 

Рисунок 60.

 

Но необходимо сортировать данные по «Математика»

 

Рисунок 61.

 

 

 

Рисунок 62.

 

 

 

Рисунок 63.

 

 

 

Рисунок 64.

 

 

 

Рисунок 65.

 

 

Рисунок 66.

 

 

Поделиться:

Воспользуйтесь поиском по сайту: