Статистические функции (Счетчики)

Читайте также:

► СЧЁТЕСЛИ (диапазон; критерий)

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

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

Критерий — критерий в форме числа, выражения или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, ">32", "яблоки".

Подстановочные знаки: (?) и (*)

? – любой символ

* – любая последовательность символов

A	B	C	D	E

	яблоки			Сок (ябл.)

Яблоки				Мулти-Сок
		ЯБЛОКИ
Сок (апл.)
				Сок
Яблоки

=СЧЕТЕСЛИ(A2:E8; "яблоки") → 4

=СЧЕТЕСЛИ(A2:E8; "груши") → 0

=СЧЕТЕСЛИ(A2:E8; ">30") → 3

=СЧЕТЕСЛИ(A2:E8; " * Сок * ") → 4

______________________________________________________________

СЧЕТЕСЛИМН (диапазон1; критерий1; диапазон2; критерий2; …)

Число пар до 127

СЧЕТЕСЛИМН ($B$2:$B$104; E2; $C$2:$C$104; F2)

A	B	C	D	E	F	H
Фамилия	Курс	Группа		Курс	Группа	Количество
НИКОНОВ
СМЕЛОВ
СТОЕВ
…………………..	….	…….
ИВЧЕНКОВ
ПРИТУЛА

Примеры использования подстановочных символов:

=СЧЕТЕСЛИ(A2:A7,"*ки") Количество ячеек с окончанием " ки "

=СЧЕТЕСЛИ(A2:A7,"????ки") Количество ячеек с окончанием " ки ", содержащих 6 букв

=СЧЕТЕСЛИ(A2:A7,"*") Количество ячеек, содержащих любой текст

=СЧЕТЕСЛИ(A2:A7,"<>"& R10)

=СЧЕТЕСЛИ(A2:A7,"") Количество ячеек, не содержащих текста

► СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

Суммирует ячейки, удовлетворяющие заданному условию.

Диапазон — диапазон анализируемых ячеек.

Критерий — критерий в форме числа, выражения или текста, определяющего суммируемые ячейки. Например, критерий может быть выражен как 32, ">32", "яблоки".

Подстановочные знаки: (?) и (*)

? – любой символ

* – любая последовательность символов

Диапазон_суммирования — фактические ячейки для суммирования.

· Ячейки в «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.

· Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».

A	B	C	D	E

	№	Товар	Вес	Стоим.
		Груши
		Яблоки
		Слива
		Яблоки
		Вишня
		Слива
		Черешня
		Яблоки

=СУММЕСЛИ(С3:C10; "яблоки"; E3:E10) → 3950

=СУММЕСЛИ(С3:C10; "СЛИВА";E3:E10) → 1300

=СУММЕСЛИ(С3:C10; "яблоки"; D3:D10) → 75

____________________________________________________________________

СуммЕСЛИМН (Диап. Суммирования; диап.1; крит.1; диап.2; крит.2; …)

Число пар до 127

СуммЕСЛИМН ($F$3:$F$10; $C$3:$C$10; “яблоки”; $D$3:$D$10; 1) = 3150

A	B	C	D	E	F

	№	Товар	Магазин	Вес	Стоим.
		Груши
		Яблоки
		Слива
		Яблоки
		Вишня
		Слива
		Черешня
		Яблоки

Самостоятельная 3. (файл Excel)

ЗАДАНИЕ1: СчетЕсли(), СуммЕсли()

1. Выставить баллы в диапазоне 0-30

2. Определить средний балл каждой группы

A	B	C	D	E	F	G	H

№ п/п	Фамилия	Группа	Балл (0-30)			Группа	Средний Балл
	Акопова Радмила	108э(2)				101м(2)
	Аманкаева Гиляна	108э(2)				101э(2)
	Ананьева Ольга	веч				106э(2)
	Балабанова Ольга	веч				108э(2)
	Банцекин Иван	101э(2)				109э(2)
	Батурова Лана	109э(2)				111э(2)
	Бобровский Дмитрий	101м(2)				веч
	Богомолов Игорь	101э(2)
	Бонева Елена	111э(2)
	Борисов Илья	109э(2)
	Буслакова Ольга	веч
	Волкова Ольга	111э(2)

