Фильтрация данных в диапазоне или таблице

Методические указания по выполнению индивидуального задания по Excel

Создание графика

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

A B C Дата Суточное количество осадков Твердые частицы 01.01.07 4,1   02.01.07 4,3   03.01.07 5,7   04.01.07 5,4   05.01.07 5,9   06.01.07 5,0   07.01.07 3,6   08.01.07 1,9   09.01.07 7,3

1. Выделите данные, которые следует нанести на график (от А2 до С10).

2. На вкладке Вставка в группе Диаграммы нажмите кнопку График.

3. Выберите тип График с маркерами.

4. Щелкните в области диаграммы (Область диаграммы. Область размещения диаграммы и всех ее элементов.) (Вкладка Макет доступна только тогда, когда мы находимсяв области диаграммы).

5. Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

6. На вкладке Конструктор в группе Стили диаграмм выберите стиль, который хотите использовать.

7. На вкладке Макет в группе Подписи нажмите кнопку Название диаграммы и выберите пункт Над диаграммой.

8. Щелкните название диаграммы и введите нужный текст.

Совет. Для данного графика было задано название Уровни содержания твердых частиц в осадках.

9. Чтобы уменьшить размер названия диаграммы, щелкните название правой кнопкой мыши и введите нужный размер в контекстном меню в поле Размер.

10. На диаграмме щелкните легенду или выберите ее в списке элементов диаграммы (вкладка Макет, группа Текущий фрагмент, поле Элементы диаграммы).

11. На вкладке Макет в группе Подписи нажмите кнопку Легенда и выберите нужную позицию.

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

12. Чтобы нанести один из рядов данных вдоль вспомогательной вертикальной оси, выделите ряд данных по количеству осадков или выберите его в списке элементов диаграммы (вкладка Макет, группа Текущий фрагмент, поле Элементы диаграммы).

13. На вкладке Макет в группе Текущий фрагмент нажмите кнопку Формат выделенного фрагмента.

14. В группе Параметры ряда выберите вариант По вспомогательной оси и нажмите кнопку Закрыть.

15. На вкладке Макет в группе Подписи нажмите кнопку Названия осей и выполните действия, описанные ниже.

Чтобы добавить название основной вертикальной оси, выделите пункт Название основной вертикальной оси и затем выберите нужный тип названия вертикальной оси.

Совет. Для данного графика было выбрано Повернутое название.

§ Чтобы добавить название вспомогательной вертикальной оси, выделите пункт Название вспомогательной вертикальной оси и затем выберите нужный тип названия вертикальной оси.

Совет. Для данного графика было выбрано Повернутое название.

§ Щелкните каждое из названий, введите нужный текст и нажмите клавишу ВВОД.

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

В закладке Формат оформите диаграмму по своему желанию.

Фильтрация данных в диапазоне или таблице

Скрыть все

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

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

Щелкните стрелку рядом с заголовком столбца.

Выполните одно из следующих действий:

Выбор из списка чисел

§ В списке чисел выделите или снимите выделение одного или нескольких чисел, по которым требуется выполнить отбор. Затем ОК.

Список может содержать до 10 000 чисел. При большом объеме списка снимите в его верхней части флажок (Выделить все) и выберите конкретные числа, по которым требуется выполнить отбор (например, 4,1 и 4,3).

Создание условий

1. Выберите команду Числовые фильтры, а затем — одну из команд оператора сравнения (Оператор сравнения. Знак, используемый в условиях для сравнения двух значений. Имеется шесть стандартных операторов: = (равно), > (больше), < (меньше), >= (больше или равно), <= (меньше или равно), <> (не равно).) или вариант Настраиваемый фильтр.

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

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

Например, чтобы выполнить отбор чисел в диапазоне между 4 и 5, введите числа 4 и 5 (в результате будут выбраны строки, где к-во осадков равно 4.1, 4.3, 5).

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

Выполните одно из следующих действий:

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

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

Выберите оператор сравнения, а затем в поле справа введите число или выберите числовое значение из списка.

Чтобы развернуть первоначальный вид отфильтрованного столбца, щелкните стрелку рядом с заголовком столбца, затем Выделить все, ОК.

Добавление примечания

Скрыть все

В приложении Excel в ячейку можно добавить комментарий, вставив в нее примечание. Текст в примечаниях можно редактировать. Ненужные примечания можно удалить.

1. Щелкните ячейку, к которой нужно добавить примечание.

2. На вкладке Обзор в группе Примечания выберите команду Создать примечание.

3. В текстовом поле примечания введите текст примечания.

Примеры создания условий (критериев)

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

Несмотря на то, что диапазон условий может находиться в любом месте листа, не следует помещать его под списком. Это связано с тем, что данные, добавляемые в список, вставляются начиная с первой строки после списка. Если эта строка уже содержит данные, Microsoft Excel не сможет добавить новые данные в список.

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

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

В следующих подразделах приведены примеры условий.

Пример 1. Логическое выражение: ((Продажи > 6 000 И Продажи < 6 500) ИЛИ (Продажи < 500))

