пятница, 15 февраля 2013 г.

Работа с формулами и функциями в 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) сделать ссылки на ячейки таблицы (ее можно разместить на том же листе сбоку или снизу):

Комментариев нет:

Отправить комментарий