Работа с формулами и функциями в Excel

Формулы

Важным достоинством Excel является умение производить ВЫЧИСЛЕНИЯ в таблицах, т.е. значения в некоторых ячейках могут рассчитываться в исходя из других.  Давайте для примера посчитаем значение линейной функции: 

На этом листе сверху записана функция (знак второй степени вы найдете среди символов в меню Вставка>Символ).

Ниже значения параметров k и b. Обратите внимание, что сами значения (цифры) записаны каждая в ОТДЕЛЬНОЙ ячейке. Внизу располагается маленькая табличка: в ячейке C5 значение х, в D5 – ВЫЧИСЛЯЕМОЕ значение у.

Запись функции в верхней строке еще не является формулой для вычисления (это просто текстовая строка). Саму формулу мы запишем в ячейке D5, и использовать для вычисления она будет данные из ячеек С2, С3 и С5. Посмотрите внимательно, как это делается, на небольшом фрагменте видео:

 

Кратко еще раз проговорим правила работы с формулами:

1. формула ВСЕГДА начинается со знака = ;

2. ВХОД в режим редактирования формул – выделение ячейки мышью и переход в строку формул, ВЫХОД – клавишей Enter;

3. в формуле вы можете использовать данные из ДРУГИХ ЯЧЕЕК таблицы; для этого в режиме редактирования просто щелкните на них мышью (ячейки выделятся цветными прямоугольниками);

4. формулы можно копировать АВТОЗАПОЛНЕНИЕМ (при этом относительное положение ссылок не изменяется);

5. чтобы ЗАФИКСИРОВАТЬ положение ссылки (сделать абсолютным), поставьте знак доллара $ перед буквой столбца или цифрой строки смотря по тому, что именно вы хотите зафиксировать:

ссылка	при копировании формулы автозаполнением…
B2	ячейка данных будут смещаться вслед за ячейкой результата
B$2	столбец ячейки данных будет смещаться, а строка всегда будет оставаться 2-й
$B2	столбец ячейки данных всегда будет оставаться B, а строка будет смещаться
$B$2	ячейка данных будет строго B2

Задание 2

Воспроизведите таблицу для вычисления значений линейной функции (на Листе 2; переименуйте его в 2 Линейная функция). Значения параметров k и b могут быть произвольными.

Задание 3

Сделайте таблицу умножения от 1 до 10 (Лист 3 переименуйте в 3 Таблица умножения) Воспользоваться автозаполнением, формулами.

Примечание: двумерные таблицы заполняются так: сначала вы пишете формулу в верхней левой ячейке (где стоит подчеркнутая 1), и автозаполнением (сверху вниз) копируете ее на весь левый столбец. Отпускаете мышь. Далее (не снимая выделение с левого столбца) автозаполнением слева направо (от 10 до 100) копируете столбец на всю таблицу. Все!

 Функции

  В формулах в Excel можно использовать не только знаки арифметических операций (+, -, *, :, ^…), но и около 240 встроенных функций. Они подразделяются на классы:

математические	СУММ, КОРЕНЬ, ОКРУГЛ и др.
текстовые	СТРОЧН (делает все буквы в строке строчными), СОВПАД (проверяет, совпадают ли две строки), ДЛСТР (считает количество символов с строке), ЛЕВСИМВ (берет заданное число символов с начала строки) и др.
логические	И, ИЛИ, НЕ, ЕСЛИ, ИСТИНА, ЛОЖЬ и др.
дата и время	СЕГОДНЯ (текущая дата), МЕСЯЦ (из даты в полном формате выделяет месяц), ДАТА (преобразует числа в переменную с типом «дата») и др.
статистические	МАКС, МИН, ГАММАОБР, БЕТАРАСП, ДИСП и др. (методы статистической обработки данных)
и др.

 Посмотрите на видео, как вставлять функции.

 

Мы будем делать классный журнал: 
 

Нужно посчитать сумму баллов за три самостоятельных и выставить итоговую оценку по пятибалльной системе из такого расчета:

Вот формула для расчета итоговой оценки:

 Окно условного форматирования выглядит так:

Условия:

если значение равно 5, фон желтый, а также

если значение между 3 и 4, фон синий, а также

если значение равно 2, фон красный.

Задание 4

Сделайте классный журнал. Посчитайте сумму баллов, итоговую оценку. Сделайте условное форматирование. Фамилии студентов (4–5 человек) и оценки произвольные.

Дополнительное задание (на аккуратность и внимательность): в формуле ЕСЛИ(…) вместо констант (60, 75, 90, 2, 3, 4, 5) сделать ссылки на ячейки таблицы (ее можно разместить на том же листе сбоку или снизу):

Источник