2020-06-08
141
Excel рассматривает содержимое ячейки, как формулу, если оно начинается со знака равенства (=). Формула – это набор чисел и ссылок на числовые ячейки, соединенные знаками математических операций.
Чтобы задать ссылку на ячейку, надо указать в формуле ее имя. Это можно сделать вручную или щелчком на соответствующей ячейке по ходу ввода формулы. По окончании ввода надо нажать Enter.
Формулы в ячейках не отображаются. Вместо формулы воспроизводится результат ее вычисления. Чтобы увидеть формулу, хранящуюся в вычисляемой ячейке, надо выделить эту ячейку и посмотреть в строку формул.
Пример 1. Имеется таблица «Покупки».
| А | В | С | D | |
| 1 | Наименование | Цена | Количество | Стоимость |
| 2 | Молоко | 18,00 | 3 | =В2*С2 |
| 3 | Хлеб | 19,50 | 2 | =В3*С3 |
| 4 | Сахар | 20,70 | 5 | =В4*С4 |
| 5 | Общая стоимость | =D1+D2+D3 |
ü В какие ячейки заносят текст, в какие – числа, а в какие – формулы?
ü Запишите формулу для стоимости молока.
ü Как найти стоимость каждого товара?
ü Заполните столбец D с помощью автозаполнения. Что получилось?
ü Поменяем цены на товары, что произойдет со стоимостью? Почему?
ü Измените количество товара, что при этом еще изменится?
ü А как найти общую стоимость покупок? Вычислите.
Можно также использовать Мастер функций (Рис.7): вкладка Формулы → группа Библиотека функций → кнопка Вставить функции → выбрать нужную функцию из списка → задать аргументы выбранной функции с помощью набора адресов или щелчка на конкретных ячейках.
Мастер функций вызывается также с помощью кнопки fx в строке формул.
Наиболее распространенные функции: СРЗНАЧ, СУММ, МАКС, МИН, ПРОИЗВ.
Рис.7. Окно Мастера фукнций
Функция СЧЕТ – подсчитывает общее количество чисел в списке аргументов.
Например,
СЧЁТ(3;а;2;7;хлеб;1)=4
Т.е. аргументы данной функции могут содержать или ссылаться на данные различных типов, но в подсчете участвуют только числа.
Функция СЧЁТЕСЛИ – подсчитывает количество ячеек внутри диапазона, удовлетворяющих заданному критерию.
СЧЁТЕСЛИ(А1:А10; «>15»)
Например,
=СЧЕТЕСЛИ(A2:A5;"яблоки") – количество ячеек с «яблоки» в диапазоне A2:A5.
=СЧЕТЕСЛИ(B2:B5;">55") – количество ячеек со значением выше 55 в диапазоне B2:B5.
Функция СУММЕСЛИ – суммирует ячейки, заданные критерием.
СУММЕСЛИ(диапазон; критерий; диапазон суммирования )
Замечание
ü Ячейки в «диапазон суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют критерию.
ü Если «диапазон_суммирования» опущен, то суммируются ячейки в аргументе «диапазон».
Например
=СУММЕСЛИ(A2:A5;">160";B2:B5) – сумма чисел из диапазона B2:B5 для которых соответствующее значение из диапазона А2:А5 более 160.
Задание 1.
– Создать новую Книгу Excel и на Листе 1 заполнить диапазон А1:N1 по образцу:
| А | В | С | D | E | F | G | H | I | J | K | L | N | |
| 1 | 25 | -61 | 0 | -82 | 18 | -11 | 0 | 30 | 15 | -31 | 0 | -58 | 22 |
– Найти:
| общее количество чисел | =СЧЁТ(А1:N1) | 13 |
| количество положительных чисел | =СЧЁТЕСЛИ(А1:N1;">0") | 5 |
| количество отрицательных чисел | =СЧЁТЕСЛИ(А1:N1;"<0") | 5 |
| количество нулей | =СЧЁТЕСЛИ(А1:N1;"=0") | 3 |
| максимальное значение | =МАКС(А1:N1) | 30 |
| минимальное значение | =МИН(А1:N1) | -82 |
| среднее значение | =СРЗНАЧ(А1:N1) | -10,23076923 |
| сумма всех чисел | =СУММ(А1:N1) | -133 |
| сумма положительных чисел | =СУММЕСЛИ(А1:N1;">0") | 110 |
| сумма отрицательных чисел | =СУММЕСЛИ(А1:N1;"<0") | -243 |
– Сохранить документ под именем Вычисления_ Фамилия
Условная функция ЕСЛИ – возвращает одно значение, если заданное условие при вычислении дает значение ИСТИНА, и другое значение, если ЛОЖЬ.
Общий вид: ЕСЛИ(условие; выражение1; выражение2).
Условие – это логическое выражение, которое может принимать значение ИСТИНА или ЛОЖЬ. Если условие истинно, то значение данной ячейки определит выражение1, в противном случае – выражение2.
Выражение1 и Выражение2 могут быть числами, формулами или текстом.
Задание 2.
– открыть файл Вычисления_Фамилия..
– Лист2 переименовать в «ЕСЛИ» и построить таблицу по образцу, содержащую фамилии учеников 4 класса, оценки за контрольную работу по трем предметам. Известно, что ученик будет зачислен в гимназию, если сумма баллов за три работы будет больше или равна проходному баллу, в противном случае – нет.
Решение.
Исходной информацией являются фамилии, оценки за экзамены и проходной балл. Сумма баллов и информация о зачислении вычисляются с помощью формул.
ü Образец таблицы
| А | В | С | D | E | F | |
| 1. | Фамилия | Оценки | Сумма баллов | Зачислен (да/нет) | ||
| 2. | Математика | Русский | Иностранный | |||
| 3. | Антонов | 4 | 5 | 5 | ||
| 4. | Воробьев | 3 | 5 | 5 | ||
| 5. | Синичкин | 5 | 5 | 3 | ||
| 6. | Воронина | 5 | 4 | 3 | ||
| 7. | Снегирева | 3 | 5 | 4 | ||
| 8. | Общее количество поступивших |
| ||||
ü В ячейке Е3 вычислить сумму баллов за три экзамена для первого ученика, по формуле:
=В3+С3+D3
ü С помощью автозаполнения вычислить сумму баллов для остальных учеников (ячейки Е4:Е7)
ü С помощью функции ЕСЛИ выяснить, кто из ребят зачислен, если проходной балл равен 13:
=ЕСЛИ(E3>=13; «ДА»; «НЕТ»)
ü В ячейке D2 подсчитать общее кол-во поступивших:
=СЧЕТЕСЛИ(F3:F7, «ДА», «НЕТ»)
ü Сохранить изменения в документе
Задание 3.
– Лист3 переименовать в «Логические операции» и создать таблицу по образцу.
– Выяснить сколько учеников могут заниматься в баскетбольной секции, если туда принимают детей с ростом не менее 155 см? Возраст не должен превышать 15 лет.
Решение
| А | В | С | D | |
| 1 | Фамилия | Рост | Возраст | Зачислен/не зачислен |
| 2 | Алешин | 163 | 16 | |
| 3 | Борисова | 172 | 17 | |
| 4 | Васильев | 157 | 13 | |
| 5 | Иванова | 170 | 12 | |
| 6 | Калягина | 180 | 17 | |
| 7 | Левченко | 169 | 14 | |
| 8 | Машин | 158 | 15 | |
| 9 | Петров | 165 | 13 | |
| 10 | Сидорова | 174 | 16 |
В столбец D запишем сведения о том, зачислен ученик или нет.
В данном примере нужно учитывать два условия одновременно: возраст не более 15 лет и рост не менее 155 см.
Подобные логические выражения строятся при помощи логических операций (логическое И, логическое ИЛИ, логическое отрицание НЕ). При записи логических операций в табличных процессорах сначала записывается имя логической операции (И, ИЛИ, НЕ), а затем в круглых скобках перечисляются логические операнды.
Т.о. условие данной задачи запишется в виде: = И(B4>=155;C4<=15). А чтобы подсчитать количество зачисленных в секцию учеников будем заполнять ячейки следующим образом: в том случае если ученик подходит, то в столбце D запишем «да», а если не подходит – «нет», т.е. значение D2
=ЕСЛИ(И(B2>=155;C2<=15); «да»; «нет»).
После этого подсчитать количество учащихся, которые зачислены в баскетбольную секцию, используя функцию СЧЕТЕСЛИ, т.е. в ячейке D12 запишется формула:
=СЧЕТЕСЛИ(D2:D11; «да»).
| А | В | С | |
| 1 | Фамилия | Количество баллов | Оценка |
| 2 | Воробьев | 13 | |
| 3 | Галкина | 11 | |
| 4 | Глухарев | 18 | |
| 5 | Ласточкин | 22 | |
| 6 | Перепелкина | 25 | |
| 7 | Синичкин | 16 | |
| 8 | Снегирев | 19 | |
| 9 | Сорокина | 14 | |
| 10 | Стрижкова | 12 | |
| 11 | Тетерева | 11 |
Задание 4*.
Учащиеся 3 класса выполняли контрольную работу по математике, в таблице записаны набранные балы каждого ученика.
Необходимо выставить оценки за работу, используя следующие критерии:
ü Если набранных баллов менее 5, то оценка «2»,
ü Если баллов больше 5, но менее 7– оценка «3»,
ü Если баллов больше 6, но менее 9, то – «4»,
ü Если же баллов более 8, то оценка «5».
Решение
Лист 4 переименовать «Сложное условие»
Прежде чем заполнить столбец С, рассмотрим от каких условий зависит результат в ячейке. Рассмотрим это с помощью блок схемы:
 |
