СЧЕТЕСЛИ(интервал; критерий)

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

Описание аргументов аналогично функции СУММЕСЛИ.

 

Таблица 2.10

 

Информация о поступающих в вуз

 

	А	В	С
	Ф.И.О.	Школа №	Ср. оценка аттестата
	Иванов И. И.		3,8
	Федоров П.Е.
	Персик И.Н.		4,6
	Семенова Е.Н.

 

Подсчитаем количество выпускников школы №20 (табл. 2.10).

=СЧЕТЕСЛИ(А2:В5;20) равняется 2

Подсчитаем количество выпускников с средним баллом больше 4.

=СЧЕТЕСЛИ(С2:С5;">4") равняется 2

Подсчитаем количество выпускников с средним баллом 3.

=СЧЕТЕСЛИ(С2:С5;"=3") равняется 0

 

СЛЧИС()

Возвращает случайное число между 0 и 1. Новое случайное число возвращается каждый раз, когда рабочий лист вычисляется.

Замечания:

• Чтобы получить случайное вещественное число между А и В, можно использовать следующую формулу: =СЛЧИС()*(b-а)+а

• Если Вы хотите использовать функцию СЛЧИС для генерации случайного числа, но не хотите, чтобы это число менялось каждый раз при вычислении значения ячейки, то Вы можете ввести =СЛЧИС () в строку формул и нажать F9 для фиксации значения случайного числа.

Пример:

=СЛЧИС()*100

Для генерации случайного числа в пределах от 0 до 100.

 

ОКРУГЛ(число; число разрядов)

Округляет число до указанного количества десятичных разрядов.

Число - это округляемое число или аргумент

Число_разрядов- это количество десятичных разрядов, до которого нужно округлить число.

Пример:

=ОКРУГЛ(2,15:1) равняется 2.2

=ОКРУГЛ(2.15;0) равняется 2

=ОКРУГЛ(В1;0) равняется 2, если в ячейке В1 число 2,15

 

ЦЕЛОЕ(число)

Округляет число до ближайшего меньшего целого.

Число - это округляемый аргумент.

Пример:

=ЦЕЛОЕ(2,78) равняется 2

 

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

МАКС(число1;число2;...)

МИН(число1;число2;...)

Возвращает максимальное (минимальное) значение из списка аргументов.

Число1, Число2,... –это от 1 до 30 аргументов, среди которых ищется максимальное (минимальное) значение.

• Аргументами могут быть числа, логические значения и текстовые представления чисел. Аргументы, которые являются текстами не преобразуемыми в числа, вызывают ошибки.

• Если аргумент является массивом или ссылкой, то учитываются только числа. Пустые ячейки, логические значения, тексты и значения ошибок в массиве или ссылке игнорируются.

• Если аргументы не содержат чисел, то функции МАКС (МИН) возвращает ноль.

Таблица 2.11

 

Пример таблицы вида

 

	А	В
	"5"	ИСТИНА
3

 

=МИН(А1:АЗ) результат 10

=МИН(А1:ВЗ) результат 2

=МИН(ИСТИНА;4;"3") результат 1

=МАКС(А1:ВЗ;100) результат 100

 

Логические функции

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

Выполняет логическую проверку.

Лог_выражение –это любое значение или выражение, которое при вычислении дает значение ИСТИНА или ЛОЖЬ.

Знач_если_истина –это значение, которое возвращается, если лог_выражение имеет значение ИСТИНА,

Знач_если_ложь –это значение, которое возвращается, если лог_выражение имеет значение ЛОЖЬ.

Пример №1: Рассмотрим табл. 2.10

Подсчитаем количество выпускников школы №20. Запишем формулу =ЕСЛИ(В2=20;1;0) и ячейку D2, затем скопируйте формулу на интервал D3:D5, (па против каждой записи будут стоять.0 - не выпускник школы №20 или 1 - выпускник школы №20) и в ячейке D6 запишите формулу =СУММ(D2:D5). Ответом служит число 2, стоящее в ячейке D6.

Пример №2: Рассмотрим табл. 2.10

Подсчитаем количество выпускников школы №20, имеющих средний балл аттестата больше 4. Запишем формулу

=ЕСЛИ(В2=20;ЕСЛИ(С2>4;1;0);0) в ячейку Е2, затем скопируйте формулу на интервал ЕЗ:Е5, (на против каждой записи будут стоять 0 - не выпускник школы №20 или 1 - выпускник школы №20) и в ячейке D6 запишите формулу =СУММ(Е2:Е5). Ответом служит число 1, стоящее в ячейке Е6.

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

 

И(логическое1;логическое2;...)

ИЛИ (логическое1;логическое2;...)

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

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

•Аргументы должны быть логическими значениями, массивами или ссылками которые содержат логические значения. Если указанный интервал не содержит логических значений, то И возвращает значение ошибки #ЗНАЧ!

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