В диапазоне данных (A6:C10) диапазон условий отбора (C1:D3) отображает строки, содержащие значения от 6000 до 6500, а также значения меньше 500 в столбце «Продажи» (A8:C10).

 

	A	B	C	D
	Тип	Продавец	Продажи	Продажи
			>6 000	<6 500
			<500	Критерий
	Тип	Продавец	Продажи	Исходная таблица
	Напитки	Рощин	5 122р.
	Мясо	Белов	450р.
	фрукты	Батурин	6 328р.
	Фрукты	Белов	6 544р.

Пример 2. Рассмотрим критерии такого вида и функции баз данных:

A B C D E F Дерево Высота Возраст Урожай Доход Высота ="=Яблоня" >10       <16 ="=Груша"           Дерево Высота Возраст Урожай Доход   Яблоня       105,0   Груша       96,00   Вишня       105,0   Яблоня       75,00   Груша       76,80   Яблоня       45,00

Функции базы данных по рассмотренным выше критериям.

=БСЧЕТ(A4:E10;"Возраст";A1:F2)

Эта функция просматривает записи о яблонях, которые имеют высоту от 10 до 16, и подсчитывает количество числовых полей «Возраст» в этих записях (1)

=ДМАКС(A4:E10;"Доход";A1:A3)

Эта функция вычисляет наибольший доход от яблоневых и грушевых деревьев (105)

=БДСУММ(A4:E10;"Доход";A1:A2)

Эта функция вычисляет общий доход от яблонь (225)

=БДСУММ(A4:E10;"Доход";A1:F2)

Эта функция вычисляет общий доход от яблонь, имеющих высоту от 10 до 16 (75)

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

=''= строка ''

строка — значение, которые нужно найти.

Например:

Введено в ячейку	Результат вычисления
="=3 000"	=3 000

Функция ЕСЛИ

Скрыть все

Возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.

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

Синтаксис

ЕСЛИ (лог_выражение; значение_если_истина;значение_если_ложь)

Лог_выражение — любое значение или выражение, принимающее значения ИСТИНА или ЛОЖЬ. Например, A10=100 — логическое выражение; если значение в ячейке A10 действительно равно 100, это выражение принимает значение ИСТИНА, а в противном случае — значение ЛОЖЬ. Этот аргумент может использоваться в любом операторе сравнения.

Значение_если_истина — значение, которое возвращается, если аргумент «лог_выражение» имеет значение ИСТИНА. Аргумент «значение_если_истина» может быть формулой.

Значение_если_ложь — значение, которое возвращается, если «лог_выражение» имеет значение ЛОЖЬ. Аргумент «значение_если_ложь» может быть формулой.

Замечания

В качестве значений аргументов «значение_если_истина» и «значение_если_ложь» можно для построения более сложных проверок использовать до 64 вложенных друг в друга функций ЕСЛИ.

Пример 1

A Данные   Формула Описание (результат) =ЕСЛИ(A2<=100;"Внутри бюджета";"Вне бюджета") Если приведенное выше число меньше или равно 100, формула отображает строку «В пределах бюджета». В противном случае отображается строка «Превышение бюджета» (В пределах бюджета) =ЕСЛИ(A2=100;СУММ(B5:B15);"") Если число равно 100, вычисляется сумма в диапазоне B5:B15. В противном случае возвращается пустая текстовая строка ("") ()

Пример 2

A B Фактические расходы Предполагаемые расходы             Формула Описание (результат) =ЕСЛИ(A2>B2;"Превышение бюджета";"ОК") Проверяет первую строку на превышение бюджета (Результат: Превышение бюджета) =ЕСЛИ(A3>B3;"Превышение бюджета";"ОК") Проверяет вторую строку на превышение бюджета (Результат: ОК)

Функция И

Скрыть все

Возвращает значение ИСТИНА, если все аргументы имеют значение ИСТИНА; возвращает значение ЛОЖЬ, если хотя бы один аргумент имеет значение ЛОЖЬ.

Синтаксис

И (логическое_значение1; логическое_значение2;...)

Логическое_значение1, логическое_значение2,... — от 1 до 255 проверяемых условий, которые могут иметь значение либо ИСТИНА, либо ЛОЖЬ.

Замечания

Аргументы должны быть логическими значениями (такими, как ИСТИНА или ЛОЖЬ).

Пример

A Данные     Формула Описание (результат) =И(1<A2; A2<100) Т.к. 50 больше 1 и меньше 100 (ИСТИНА) =ЕСЛИ(И(A3>=1; A3<=100);A3+50;А3) Рассматривает второе число из приведенных выше. Если оно в интервале от 1 до 100, то его значение увеличить на 50, а если число вне интервала, то его значение оставить без изменения. Результат: значение А3 не изменится =ЕСЛИ(И(A2>1; A2<80); A2+10; "Значение вне интервала.") Рассматривает первое число из приведенных выше. Результатом будет: если число больше 1 и меньше 80, то содержимое ячейки А2 увеличится на 10 (A2+10=60) или текстовое сообщение (значение вне интервала), а значение А2 останется прежним.