Рекомендации по выполнению задания 19

Таблица содержит большое количество записей и требуют навыков работы с большими массивами данных.

Быстрое перемещение по таблице:

При решении задачи 19 можно использовать функции (см. Приложение).

  1. Если в условии задачи необходимо найти количество чего-либо, то используем функцию СЧЁТЕСЛИ(диапазон; критерий)

Например,

Светлана Сидорова собиралась полететь на каникулах на отдых и записала расписание перелётов из Москвы в разные города в виде таблицы. Ниже приведены первые пять строк таблицы.

 

А

B

C

1

Город

Страна

Примерное время  в пути

2

Абакан

РОССИЯ

4

3

Абу-Даби

ОАЭ

5

4

Адлер

РОССИЯ

2

5

Акаба

ЕГИПЕТ

5

 

Каждая строка таблицы содержит запись об одном маршруте перелёта.

В столбце A записан город назначения, в столбце B – страна назначения; в столбце C – примерное время в пути.

Всего в электронную таблицу были занесены данные по 191 маршруту в алфавитном порядке.

Для какого количества городов расчётное время перелёта составляет более 5 часов? Ответ на этот вопрос запишите в ячейку H2 таблицы.

 

  1. Если в условии задачи необходимо найти процент чего-либо, то используем функции:

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]),

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

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

Например,

Какой процент от всех 6-часовых перелетов составляют 6-часовые перелёты в города России? Ответ на этот вопрос в виде целого числа нужно записать в ячейку H3 таблицы.

=СЧЁТЕСЛИ(С2:С192; 6)

=ЕСЛИ(В2="РОССИЯ"; 1; 0)

=СУММЕСЛИ(С2:С192; 6; D2:D192)

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

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

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

Например,

В электронную таблицу занесли данные о калорийности продуктов. Ниже приведены первые пять строк таблицы.

А

В

С

D

Е

1

Продукт

Жиры, г

Белки, г

Углеводы, г

Калорийность, Ккал

2

Арахис

45,2

26,3

9,9

552

3

Арахис жареный

52

26

13,4

626

4

Горох отварной

0,8

10,5

20,4

130

5

Горошек зелёный

0,2

5

8,3

55

Всего в электронную таблицу были занесены данные по 1000 продуктам.

Сколько продуктов в таблице содержат меньше 25 г жиров и меньше 25 г углеводов? Запишите число этих продуктов в ячейку H2 таблицы.

                  =ЕСЛИ(И(В2<25;D2<25);1;0)

  1. Если в задаче необходимо найти среднее чего-либо, то используем функции:

      СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]),

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

Например,

Какова средняя калорийность продуктов с содержанием белков менее 1 г? Ответ на этот вопрос запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

=СЧЁТЕСЛИ(C2:C1001;"<1")

                =СУММЕСЛИ(C2:C1001;"<1";E2:E1001)

                        =J3/J2

                или более подробно:

=СУММЕСЛИ(C2:C1001;"<1";E2:E1001)/СЧЁТЕСЛИ(C2:C1001;"<1")

Некоторые функции Exsel

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

Функция ЕСЛИ возвращает одно значение, если указанное условие дает в результате значение ИСТИНА, и другое значение, если условие дает в результате значение ЛОЖЬ. Например, формула =ЕСЛИ(A1>10;"Больше 10";"10 или меньше") возвращает строку "Больше 10", если значение в ячейке A1 больше 10, и "10 или меньше", если оно меньше или равно 10.

Аргументы функции ЕСЛИ описаны ниже.

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

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

Функция И имеет аргументы:

 

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

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

Функция ИЛИ имеет аргументы:

Логическое_значение1, логическое значение2...    

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

 

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

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

Замечания

Если аргумент «логическое_значение» имеет значение ЛОЖЬ, функция НЕ возвращает значение «Да»; если он имеет значение ИСТИНА, функция НЕ возвращает значение «Нет».

СРЗНАЧ(число1; [число2]; …)

Возвращает среднее значение (среднее арифметическое) аргументов. Например, если диапазон A1:A20 содержит числа, формула =СРЗНАЧ(A1:A20) возвращает среднее значение этих чисел.

Аргументы функции СРЗНАЧ описаны ниже.

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

Функция СЧЁТЕСЛИ подсчитывает количество ячеек в диапазоне, которые соответствуют одному указанному пользователем критерию. Например, можно подсчитать количество всех ячеек, которые начинаются с определенной буквы или содержат числа, большие или меньшие указанного значения. Предположим, например, что есть лист со списком задач в столбце A, а имя пользователя, ответственного за каждую из задач, указано в столбце B. Для подсчета количества повторений имени ответственного в столбце B можно использовать функцию СЧЁТЕСЛИ. Это позволяет определить количество задач, назначенных этому пользователю. Пример:

=СЧЁТЕСЛИ(B2:B25;"Валентин")

Аргументы функции СЧЁТЕСЛИ описаны ниже.

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

Число1, число2, ...      — от 1 до 30 чисел, среди которых требуется найти наибольшее.

Заметки

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

Число1, число2, ...     — от 1 до 30 чисел, среди которых требуется найти наименьшее.

Заметки

СУММ(число1;[число2]; ...)

Функция СУММ вычисляет сумму всех чисел, указанных в качестве аргументов. Каждый аргумент может быть диапазоном, ссылкой на ячейку, массивом, константой, формулой или результатом другой функции. Например, функция СУММ(A1:A5) вычисляет сумму всех чисел в ячейках от A1 до A5. Другой пример: функция СУММ(A1, A3, A5) вычисляет сумму чисел в ячейках A1, A3 и A5.

Аргументы функции СУММ указаны ниже.

Замечания

СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])


Функция
СУММЕСЛИ используется, если необходимо просуммировать значения диапазона, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать указанную ниже формулу.

=СУММЕСЛИ(B2:B25,">5")

В данном примере на соответствие критерию проверяются суммируемые значения. При необходимости условие можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван".

Аргументы функции СУММЕСЛИ указаны ниже.

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

 Примечания 

В аргументе условие можно использовать подстановочные знаки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует одному любому символу, а звездочка — любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).