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

Пятнадцать чисел записаны подряд в ячейках одной строки. Найти количество пар «соседних» чисел, равных нулю.

Решение

1. В связи с отсутствием всех данных, заполняем таблицу исходных данных самостоятельно.

2. Устанавливаем курсор на ячейку А2.

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

4. В открывшемся диалоговом окне выбираем категорию Логические.

5. Из списка функций данного раздела выбираем функцию ЕСЛИ.

6. Нажимаем кнопку ОК окна мастера функций.

7. В качестве параметров функции Лог_выражение вводим И(A1=0;A1=B1); Значение_если_истина вводим 1; Значение_если_ложь вводим 0.

8. После нажатия кнопки ОК в окне функции в ячейке D3 отображается результат. С помощью автозаполнения заполняем диапазон ячеек В2:N2.

9. Устанавливаем курсор на ячейку E4.

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

11. В открывшемся диалоговом окне выбираем категорию Математические.

12. Из списка функций данного раздела выбираем функцию СУММ.

13. Нажимаем кнопку ОК окна мастера функций.

14. В качестве параметров функции вводим А2:N2.

15. После нажатия кнопки ОК в окне функции в ячейке E4 отображается результат.

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

Указание

Задачи 1- 24 решить двумя способами:

• с выводом соответствующих чисел в каких-либо дополнительных ячейках;

• без вывода соответствующих чисел в каких-либо дополнительных ячейках.

Даны 20 чисел. Найти сумму тех из них, которые больше некоторого числа, значение которого указывается в отдельной ячейке.

Решение

1. В связи с отсутствием всех данных, заполняем таблицу исходных данных самостоятельно.

2. Устанавливаем курсор на ячейку В2.

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

4. В открывшемся диалоговом окне выбираем категорию Логические.

5. Из списка функций данного раздела выбираем функцию ЕСЛИ.

6. Нажимаем кнопку ОК окна мастера функций.

7. В качестве параметров функции Лог_выражение вводим A2>D$1; Значение_если_истина вводим A2; Значение_если_ложь вводим "".

8. После нажатия кнопки ОК в ячейке В2 отображается результат.

9. Растягиваем формулу на диапазон ячеек В3:В21.

10. Устанавливаем курсор на ячейку D23.

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

12. В открывшемся диалоговом окне выбираем раздел Математические.

13. Из списка функций данного раздела выбираем функцию СУММ.

14. Нажимаем кнопку ОК окна мастера функций.

15. В качестве параметров функции вводим В2:В31

16. После нажатия кнопки ОК в ячейке D23 отображается результат.

17. Устанавливаем курсор на ячейку D24.

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

19. В открывшемся диалоговом окне выбираем раздел Математические.

20. Из списка функций данного раздела выбираем функцию СУММЕСЛИ.

21. Нажимаем кнопку ОК окна мастера функций.

22. В качестве параметров функции вводим Диапазон: A2:A21; Критерий: ">"&D1.

23. После нажатия кнопки ОК в ячейке D24 отображается результат.

3.5. Определение среднего арифметического значения чисел, удовлетворяющих некоторому условию

Указание

Все задачи данного раздела решить двумя способами:

• с использованием функции СРЗНАЧ;

• без использования этой функции.

Известна масса каждого человека из группы в 20 человек. Людей, имеющих массу более 100 кг, будем условно называть полными. Подготовить лист для определения средней массы полных людей и средней массы остальных людей. Рассмотреть два случая:

• известно, что полные люди в рассматриваемой группе есть;

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

Решение

1 способ

1. В связи с отсутствием всех данных, заполняем таблицу исходных данных самостоятельно.

2. Устанавливаем курсор на ячейку С2.

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

4. В открывшемся диалоговом окне выбираем раздел Логические.

5. Из списка функций данного раздела выбираем функцию ЕСЛИ.

6. Нажимаем кнопку ОК окна мастера функций.

7. В качестве параметров Лог_выражение вводим B2>100; Значение_если_истина вводим В2; Значение_если_ложь вводим "Меньше 100".

8. После нажатия кнопки ОК в ячейке С2 отображается результат.

9. С помощью автозаполнения заполняем диапазон ячеек С3:С21.

10. Устанавливаем курсор в ячейке D23.

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

12. В открывшемся диалоговом окне выбираем раздел Логические.

13. Из списка функций данного раздела выбираем функцию ЕСЛИ.

14. Нажимаем кнопку ОК окна мастера функций.

15. В качестве параметров Лог_выражение вводим ЕОШИБКА(СРЗНАЧ(C2:C21)); Значение_если_истина вводим "Нет полных людей в группе"; Значение_если_ложь вводим СРЗНАЧ(C2:C21).

16. После нажатия кнопки ОК в ячейке D23 отображается результат.

17. Устанавливаем курсор в ячейке D24.

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

19. В открывшемся диалоговом окне выбираем раздел Статистические.

20. Из списка функций данного раздела выбираем функцию CРЗНАЧЕСЛИ.

21. Нажимаем кнопку ОК окна мастера функций.