Пример №1.

=И(ИСТИНА;ИСТИНА) равняется ИСТИНА

=И(ИСТИНА;ЛОЖЬ) равняется ЛОЖЬ

Если интервал В1:ВЗ содержит значения ИСТИНА, ЛОЖЬ, ИСТИНА, то =И(В1:ВЗ) равняется ЛОЖЬ. Если в ячейке В4 содержится число в пределах от 50 до 70, то =И(В4>50;ВЗ<70) результат ИСТИНА.

Проверим, если в ячейке А2 записано "яблоки", а в ячейке В2 их стоимость меньше 6, выведем их стоимость в ячейке С2, иначе "таких яблок нет". Запишем формулу в ячейке С2:

=ЕСЛИ(И(А2="яблоки";В2<6);В2;"таких яблок нет")

Пример №2: Запишем формулу в ячейку СЗ:

=ЕСЛИ(ИЛИ(А2="яблоки";А2="груши");В2;"это другой продукт")

Если в ячейке А2 записано "яблоки" или "груши", то в СЗ будет выведена цена из ячейки В2, иначе фраза "это другой продукт".

 

НЕ(логическое_значение)

Меняет на противоположное логическое значение своего аргумента.

Логическое_значение –это значение или выражение, которое при вычислении дает ИСТИНА или ЛОЖЬ.

Пример:

=НЕ(ЛОЖЬ) равняется ИСТИНА

 

Ссылки

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

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

Таблииа_массив –область рабочего листа с информацией для поиска и вывода результата.

Номер_столбца –номер столбца из которого берется значение, относительно столбца в котором производится поиск,

Диапазон_просмотра –(данный параметр необязательный, по умолчанию принимает значение ИСТИНА) признак, определяющий точность сопоставления искомого значения с значениями в первом столбце таблицы массива. Параметр имеет два значения: ИСТИНА –сопоставление приближенное; ЛОЖЬ – сопоставление точное.

Пример.№1: Рассмотрим табл. 2.15

Найдем номер школы, которую закончил ученик по фамилии Персик И.В.

=ВПР("Персик И.Н.";А2:В5;2;ложь)

Ищем фамилию Персик И.Н. в первом столбце интервала А2:В5, результат берем из второго столбца интервала А2:В5. Сопоставление фамилий точное.

 

Табличные формулы

Пример №1: Рассмотрим табл. 2.10

Подсчитаем количество выпускников школы №20

{=СУММ(ЕСЛИ(В2:В5=20;1))}

При наборе табличной формулы руководствуйтесь правилами:

1. наберите формулу без фигурных скобок и не спешите нажимать Enter;

2. нажмите комбинацию клавиш < Ctrl > + <Shift> + <Enter>. Вы должны удерживать нажатыми клавиши <Ctrl> и <Shift> в тот момент когда нажимаете <Enter>, Вы получите фигурные скобки иначе формула будет введена как обычная формула.

Пример №2: Рассмотрим табл. 2.10

Подсчитаем количество выпускников школы №20, имеющих среднюю оценку аттестата >4 баллов.

{=СУММ(ЕСЛИ(В2:В5=20;ЕСЛИ(С2:C5>4;1;0);0))}

Пример №3: Имеется таблица вида:

 

Таблица 2.12

 

Пример таблицы вида

 

№ Отделения банка	Валюта	Объем продажи
	$
	DM
	$
	DM
	DM

 

Подсчитаем объем продаж $ по всем отделениям банков. {=СУММ(ЕСЛИ(В3:В7="$";С3:С7;0))}

Выражение А3:А7="$" имеет значение ИСТИНА для каждого вхождения символа $ в указанном интервале. При значении ИСТИНА складываются значения ячеек из интервала В3:В7. Результат функции равен 130.

Подсчитаем объем продаж DM по всем отделениям банков. {=СУММ(ЕСЛИ(В3:В7=" DM ";С3:С7;0))}

Выражение А3:А7="DM" имеет значение ИСТИНА для каждого вхождения символа $ в указанном интервале. При значении ИСТИНА складываются значения ячеек из интервала В3:В7. Результат функции равен 420.

Варианты заданий

Вариант № 1

	А	В	С	D
	Отчетная ведомость
	Изделие	Стоимость (тыс.)	Цвет	Кол-во
	стол		черный
	стул		белый
...
	шкаф		белый

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, С заполнить произвольно.

1. Стоимость одного изделия вычислить по формуле: случайное число из диапазона 50..700.

2. Количество изделий вычислить по формуле: (стоимость изделия) - (случайное число в пределах от 1 до стоимости изделия).

3. Двумя различными способами подсчитать, сколько видов изделий «стол» выпущено.

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

5. В столбце «Стоимость» используйте денежный формат.

