Создание формул в Excel

Упражнение 1. Ввод простых формул сложения, вычитания, умножения и деления

1. Щелкните в ячейке A1. Сначала сложим два числа.
2. Введите =534+382.
3. Нажмите клавишу ВВОД, чтобы получить результат 916.
4. Теперь выполним операции вычитания, умножения и деления.

• В ячейку A2 введите =534-382, после чего нажмите клавишу ВВОД. Результатом будет 152.
• В ячейку A3 введите =534*382, после чего нажмите клавишу ВВОД. Результатом будет 203 988.
• В ячейку A4 введите =534/382, после чего нажмите клавишу ВВОД. Результатом будет 1,397906 (результат может зависеть от количества знаков после запятой, заданного в Microsoft Excel).

5. Щелкните в ячейке A4, чтобы настроить количество знаков после запятой. Откройте вкладку Главная.
6. Переместите указатель мыши в группу Число  .
7. В группе Число наведите указатель мыши на кнопку Уменьшить разрядность  и нажмите ее. Обратите внимание на результат в ячейке A4, в котором количество знаков после запятой должно уменьшится на один. Нажмите кнопку Уменьшить разрядность  нужное количество раз.

Упражнение 2. Использование нескольких математических операторов в одной формуле

• Введите в Excel формулу для вычисления 4+4, а затем умножьте результат на 6. 
  Совет. Помните правила приоритета операторов: первыми вычисляются выражения в скобках, а умножение выполняется до сложения.

• Это выражение можно записать в виде =(4+4)*6 (результат — 48) или =6*(4+4).
  Хотя умножение следует до сложения (слева), в первую очередь вычисляется выражение в скобках.

Упражнение 3. Сложение чисел с использованием функции СУММ

1. Щелкните в ячейке A5.
2. На вкладке Главная переместите указатель мыши в группу .
3. Нажмите кнопку Автосумма . На листе появится формула, включающая все ячейки с A1 по A4.
4. Нажмите клавишу ВВОД, чтобы получить результат 205 057,4. 
   Результат может отличаться в зависимости от количества знаков после запятой, заданного в Excel.

Упражнение 4. Сложение двух чисел в столбце с использованием функции СУММ

1. Щелкните в любой пустой ячейке, например C2. Введите =СУММ(.
2. Щелкните в ячейке A1, чтобы добавить ее в формулу. Введите в формуле точку с запятой (;) после A1, чтобы отделить это число от следующего аргумента.
3. Щелкните в ячейке A3, чтобы добавить ее в формулу.
4. Введите закрывающую скобку ) и нажмите клавишу ВВОД. 
   Результатом будет 204 904.
5. Сравните формулы в ячейках A5 и C2. Сначала щелкните в ячейке A5 и посмотрите на формулу в верхней части листа в строке формул. Формула будет выглядеть следующим образом: =СУММ(A1:A4).
6. Щелкните в ячейке C2 (если в ней была введена последняя формула) и посмотрите на формулу в верхней части листа в строке формул. Формула будет выглядеть следующим образом: =СУММ(A1;A3).

В первой формуле двоеточие между А1 и А4 указывает, что аргумент формулы охватывает все ячейки с A1 по A4. Во второй формуле двоеточия нет, поскольку в ней указаны две ячейки, которые не находятся рядом. Точка с запятой между А1 и А3 отделяет первый аргумент формулы (A1) от второго (A3).

Упражнение 5. Копирование формулы из одной ячейки в другую с помощью относительных ссылок на ячейки

1. Сначала нужно выбрать другой лист для работы. Внизу листа щелкните ярлычок Сведения о продажах.
2. Щелкните в ячейке B7. В строке формул вверху листа появится формула в ячейке: =СУММ(B2:B6).
3. В ячейке B7 наведите указатель мыши на правый нижний угол, чтобы он принял вид черного знака "плюс" .
4. Щелкните и потащите угол ячейки с B7 до C7. Отпустите кнопку мыши. Результатом в ячейке C7 будет 9041,80 р.
5. Пока курсор находится в ячейке C7, посмотрите на формулу в поле "Имя". Теперь она имеет вид =СУММ(C2:C6). Значение формулы изменилось, поскольку ссылки на ячейки являются относительными и автоматически изменяются в соответствии с расположением новой формулы.

Упражнение 6. Создание формулы с абсолютными ссылками на ячейки

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

1. Сначала нужно рассчитать общую сумму продаж за две недели. Щелкните в ячейке D2. На вкладке Главная в группе Редактирование нажмите кнопку Автосумма.
2. Нажмите клавишу ВВОД, чтобы получить в ячейке общий итог: 1129,20 р.
3. Теперь скопируйте формулу в ячейку D2. Щелкните в ячейке D2 и наведите указатель мыши на ее правый нижний угол, чтобы он принял вид черного знака "плюс" .
4. Щелкните и потащите угол ячейки с D2 до D6. Отпустите кнопку мыши. Ниже представлен результат.