ЗАДАНИЕ2:. (файл Excel) СчетЕслиМН(), СуммЕслиМН()

ТОВАР	ПРОДАНО (количество)	ПРОДАНО (сумма)
Маг. 1	Маг. 2	Маг. 3	Маг. 1	Маг. 2	Маг. 3
Газовые плиты
Электрические плиты
Стиральные машины
Холодильники
Морозильные камеры
Пылесосы
Утюги
Кофеварки
Кофемолки
Микроволновые печи
Соковыжималки
Электрочайники

ЗАНЯТИЕ 5

Функции поиска

Функция ИНДЕКС

ИНДЕКС (Диапазон; Nстр; Nстл)

Возвращает значение элемента таблицы заданного

номером строки (Nстр) и номером столбца (Nстл)

Диапазон – таблица, столбец или строка

A	B	C	D	E	F	G	H


		Фамилия	Оклад	Телефон	Дата рождения	Место рождения
		ЛИСИЧКИН			22.07.1989	г.Полтава
		СВЕШНИКОВ			14.05.1989	г. Дедовск
		ХАРИН			24.08.1989	г. Смоленск
		КИТАШОВ			15.12.1988	г. Москва
		РУБАС			13.04.1988	г. Алма-Ата
		ЧЕМЕРИЦКИЙ	77700		30.05.1989	г. Завитинск
		КОЛЕСНИКОВ			17.10.1988	г. Москва

ИНДЕКС (C4:G10; 6; 2) → 77700

Когда столбец ИНДЕКС (F4:F10; 4) → 15.12.1988

Когда строка ИНДЕКС ($C$7:$G$7; 5) → г. Москва

Функция ПОИСКПОЗ

ПОИСКПОЗ (Что искать; Где искать; [Тип]) (по умолчании Тип =1)

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

Что искать – искомое значение.

Где искать – строка или столбец

Тип – вид поиска

A	B	C	D	E	F	G	H


		Фамилия	Оклад	Телефон	Дата рождения	Место рождения
		ЛИСИЧКИН			22.07.1989	г.Полтава
		СВЕШНИКОВ			14.05.1989	г. Дедовск
		ХАРИН			24.08.1989	г. Смоленск
		КИТАШОВ			15.12.1988	г. Москва
		РУБАС			13.04.1988	г. Алма-Ата
		ЧЕМЕРИЦКИЙ			30.05.1989	г. Завитинск
		КОЛЕСНИКОВ			17.10.1988	г. Москва

Тип = 0 - Возвращает позицию точного значения (сортировка не обязательна)

Тип = 1 - Возвращает позицию точного или ближайшего меньшего значения (сортировка в порядке возрастания)

Тип = -1 - Возвращает позицию точного или ближайшего большего значения (сортировка в порядке убывания)

ПОИСКПОЗ (“Киташов”; $C$4:$C$10; 0) → 4 (поиск фамилии по столбцу)

ПОИСКПОЗ (“Киташов”; $C$7:$G$7; 0) → 1 (поиск фамилии по строке)

ПОИСКПОЗ (1421423; $E$4:$E$10; 0) → 2 (поиск телефона по столбцу)

ПРИМЕР совместного использования функций ИНДЕКС и ПОИСКПОЗ

A	B	C	D	E	F	G	H


		Фамилия	Оклад	Телефон	Дата рождения	Место рождения
		ЛИСИЧКИН			22.07.1989	г.Полтава
		СВЕШНИКОВ			14.05.1989	г. Дедовск
		ХАРИН			24.08.1989	г. Смоленск
		КИТАШОВ			15.12.1988	г. Москва
		РУБАС			13.04.1988	г. Алма-Ата
		ЧЕМЕРИЦКИЙ			30.05.1989	г. Завитинск
		КОЛЕСНИКОВ			17.10.1988	г. Москва

Задача 1: Определить дату рождения Харина.

=ИНДЕКС ( F4:F10; ПОИСКПОЗ("Харин"; C4:C10; 0) ) → 24.08.1989

------------------------------------------------------------------

Задача 2: Определить владельца телефона 4511973.

=ИНДЕКС ( C4:C10; ПОИСКПОЗ (4511973; E4:E10; 0 )) → ЧЕМЕРИЦКИЙ