6. Записать ЭТ в ваш рабочий каталог.

 

Вариант № 2

 

	А	В	С	D	E	F
	СПРАВОЧНИК
	Площадь. кв.м	Адрес	Стоимость квартиры
	улица	дом	№ кв.	этаж
		Мира
…
		Ленина

 

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, С, Е заполнить произвольно. Номер квартиры вычисляется по формуле: случайное число из диапазона 1..150.

1. Стоимость квартиры вычисляется по формуле: ((площадь квартиры)*(случайное число из диапазона 1..7))-(этаж)*(случайное число из диапазона 1..5)).

2. Двумя различными способами подсчитать сколько квартир с номером 33 на улице Мира и площадью >50 кв.м.

3. Двумя различными способами подсчитать общую площадь квартир на улицах Ленина и Мира.

4. В столбце «Стоимость квартиры» используйте денежный формат «млн.»

5. Записать ЭТ в ваш рабочий каталог.

Вариант № 3

	А	В	С	D	Е
	СПРАВОЧНИК
	мин. з/п
	Ф.И.О.	День недели	Смена, 1/2	Коэф. 1/1.5	Заработная плата
	Иванов И. Н.	Суббота	2
...
	Петров М. Л.	четверг

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, С заполнить произвольно.

1. Расставить коэффициент в столбце D по правилу: человек, работающий в первую смену имеет коэффициент 1; человек, работающий во вторую смену имеет коэффициент 1,5.

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

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

4. В столбце «Заработная плата» используйте денежный формат «тыс».

5. Записать ЭТ в ваш рабочий каталог.

Вариант № 4

 

	А	В	С	D	Е
	Наличие товара на складе
	Номер склада	Количество товара	Цена ед. товара	Стоимость товара, тыс.	Площадь склада
…

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, С, Е заполнить произвольно. Количество товара вычисляется но формуле: случайное число из диапазона 100..250.

1. Стоимость товара вычислять по формуле: Количество товара * Цену ед. товара.

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

3. Двумя различными способами подсчитать количество товара, стоимость которого превышает 500 тыс.

4. В столбце «Количество товара» используйте формат «тонн».

5. Записать ЭТ в ваш рабочий каталог.

Вариант № 5

 

	А	В	С	D	Е
	Физические показатели группы.
	Ф. И. О.	Группа	Пол, м/ж	Рост, см	Вес. кг.
	Иванов В. В.	21-1	м
...
	Сидоров А. П.	73-2	м

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, С заполнить произвольно. Колонку "Рост" заполнить по формуле: случайное число из диапазона 150..250.

1. Колонку «Вес» заполнить по формуле: случайное число из диапазона 45..150.

2. Двумя различными способами подсчитать средний рост мужчин в группе X.

3. Двумя различными способами подсчитать процентное соотношение мужчин и женщин в группе X.

4. В столбце «Вес» используйте формат «кг».

5. Записать ЭТ в ваш рабочий каталог.

 

Вариант № 6

 

	А	В	С	D	E
	Расписание
	Номер рейса	Пункт отправления	Пункт назначения	Время
	6.30	10.10
…
		Красноярск	Минск	5.20	7.54

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, С заполнить произвольно.

1. Время вылета и время прилета вычисляется по формуле: случайное число из диапазона 0.00…24.00

2. Двумя различными способами подсчитать сколько утренних рейсов (4.00-11.00) вылетает в Москву.

3. Двумя различными способами подсчитать количество рейсов вылетающих в Норильск.

4. В столбце «Время» используйте формат «час».

5. Записать ЭТ в ваш рабочий каталог.

 

Вариант № 7

 

	А	В	С	D
	СПРАВОЧНИК
	Автор	Название книги	Год издания	Тираж'
	Симонович	Всё об internet
…
…
	Том Сван	Windows форматы файлов

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, D заполнить произвольно.

1. Год издания вычисляется по формуле: случайное число из диапазона 1800...2005.

2. Двумя различными способами подсчитать сколько раз издавалась книга Y автора X.

3. Двумя различными способами подсчитать каков общий тираж книги Y автора X.

4. В столбце «Год издания» используйте формат «год».

5. Записать ЭТ в ваш рабочий каталог.

Вариант № 8

	А	В	С	D
	СТАТИСТИКА
	Город	Месяц	Ср. температура	Кол. осадков
	Москва	Январь	-15.4	24,1
…
	Киев	Март	10,2

 

Требуется: Создать и заполнить ЭТ (не менее 20 записей), колонки А, В, С заполнить произвольно.

1. Количество осадков вычисляется по формуле: случайное число из диапазона 10..50.

2. Двумя различными способами подсчитать среднюю температуру в городе X за два месяца Январь. Февраль.

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

4. В столбце «Ср. температура» используйте формат «⁰С».

5. Записать ЭТ в ваш рабочий каталог.

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