• Ячейка D3: 3806,40 р.
• Ячейка D4: 2994,60 р.
• Ячейка D5: 1246,06 р.
• Ячейка D6: 7973,50 р.

5. Щелкните в пустой ячейке, например F2. Введите 0,03 и нажмите клавишу ВВОД.
6. Щелкните в ячейке E2. Введите знак равенства (=).
7. Щелкните в ячейке D2, чтобы добавить ее в формулу. В формуле введите звездочку (*), чтобы выполнить умножение на комиссионную ставку.
8. Щелкните в ячейке F2, чтобы добавить в формулу комиссионную ставку. Ячейка F2 будет вставлена в формулу.
9. Нажмите клавишу F4 . В формуле аргумент F2 будет преобразован в абсолютную ссылку на ячейку: $F$2. 
   Нажмите клавишу ВВОД. Результатом будет 33,88 р.
10. Скопируйте формулу. Щелкните в ячейке E2 и наведите указатель мыши на ее правый нижний угол, чтобы он принял вид черного знака "плюс" .
11. Щелкните и потащите угол ячейки с E2 до E6. Отпустите кнопку мыши. Ниже представлен результат.

• Ячейка E3: 114,19 р.
• Ячейка E4: 89,84 р.
• Ячейка E5: 37,38 р.
• Ячейка E6: 239,21 р.

12. Щелкните в ячейке E2 и посмотрите значение в строке формул: =D2*$F$2. Щелкните в ячейке E3 и посмотрите значение в строке формул: =D3*$F$2. Относительная ссылка на ячейку D2 изменилась на D3. Абсолютная ссылка на ячейку $F$2 осталась неизменной.

Упражнение 7. Создание формулы со смешанными ссылками на ячейки

В этом упражнении будет заполнена таблица комиссионных ставок, чтобы можно было определить суммы комиссии на основе объемов продаж и ставок.

Подготовка. Если в столбце C отображаются символы ####, это означает, что он недостаточно широк для отображения всего содержимого. Чтобы расширить столбец C, наведите курсор на его правую границу в верхней части. Когда указатель примет вид двусторонней стрелки, щелкните и перетащите границу вправо. Отпустите кнопку мыши, когда символы # исчезнут.

1. Сначала нужно выбрать другой лист для работы. Внизу листа щелкните ярлычок Таблица комиссий. Столбец C содержит суммы продаж. В строке 3 находятся ставки комиссии.
2. В формуле нужно указать столбцы и строки, которые должны быть абсолютными. Формула всегда должна указывать на столбец C со ставками продаж, поэтому этот столбец будет абсолютным. Кроме того, формула должна указывать на ставки комиссии, поэтому строка 3 также будет абсолютной.
3. Щелкните в ячейке D4, чтобы создать в ней первую формулу. Введите знак равенства (=). Щелкните в ячейке C4, чтобы ввести в формулу сумму продаж. Формула примет вид =C4.
4. Измените "C" на абсолютную ссылку. Нажмите клавишу F4 . Результатом будет $C$4. Нажмите клавишу F4 еще раз. Результатом будет C$4. Нажмите клавишу F4 еще раз. Результатом будет $C4. Теперь формула включает абсолютную ссылку на столбец C ($C) и относительную ссылку на строку 4 (4).
5. В этой же формуле введите звездочку (*), чтобы умножить сумму продаж на ставку комиссии. Новая формула примет вид $C4*.
6. В формуле введите D3, чтобы указать ставку комиссии. Формула примет вид $C4*D3. Ссылка на строку 3 должна быть абсолютной.
7. Нажмите клавишу F4. Результатом будет $D$3. Нажмите клавишу F4 еще раз. Результатом будет D$3. Это и есть нужный вариант, в котором ссылка на столбец D является относительной, а на строку 3 — абсолютной.
8. Нажмите клавишу ВВОД. Результатом будет 1000 р.
9. Скопируйте формулу из ячейки D4 в ячейку H4. Щелкните в ячейке D4 и наведите указатель мыши на ее правый нижний угол, чтобы он принял вид черного знака "плюс" .
10. Щелкните и потащите угол ячейки с D4 до H4. Отпустите кнопку мыши. Результатами будут 2000 р., 3000 р., 4000 р. и 5000 р.
11. Щелкните в ячейке E4 и посмотрите значение в строке формул: =$C4*E$3. Абсолютная ссылка $C означает, что формула всегда будет указывать на столбец C. Абсолютная ссылка $3 означает, что формула всегда будет указывать на строку 3. Затем нужно скопировать формулу в другие ячейки таблицы.
12. Щелкните в ячейке D4 и потащите ее до ячейки H4. Вокруг пяти выделенных ячеек появится черная рамка. В ячейке H4 наведите указатель мыши на правый нижний угол, чтобы он принял вид черного знака "плюс" . Щелкните и потащите угол ячейки с H4 до H16. Отпустите кнопку мыши. Будет заполнена остальная часть таблицы.
13. Щелкните в любой ячейке и посмотрите на строку формулы, чтобы убедиться, что абсолютные ссылки на столбец C ($C) и строку 3 ($3) не изменились.