6
Функция ВПР

ВПР (искомое_значение; таблица; №стл; интервальный_просмотр)

Ищет значение в крайнем левом столбце таблицы и возвращает значение в той же строке из указанного столбца таблицы.

Искомое_значение — это значение, которое должно быть найдено в первом столбце массива. Искомое_значение может быть значением, ссылкой или текстовой строкой.

Таблица — таблица с информацией, в которой ищутся данные. Можно использовать ссылку на интервал или имя интервала, например БазаДанных или Список.

· Если интервальный_просмотр имеет значение ИСТИНА, то значения в первом столбце аргумента «таблица» должны быть расположены в возрастающем порядке:..., -2, -1, 0, 1, 2,..., A-Z, ЛОЖЬ, ИСТИНА; в противном случае функция ВПР может выдать неправильный результат. Если «интервальный_просмотр» имеет значение ЛОЖЬ, то «таблица» не обязана быть отсортированной.

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

· Текстовые строки сравниваются без учета регистра букв.

№стл — это номер столбца в массиве «таблица», в котором должно быть найдено соответствующее значение. Если «№стл» равен 1, то возвращается значение из первого столбца аргумента «таблица»; если «№стл» равен 2, то возвращается значение из второго столбца аргумента «таблица» и так далее. Если «номер_столбца» меньше 1, то функция ВПР возвращает значение ошибки #ЗНАЧ!; если «№стл» больше, чем количество столбцов в аргументе «таблица», то функция ВПР возвращает значение ошибки #ССЫЛ!.

Интервальный_просмотр — это логическое значение, которое определяет, нужно ли, чтобы ВПР искала точное или приближенное соответствие. Если этот аргумент имеет значение ИСТИНА или опущен, то возвращается приблизительно соответствующее значение; другими словами, если точное соответствие не найдено, то возвращается наибольшее значение, которое меньше, чем искомое значение. Если этот аргумент имеет значение ЛОЖЬ, то функция ВПР ищет точное соответствие. Если таковое не найдено, то возвращается значение ошибки #Н/Д.

= ВПР (Искомое значение; таблица; №стл; ЛОЖЬ)

ПРИМЕРЫ использования функции ВПР

A	B	C	D	E	F	G	H


	N	Фамилия	Оклад	Телефон	Дата рождения	Место рождения
		ЛИСИЧКИН			22.07.1989	г.Полтава
		СВЕШНИКОВ			14.05.1989	г. Дедовск
		ХАРИН			24.08.1989	г. Смоленск
		КИТАШОВ			15.12.1988	г. Москва

Задача: Определить дату рождения Харина.

=ИНДЕКС(F4:F10; ПОИСКПОЗ("Харин";C4:C10; 0)) → 24.08.1989

=ВПР(Искомое значение; таблица; №стл; ЛОЖЬ)

=ВПР("Харин"; $C$4:$G$10; 4; Ложь) → 24.08.1989

A	B	C	D	E	F	G	H


		Фамилия	Оклад	Телефон	Дата рождения	Место рождения
		ЛИСИЧКИН			22.07.1989	г.Полтава
		СВЕШНИКОВ			14.05.1989	г. Дедовск
		ХАРИН			24.08.1989	г. Смоленск
		КИТАШОВ			15.12.1988	г. Москва
		РУБАС			13.04.1988	г. Алма-Ата
		ЧЕМЕРИЦКИЙ			30.05.1989	г. Завитинск
		КОЛЕСНИКОВ			17.10.1988	г. Москва

Задача 2: Определить владельца телефона 4511973.

=ИНДЕКС(C4:C10; ПОИСКПОЗ(4511973; E4:E10; 0)) → ЧЕМЕРИЦКИЙ

=ВПР(Искомое значение; таблица; №стл; ЛОЖЬ)

= ВПР (4511973; B4:C10; 2; Ложь) → ЧЕМЕРИЦКИЙ

Дата добавления: 2015-10-23; просмотров: 136 | Нарушение авторских прав

<== предыдущая страница	\|	следующая страница ==>
Преобразование типов.	\|	Применение функции ВПР() когда точного соответствия поиска нет

mybiblioteka.su - 2015-2024 год. (0.034 сек.)