Таким образом, в ячейке С2 можно записать следующую формулу:
=ЕСЛИ(B2>8;5;ЕСЛИ(B2>=7;4;ЕСЛИ(B2>5;3;2)))
С помощью автозаполнения заполняем диапазон С3:С11.
Сохранить изменения в документе Вычисления_Фамилия.
Excel: сортировка и фильтрация данных.
Сортировка – это упорядочение данных по возрастанию или по убыванию.
При сортировке данных: выделить строки в таблице, которые необходимо отсортировать → вкладка Главная → группа Редактирование → Сортировка и фильтр → Настраиваемая сортировка → задать порядок сортировки по каждому полю.
Задание 5.
ü Создать новую книгу и сохранить ее под именем «Сортировка_Фамилия»
ü На Листе 1 создать таблицу по образцу и скопировать ее на Лист2 и Лист3:
| А | В | С | |
| 1. | Фамилия | Имя | Год рождения |
| 2. | Иванов | Максим | 1999 |
| 3. | Хасанова | Альбина | 2000 |
| 4. | Акчулпанов | Марсель | 1996 |
| 5. | Карпова | Марина | 1995 |
| 6. | Иванов | Сергей | 1993 |
| 7. | Акчулпанов | Айдар | 1999 |
| 8. | Иванов | Максим | 1997 |
ü На Листе1 упорядочить фамилии в алфавитном порядке.
– выделить любую ячейку в столбце Фамилия → вкладка Главная → группа Редактирование → Сортировка и фильтр → Сортировать от А до Я
ü На Листе 2 упорядочить таблицу по году рождения по убыванию.
– выделить любую ячейку в столбце Год рождения → вкладка Главная → группа Редактирование → Сортировка и фильтр → Сортировать по убыванию
ü На Листе 3 Упорядочить таблицу следующим образом: сначала фамилии по возрастанию, затем имена по возрастанию, после год рождения по убыванию.
– выделить всю таблицу (диапазон А1:С8) → вкладка Главная → группа Редактирование → Сортировка и фильтр → Настраиваемая сортировка →
ü Сохранить документ.
Фильтр – быстрый способ поиска подмножества данных и работы с ними в списке. Т.е. при фильтрации отображаются только записи, обладающие нужными свойствами.
При фильтрации данных: выделить заголовок столбца, по которому нужно отфильтровать → вкладка Главная → группа Редактирование → Сортировка и фильтр → Фильтр … → в ячейках, содержащих заголовки полей, появляются раскрывающие кнопки, щелчок на которых открывает доступ к списку вариантов фильтрации.
Чтобы отменить использование автофильтра надо повторно дать команду вкладка Главная → группа Редактирование → Сортировка и фильтр → Фильтр.
Задание 6.
ü Открыть файл Сортировка_Фамилия.
ü На Лист 4 скопировать таблицу с Листа 2.
ü В заголовках столбцов установить фильтр.
ü Сделать так, чтобы отображались только ученики с фамилией ИВАНОВ:
ü Скопировать полученную таблицу на Лист 5 и вернуться на Лист4 и отобразить все записи.
ü На Лист6 нужно скопировать сведения о тех учениках, кто родился после 1995 года и не позже 1999.
ü Сохранить изменения в книге «Сортировка_Фамилия»
Диаграмма
– это графическое представление данных рабочего листа. Используются для более наглядного представления табличных данных.
Создание диаграммы: выделить данные для диаграммы → вкладка Вставка → группа Диаграммы → выбрать нужный тип диаграммы.
При создании диаграммы открывается доступ к инструментам для работы с диаграммой: отображаются вкладки Конструктор и Формат.
Добавление названия к диаграмме:
Вкладка Макет ® группа Подписи ® кнопка Название диаграммы ® ввести с клавиатуры название диаграммы.
В той же группе кнопка Подписи данных позволяет отобразить на диаграмме числовые значения.
Изменение данных в легенде или в подписях к оси: Вкладка Конструктор ® группа Данные ® кнопка Выбрать данные ® выделить элемент, который нужно изменить ® кнопка Изменить ® указать диапазон ячеек, из которых необходимо взять данные или ввести вручную.
