Файл: Лекция Электронная таблица Excel, основные возможности 3 1 Мастер функций MicrosoftExcel, простые вычисления 3.docx

Рассчитаем по формуле:

=СУММЕСЛИ(С1:С9; ">100"

Получается ответ: 966.

Функция СЧЁТСЛИ – статистическая функция, которая определяет, сколько строчек диапазона удовлетворяет условию (критерию).

Общий вид:

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

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

1 В качестве диапазона используются ячейки только одного столбца.

2 Критерий – только одно условие, которое может быть текстом или числом.
Пример (таб. 1).

Необходимо определить: сколько раз в таблице упоминается транспортное средство поезд.

Рассчитаем по формуле:

=СЧЁТЕСЛИ(A1:A9; "Поезд")

Ответ: 2.
Контрольные вопросы

1. 
   Что понимают под функцией в Excel?
   
2. 
   Перечислите основные категории функций в Excel.
   
3. 
   В каких случаях следует использовать абсолютную, а в каких относительную адресацию?
   
4. 
   Как пользоваться справочной информацией Мастера функций?
   
5. 
   Как записываются вложенные функции с помощью Мастера функций?
   
6. 
   Какие логические функции Вы знаете?
   
7. 
   Перечислите правила записи функций.
   

---

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

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

В MicrosoftExcel существует только 6 логических функций.
Функция ЕСЛИ – логическая функция, которая возвращает значение варажение1, если условие истинно, или значение выражение2, если условие ложно.

Общий вид: =ЕСЛИ(условие; выражение1; выражение2)
Пример (таб. 1).

Пусть в клетке A11 находится формула:
=ЕСЛИ(C3<100;С3*5;C3:5)
Ответ: 25

Если число в ячейке C3 меньше 100, то в ячейку A11 занесется 25 (результат вычисления C3*5), иначе в ячейке A11 будет 1 (результат вычисления 5*C3:5).

Функция И – логическая функция проверки выполнимости условий.

Общий вид: =И(условие1; условие2;…)
Работает так: если все логические выражения (условия) верны (истинны), то и результат - истина, иначе - ложь.
Например: =И(А3=”Автомобиль ”;D3<100)

Данная формула выдаст значение ИСТИНА, т.к. в А3 введен текст «Автомобиль», а в D3 = 5. Иначе было бы выдано значение ЛОЖЬ.

2.2 Вложенные функции

Функцию И можно использовать как функцию, вложенную в ЕСЛИ.
Пример

Пусть в ячейке Е3 записана формула:

=ЕСЛИ(И(А3=”Автомобиль”;C3>100);B3;C3).

Если содержимое клетки А3=”Автомобиль”и C3>100, то в клетку Е3 запишется содержимое клетки В3, иначе в клетку Е3 запишется содержимое клетки С3.

Ответ:в E3 будет число 5, т.к значение И(А3=”Автомобиль”;C3>100)= ложь;
Функция ИЛИ – логическая функция проверки выполнимости условий.

Общий вид:

=ИЛИ(условие1; условие2;…)

Работает так: если хотя бы одно логическое выражение (условие) истинно, то результат - истина, иначе - ложь.
Пример

Проверим содержимое таблицы, определив, введен ли в ячейку А3 текст «Автомобиль» или в ячейку D3 – некоторое число, большее 100.

Проверит это формула: =ИЛИ(А3=”Автомобиль”;С3>100)

Данная формула выдаст значение ИСТИНА, т.к в ячейке А3 введен текст «Автомобиль», не смотря на то что в ячейке С3 –число < 100. Значение ЛОЖЬбудет выданов случае если оба условия будут ЛОЖЬ

Функцию ИЛИ можно использовать как функцию, вложенную в ЕСЛИ.

Пример

Пусть в клетке Е3 записана формула:

=ЕСЛИ(ИЛИ(А3=”Автомобиль”;С3>100);B3;C3)

Если ИЛИ(А3=”Автомобиль”;С3>100)=ИСТИНА в клетку Е3 запишется содержимое ячейки В3, иначе в ячейку Е3 запишется содержимое ячейки С3.
Функция НЕ – логическая функция отрицания условий.

Общий вид:

=НЕ(условие)

Работает так: если логическое выражение (условие) истинно

---

, то результат - ложь, иначе - истина.
Например: =НЕ(А3<>”Автомобиль”). Данная формула выдаст значение ЛОЖЬ, т.к. ячейке А3 введен текст «Автомобиль». Иначе будет выдано значениеИСТИНА.
Функцию НЕ можно использовать как функцию, вложенную в ЕСЛИ.
Функции И, ИЛИ, НЕ можно вкладывать одну в другую, проверяя тем самым правильность сложных условий.

Результат работы такой формулы – ИСТИНА или ЛОЖЬ. Поэтому такие сложные условия можно использовать в качестве логических условий функции ЕСЛИ.
Например (таблица 1).

Определить: находятся ли в третьей строке таблицы данные о черном автомобиле, а в четвертой строке – о самолете с числом пассажиров больше 100.

Рассчитаем по формуле:
=ИЛИ(И(A3=”Автомобиль”;B3=”Черный”);И(A4=”Самолет”;С4>100))
Данная формула проверит содержимое таблицы, содержащей наименование транспортного средства, его цвет и число пассажиров. Первое условие у нас не выполняется, т.к. автомобиль в строке 3 белого цвета, а второе условие выполняется, т.к. в строке 4 – самолет с 250 пассажирами. Следовательно, формула выдаст значение ИСТИНА потому, что нам требуется выполнение хотя одного из условий задания.
Необходимо в столбце D таблицы 1 показать цвет поезда или трамвая.

Для этого в ячейку D2 введем формулу:

=ЕСЛИ(ИЛИ(A2="Поезд";A2="Трамвай");B2;" ")

После это скопируем ее в ячейки D3:D9.

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

2.3 Расчеты с использованием функций баз данных

В MicrosoftExcel имеется 12 функций, используемых для анализа данных из списков или баз данных. Каждая из этих функций, которые из соображений совместимости имеют обобщенное название БДФункция, использует три аргумента: база_данных, поле и критерий.

Общий вид: БДФункция(база_данных; поле; критерий), где

база_данных — это интервал ячеек, формирующих список или базу данных;

поле - название столбца (поля базы данных), используемый функцией для расчетов;

критерий — это ссылка на интервал ячеек, задающих условия для функции. Функция возвращает данные из списка, которые удовлетворяют условиям, определенным диапазоном критериев. Диапазон критериев включает копию названия столбца в списке

---

, для которого выполняется подведение итогов.
Функция БСЧЁТ – функция баз данных, определяющая количество чисел в столбце, определенном параметром поле, в базе_данных, удовлетворяющих критерию.

Общий вид: БСЧЁТ(база_данных; поле; критерий)

Работает так: проверяет каждую строчку базы_данных на соответствие критерию, проверяет наличие чисел в столбце поле для нужной строки, подсчитывает количество таких строк.
Примеры

Пример расчета приведем по таблице 1. Определим, сколько раз в таблице 1 встречаются данные о самолете.

Для расчета создадим критерий, который отображен ниже.

	A
15	Наименование транспортного средства
16	Самолет

В ячейку D14 введем формулу:

=БСЧЁТ(A1:C9;C1; A15:А16)

Ответ равен 2. Эта формула просматривает записи о самолетах и подсчитывает количество числовых полей «Число пассажиров» в этих записях.

Пример

Определим, сколько раз в таблице 1 встречаются данные о транспортных средствах, вмещающих от 100 до 200 пассажиров.

Для расчета создадим критерий, который отображен ниже.

	C	D
20	Число пассажиров	Число пассажиров
21	>=100	<=200

В ячейку D23 введем формулу:

=БСЧЁТ(A1:C9;"Число пассажиров"; С20:D21)

Ответ равен 1. Эта формула просматривает данные о числе пассажиров, определяет строки, в которых данные о транспортном средстве с числом пассажиров от 100 до 200, и подсчитывает количество числовых полей «Число пассажиров» в этих строках.
Функция БСЧЁТА – функция баз данных, определяющая количество непустых ячеек в столбце, определенном параметром поле, в базе_данных, удовлетворяющих критерию.

Общий вид: БСЧЁТА(база_данных; поле; критерий)

Работает так: проверяет каждую строчку базы_данных на соответствие критерию, проверяет наличие каких-либо данных в столбце поле для нужной строки, подсчитывает количество таких строк.

---

Пример

Пример расчета приведем по таблице 1. Определим, сколько раз в таблице 1 встречаются данные самолете.

Для расчета создадим критерий, который отображен ниже.

	A
25	Наименование транспортного средства
26	Самолет

В ячейку D28 введем формулу:

=БСЧЁТА(A1:C9;A1; A25:А26)

Ответ равен 2. Эта формула просматривает записи о самолетах и подсчитывает количество непустых значений в поле «Наименование транспортного средства» в этих записях.
Функция ДМАКС – функция баз данных, определяющая максимальное число в столбце, определенном параметром поле, в базе_данных, удовлетворяющее критерию.

Общий вид: ДМАКС(база_данных; поле; критерий).

Работает так: проверяет каждую строчку базы_данных на соответствие критерию, среди нужных строк смотрит значение в столбце поле и находит среди них максимальное.
Пример

Пример расчета приведем по таблице 1. По таблице 1 определим максимальное число пассажиров.

Для расчета создадим критерий, который отображен ниже.

	A	B	C
30	Наименование транспортного средства	Цвет	Число пассажиров
31

В ячейку D34 введем формулу:

=ДМАКС(A1:C9;C1; A30:C31)

Ответ равен 260. Эта формула просматривает записи о транспортных средствах и определяет максимальное число по полю «Число пассажиров» в этих записях.

Функция ДМИН – функция баз данных, определяющая минимальное число в столбце, определенном параметром поле, в базе_данных, удовлетворяющее критерию.

Общий вид: ДМИН(база_данных; поле; критерий)

Работает так: проверяет каждую строчку базы_данных на соответствие критерию, среди нужных строк смотрит значение в столбце поле и находит среди них минимальное.
Пример

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

Для расчета создадим критерий, который отображен ниже.

---