22. В качестве параметров функции вводим или выделяем Диапазон: C2:C21; условие: "меньше 100"; диапазон_усреднения: В2:В21 .

23. Нажимаем кнопку ОК в окне функции и в ячейке D 34 выводится результат.

2 способ

1. Устанавливаем курсор на ячейку Е23.

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

3. В открывшемся диалоговом окне выбираем раздел Логические.

4. Из списка функций данного раздела выбираем функцию ЕСЛИ.

5. Нажимаем кнопку ОК окна мастера функций.

6. В качестве параметров Лог_выражение вводим ЕОШИБКА(СУММ(C2:C21)/ /СЧЁТ(C2:C21)); Значение_если_истина вводим "Нет полных людей в группе"; Значение_если_ложь вводим СУММ(C2:C21)/СЧЁТ(C2:C21).

7. После нажатия OK в ячейке Е23 отображается результат

8. Устанавливаем курсор на ячейку Е24.

9. Вводим формулу СУММЕСЛИ(C2:C21;"меньше 100";B2:B21)/ /СЧЁТЕСЛИ(C2:C21;"меньше 100")

10. После нажатия OK в ячейке Е24 отображается результат

3.6. Поиск значения, соответствующего некоторому другому значению

Примечание

В данном разделе приведены задачи следующего типа:

1. Имеется таблица с данными вида:

Столбец 1	Столбец 2	…

Необходимо в столбце N найти значение из той же строки столбца M, в которой записано заданное значение, например, по заданному номеру телефона найти фамилию, по названию государства – его столицу и т. п. Принимается, что N <M и данные в столбце M неупорядочены.

2. Имеется таблица с данными вида:

Строка 1
Строка 2
…

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

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

В таблице представлены сведения о расстоянии от Москвы до некоторых городов:

Город	Расстояние
Калуга	115
Брест	1530
…	…
Кременчуг	1040

Подготовить лист для определения:

• города, находящегося в списке под городом Минск;

• расстояния от Москвы до города, находящегося в списке непосредственно под городом Саратов;

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

Принять, что заданных городов в списке может и не быть.

Решение

1. В связи с отсутствием всех данных, заполняем таблицу исходных данных самостоятельно.

2. Устанавливаем курсор на ячейку С12.

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

4. В открывшемся диалоговом окне выбираем раздел Логические.

5. Из списка функций данного раздела выбираем функцию ЕСЛИ.

6. Нажимаем кнопку ОК окна мастера функций.

7. В качестве параметров Лог_выражение вводим ЕОШИБКА(ИНДЕКС(A2:A10;ПОИСКПОЗ("Минск";A2:A10;0)+1)); Значение_если_истина вводим "Города Минск нет в списке"; Значение_если_ложь вводим ИНДЕКС(A2:A10;ПОИСКПОЗ("Минск";A2:A10;0)+1).

8. После нажатия кнопки ОК в ячейке С12 отображается результат.

9. Устанавливаем курсор в ячейке С13.

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

11. В открывшемся диалоговом окне выбираем раздел Логические.

12. Из списка функций данного раздела выбираем функцию ЕСЛИ.

13. Нажимаем кнопку ОК окна мастера функций.

14. В качестве параметров Лог_выражение вводим ЕОШИБКА(ИНДЕКС(A2:B10;ПОИСКПОЗ("Саратов";A2:A10;0)+1;2)); Значение_если_истина вводим "Города Саратов нет в списке"; Значение_если_ложь вводим ИНДЕКС(A2:B10;ПОИСКПОЗ("Саратов";A2:A10;0)+1;2).

15. После нажатия кнопки ОК в ячейке С13 отображается результат.

16. Устанавливаем курсор в ячейке С14

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

18. В открывшемся диалоговом окне выбираем раздел Логические.

19. Из списка функций данного раздела выбираем функцию ЕСЛИ.

20. Нажимаем кнопку ОК окна мастера функций.

21. В качестве параметров Лог_выражение вводим ЕОШИБКА(ИНДЕКС(A2:A10;ПОИСКПОЗ(D1;A2:A10;0)+1)); Значение_если_истина вводим "Указанного города нет в списке"; Значение_если_ложь вводим ИНДЕКС(A2:A10;ПОИСКПОЗ(D1;A2:A10;0)+1).

22. После нажатия кнопки ОК в ячейке С14 отображается результат.

23. Устанавливаем курсор в ячейке D14

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

25. В открывшемся диалоговом окне выбираем раздел Логические.

26. Из списка функций данного раздела выбираем функцию ЕСЛИ.

27. Нажимаем кнопку ОК окна мастера функций.

28. В качестве параметров Лог_выражение вводим ЕОШИБКА(ИНДЕКС(A2:B10;ПОИСКПОЗ(D1;A2:A10;0)+1;2)); Значение_если_истина вводим "Указанного города нет в списке"; Значение_если_ложь вводим ИНДЕКС(A2:B10;ПОИСКПОЗ(D1;A2:A10;0)+1;2).

29. После нажатия кнопки ОК в ячейке D14 отображается результат.