Упражнение 8. Создание формулы с использованием функции ПЛТ

В этом упражнении с помощью функции ПЛТ будет рассчитана сумма ежемесячного платежа по 30-летнему кредиту. Несмотря на то, что имя функции известно, упражнение начнется с ее поиска.

1. Сначала нужно выбрать другой лист для работы. Внизу листа щелкните ярлычок Функции.
2. Щелкните в ячейке A1.
3. На ленте на вкладке Формулы в группе Библиотека функций нажмите кнопку Вставить функцию . Откроется диалоговое окно Вставка функции.
4. В поле Поиск функции введите расчет платежа, после чего нажмите кнопку Найти. В списке Выберите функцию выберите функцию ПЛТ. Прочтите под списком описание этой функции и нажмите кнопку ОК. Откроется диалоговое окно Аргументы функции.
5. При необходимости щелкните в поле Ставка. В нижней части окна будет показано описание аргумента ставки. В поле введите 5%/12. Ставка делится на на 12 потому, что за год будет выполнено 12 ежемесячных платежей.
6. Щелкните в поле Кпер и прочтите описание аргумента. В поле введите 30*12, что соответствует 30-летнему кредиту с 12 платежами в год.
7. Щелкните в поле Пс и прочтите его описание под списком аргументов. В поле введите 220000. Остальные аргументы (Бс и Тип) не выделены полужирным шрифтом, поэтому их можно не использовать. Нажмите кнопку ОК.
8. Результатом будет значение 1181,01 р. красного цвета в скобках. Это ежемесячный платеж по кредиту 220 000 р. под 5% на 30 лет.

Упражнение 9. Создание формулы с использованием функции ПРОПНАЧ

В этом упражнении с помощью функции ПРОПНАЧ будут исправлены начальные буквы имен, представленные в неправильном регистре. В этот раз будет использована функция автозавершения формул, с помощью которой можно вводить функции, частично указывая их имена.

1. Щелкните в ячейке F2. Введите знак равенства =, а затем — первую букву имени функции: П.

1. Появится список функций, имена которых начинаются на "П". Прокрутите список вниз до функции ПРОПНАЧ и выберите ее. Появится всплывающая подсказка с описанием функции.
2. Дважды щелкните функцию ПРОПНАЧ, чтобы вставить ее в формулу. Появится подсказка с именем функции и единственным текстовым аргументом.
3. Щелкните в ячейке E2. При этом в формулу будет вставлено имя Алексея (текстовый аргумент).
4. Введите закрывающую скобку ) после E2 в формуле.
5. Нажмите клавишу ВВОД. Буквы в имени Алексея будут преобразованы в правильный регистр, т. е. первая буква станет прописной, а остальные — строчными.
6. Скопируйте формулу из ячейки F2 в ячейку F10. Щелкните в ячейке F2, наведите указатель на правый нижний угол, чтобы он принял вид черного знака "плюс" . Щелкните и потащите указатель с F2 до F10. Отпустите кнопку мыши. Все имена будет преобразованы в правильный регистр. Можно щелкнуть любое имя в столбце F и посмотреть формулу строке формул. Сохранять формулу и два столбца нет необходимости.
7. У столбца F после копирования фомулы все еще должна быть черная рамка. Щелкните столбец правой кнопкой мыши и выберите команду Копировать. Вокруг столбца появится штриховая рамка.
8. Затем щелкните в ячейке E2 и потащите указатель до ячейки E10. Вокруг столбца E должна появиться черная рамка. Щелкните столбец правой кнопкой мыши и в подменю Параметры вставки выберите пункт Значения .
9. Нажмите клавишу ESC, чтобы убрать штриховую рамку вокруг столбца F. Щелкните в ячейке E2 и посмотрите на строку формулы. Вместо формулы там должно отображаться имя Алексея.
10. Наконец, щелкните столбец F, содержащий имена с формулами. Щелкните в ячейке F2 и потащите указатель вниз до ячейки F10. Нажмите клавишу DELETE. Имена в столбце будут преобразованы в правильный регистр.