Как составить формулу в excel: подробный гид по excel таблицам для чайников

Формула 6: ЕСЛИОШИБКА

Эта функция возвращает заданное пользователем значение, если в процессе вычисления по формуле обнаруживается ошибка. Если же получившееся значение корректное, она оставляет его.

Синтаксис

У этой функции два аргумента. Синтаксис следующий:

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

Описание аргументов:

1. Значение – непосредственно формула, проверяемая на предмет багов.
2. Значение если ошибка – результат, появляющийся после того, как ошибка будет обнаружена.

Если говорить о примерах, то данная формула покажет в случае невозможности осуществления деления текст «Ошибка при вычислении».

Примечания

Excel позволяет использовать до 64 вложенных функций ЕСЛИ, но это вовсе не означает, что так и надо делать. Почему?

Нужно очень крепко подумать, чтобы выстроить последовательность из множества операторов ЕСЛИ и обеспечить их правильную отработку по каждому условию на протяжении всей цепочки. Если при вложении вы допустите в формуле малейшую неточность, она может сработать в 75 % случаев, но вернуть непредвиденные результаты в остальных 25 %. К сожалению, шансов отыскать эти 25 % немного.

Работа с множественными операторами ЕСЛИ может оказаться чрезвычайно трудоемкой, особенно если вы вернетесь к ним через какое-то время и попробуете разобраться, что пытались сделать вы или, и того хуже, кто-то другой.

Если вы видите, что ваш оператор ЕСЛИ все разрастается, устремляясь в бесконечность, значит вам пора отложить мышь и пересмотреть свою стратегию.

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

Ниже приведен пример довольно типичного вложенного оператора ЕСЛИ, предназначенного для преобразования тестовых баллов учащихся в их буквенный эквивалент.

97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«;ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))» />

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

Если тестовых баллов больше 79, учащийся получает оценку B.

Если тестовых баллов больше 69, учащийся получает оценку C.

Если тестовых баллов больше 59, учащийся получает оценку D.

В противном случае учащийся получает оценку F.

Этот частный пример относительно безопасен, поскольку взаимосвязь между тестовыми баллами и буквенными оценками вряд ли будет меняться, так что дополнительных изменений не потребуется. Но что если вам потребуется разделить оценки на A+, A и A– (и т. д.)? Теперь ваши четыре условных оператора ЕСЛИ нужно переписать с учетом 12 условий! Вот так будет выглядеть ваша формула:

Она по-прежнему точна и будет правильно работать, но вы потратите много времени, чтобы написать ее, а потом протестировать. Еще одна очевидная проблема состоит в том, что вам придется вручную вводить баллы и эквивалентные буквенные оценки. Каковы шансы, что вы не ошибетесь? А теперь представьте, как вы пытаетесь сделать это 64 раза для более сложных условий! Конечно, это возможно. Но неужели вам хочется потратить столько сил без всякой уверенности в отсутствии ошибок, которые потом будет трудно обнаружить?

Совет: Для каждой функции в Excel обязательно указываются открывающая и закрывающая скобки (). При редактировании Excel попытается помочь вам понять, что куда идет, окрашивая разными цветами части формулы. Например, во время редактирования показанной выше формулы при перемещении курсора за каждую закрывающую скобку «)» тем же цветом будет окрашиваться соответствующая открывающая скобка. Это особенно удобно в сложных вложенных формулах, когда вы пытаетесь выяснить, достаточно ли в них парных скобок.

Функция ЕСЛИ в Excel с примерами

Представление данных в виде диаграмм

Выполнив задания этой темы, вы научитесь:- создавать различные типы диаграмм;
— форматировать объекты диаграммы;
— изменять размеры диаграммы.

Создание и редактирование диаграмм

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

Редактирование диаграммы производится с помощью команд контекстного меню, позволяющих изменять ее параметры: заголовки, легенду, подписи рядов и данных. Можно добавлять новые данные для построения диаграммы или удалить построенные ранее диаграммы.

Форматирование диаграмм

Форматирование диаграммы производится с помощью команд меню Формат, которые позволяют:    изменить тип диаграммы;
    изменить используемые шрифты;
    изменить цвет и узор объектов диаграммы;
    задать масштаб и формат вертикальной и горизонтальной осей;
    выбрать вид рамки, ограничивающей диаграмму.

Задание 4.10

Используя данные таблицы Расписание.xls, создайте столбчатую диаграмму распределения времени стоянок по станциям и круговую объемную диаграмму распределения времени в пути. Отредактируйте диаграммы.

В результате выполнения задания вы освоите основные технологические операции создания и редактирования диаграмм.

Технология работы

1. Запустите табличный процессор.

2. Откройте документ Расписание.xls.

3. Создайте столбчатую диаграмму распределения времени стоянок по станциям:    выделите диапазон ячеек С5:С10;
    выберите команду меню Вставка ► Диаграмма;
    в появившемся окне Мастера диаграмм выберите тип диаграммы — Гистограмма, вид — объемный;
    щелкните на кнопке Далее.

4. На следующем шаге Мастера диаграмм задайте подписи по оси X:    на вкладке Диапазон данных установите переключатель Ряды в строках;
    перейдите на вкладку Ряд;
    щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А10;
    задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку СЗ;
    щелкните на кнопке Далее.

5. На следующем шаге Мастера диаграмм задайте подписи осей X и У:    перейдите на вкладку Заголовки;
    в поле Название диаграммы введите Распределение стоянок;
    в поле Ось X (категорий) введите Станция’,
    в поле Ось Y (значений) введите Время’,
    перейдите на вкладку Легенда;
    сбросьте флажок Добавить легенду;
    щелкните на кнопке Далее.

6. На следующем шаге Мастера диаграмм задайте расположение диаграммы, выбрав переключатель Поместить диаграмму на отдельном листе, и щелкните на кнопке Готово.

7. Измените шрифт заголовка диаграммы:    щелкните мышью на заголовке диаграммы — вокруг текста появится рамка с маркерами выделения;
    щелкните на заголовке правой кнопкой мыши и выберите в контекстном меню команду Формат заголовка диаграммы;
    перейдите на вкладку Шрифт;
    в появившемся диалоговом окне выберите полужирное начертание шрифта, размер — 16, цвет — синий;
    щелкните на кнопке ОК.

8. Создайте диаграмму распределения времени в пути:    выделите диапазон ячеек Е5:Е11;
    выберите команду меню Вставка ► Диаграмма;
    в появившемся окне Мастера диаграмм выберите тип диаграммы — Круговая, вид — объемный;
    щелкните на кнопке Далее.

9. Задайте подписи по оси X:    на вкладке Диапазон данных установите переключатель Ряды в столбцах;
    перейдите на вкладку Ряд;
    щелкните в поле Подписи по оси X и выделите в таблице диапазон ячеек А5:А11;
    задайте подпись ряда, щелкнув в поле Имя и выделив в таблице ячейку ЕЗ;
    щелкните на кнопке Далее.

10. На следующем шаге мастера задайте подписи данных на диаграмме:    выберите вкладку Подписи данных;
    установите флажок Включить в подписи — значения;
    перейдите на вкладку Легенда;
    установите флажок Добавить легенду, выберите переключатель Размещение — справа;
    щелкните на кнопке Далее.

11. Задайте расположение диаграммы, выбрав переключатель Поместить диаграмму на отдельном листе, и щелкните на кнопке Готово.

12. Сохраните документ и закройте табличный процессор командой Файл ► Выход.

Задания для самостоятельной работы

Задание 4.11.

Представьте данные созданной в предыдущей теме таблицы в виде диаграмм. Используйте несколько типов диаграмм. Задайте им различное оформление.

Задание 4.12.

Создайте таблицу успеваемости нескольких учеников по разным предметам. Отобразите эти данные на нескольких диаграммах. Используйте различные возможности оформления диаграмм.

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

В приведенном примере формула =СУММ(A1;B1) позволяет определить сумму двух чисел в ячейках, которые расположены по горизонтали.

Формула начинается со знака «=». Далее задана функция СУММ. Она указывает, что необходимо произвести суммирование заданных значений.

В скобках числятся координаты ячеек. Выбирая ячейки, следует не забывать разделять их знаком «;».

Если нужно найти сумму трех чисел, то формула будет выглядеть следующим образом:

Формула суммы трех заданных чисел

Если нужно сложить 10 и более чисел, то используется другой прием, который позволяет исключить выделение каждой ячейки. Для этого нужно просто указать их диапазон.

=СУММ(A1:A10). На рисунке арифметическая операция будет выглядеть следующим образом:

Определение диапазона ячеек для формулы сложения

Также можно определить произведение этих чисел. В формуле вместо функции СУММ необходимо выбрать функцию ПРОИЗВЕД и задать диапазон ячеек.

Формула произведения десяти чисел

Пример суммирования с использованием функции СУММЕСЛИ

Формула 2: Если

Эта функция необходима, если пользователь хочет задать определенное условие, при котором должен осуществляться расчет или выводиться конкретное значение. Она может принимать два варианта: истинное и ложное.

Синтаксис

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

=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»).

Здесь под логическим выражением подразумевается непосредственно описывающая критерий формула. С ее помощью будут проверяться данные на предмет соответствия определенному условию. Соответственно, аргумент «значение если ложь» предназначен для той же задачи, с тем лишь отличием, что оно зеркально противоположное по смыслу. Простыми словами, если условие не подтвердилось, то программа осуществляет определенные действия.

Есть еще один вариант, как можно применять функцию ЕСЛИ

– вложенные функции. Здесь может быть намного больше условий, вплоть до 64. Пример рассуждения, соответствующего формуле, приведенной на скриншоте, такой. Если ячейка А2 равна двум, то нужно вывести значение «Да». Если же она имеет другое значение, то необходимо проверить, равна ли ячейка D2 двум. Если да, то нужно выдать значение «нет», если и здесь условие оказывается ложным, то формула должна вернуть значение «возможно».

Не рекомендуется пользоваться вложенными функциями слишком часто, поскольку их использовать довольно тяжело, возможны ошибки. И потребуется немало времени, чтобы их исправить.

Функция ЕСЛИ

может применяться и с целью понять, пустая ли определенная ячейка. Чтобы добиться этой цели, необходимо использовать еще одну функцию –ЕПУСТО .

Тут синтаксис следующий:

=ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая»).

Кроме этого, есть возможность применять вместо функции ЕПУСТО

применять стандартную формулу, но указать, что при условии отсутствия никаких значений в ячейке.

ЕСЛИ –

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

Эта функция является фундаментом для некоторых других формул. Некоторые из них мы сейчас проанализируем более детально.

Создание формул со скобками

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

Допустим, у нас есть данные по продажам за 1 и 2 квартала, при этом цена товара оставалась неизменной. Узнать нужно общую сумму реализованного товара за 1-2 кварталы.

Для начала нужно сложить количество проданного товара в 1 и 2 кварталах, и далее умножить полученную сумму на цену за 1 штуку.

Произвести расчет можно сразу по одной формуле. По правилам математики, действие сложения нужно брать в скобки, иначе первым выполнится умножение (что даст неверный результат). В Excel действуют те же самые правила, и используются классические знаки скобок (открывающих и закрывающих).

1. Ставим курсор на результирующую ячейку (E2), пишем знак “=”, далее открываем скобку, в ней складываем ячейки B2 и C2, далее скобку закрываем, ставим знак умножения и, наконец, координаты ячейки D2. Так формула выглядит в конечном виде: =(B2+C2)*D2

Примечание: Стоит отметить, что вовсе не обязательно все ячейки, участвующие в формуле, располагать по соседству или на одном листе Excel. Пусть даже они будут на разных листах — результат получится правильный, если формула составлена корректно.

Использование вложенных формул в Excel

Что касается использования встроенных в Excel формул (функций), то на первый взгляд использование их путём вложения друг в друга невозможно. Такую точку зрения часто обосновывают тем, что при вставке формулы в ячейку программа показывает всплывающее окно, предназначенное для ручного или автоматического (при помощи мышки) выбора диапазонов ячеек, отдельных ячеек или отдельных значений (как числовых, так и текстовых). На скриншоте ниже приведён пример для вставки формулы вычисления среднего значения («СРЗНАЧ»).

Действительно, при использовании Мастера для вставки и редактирования формул Вы можете лишь создавать простые выражения. В большинстве случаев этого достаточно, но при создании комплексных конструкций вида «формула в формуле» в Excel следует применять иной подход, а именно: создавайте сложные выражения вручную при помощи строки формул, расположенной в панели инструментов.

Пример создания формулы в формуле в Excel

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

	А	B
1	10	60
2	20	70
3	30	80
4	40	90
5	50	100

Что нужно сделать: вычислим сумму средних значений обоих столбцов.

Если не использовать комплексные вычисления, то нам бы потребовалось целых три ячейки:

• В первой ячейке: вычислить среднее значение для колонки 1 (столбец А) при помощи функции СРЗНАЧ;
• Во второй колонке сделать то же самое, но для колонки 2 (столбец B);
• В третьей ячейке при помощи функции СУММ сложить два промежуточных результата;

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

Чтобы понять, как это сделать, нам необходимо рассмотреть синтаксис используемых «встроенных» формул Excel:

• Функция «СУММ»Применяется для сложения двух или более чисел или диапазонов чисел.Синтаксис: СУММ(X; Y; Z;)
• Функция «СРЗНАЧ»Применяется для вычисления среднего значения двух или более чисел или диапазонов чисел.Синтаксис: СРЗНАЧ(X; Y; Z;)

Здесь X, Y, Z — это или конкретные числа, указанные вручную, или адреса ячеек, или диапазон ячеек вида «A1:A10»

Обратите внимание, что при указании в качестве параметров диапазонов ячеек, аргумент у функций может быть всего один; при указании отдельных адресов ячеек или чисел — минимум два аргумента

В качестве аргументов встроенных формул разрешается использовать другие формулы, если они возвращают приемлемое для данной формулы значение (в данном случае это должно быть число).

А раз так, то мы можем составить комплексную формулу сразу для вычисления конечного результата. Для этого применим принцип Excel «формула в формуле». Вот что должно получиться для нашего примера с двумя колонками чисел:

СУММ(СРЗНАЧ(A1:A5); СРЗНАЧ(В1:В5))

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

Использование вложенных функций

В качестве примера будем использовать формулы с логическими условиями. Для этого нам нужно будет добавить какую-нибудь таблицу.

Затем придерживайтесь следующей инструкции:

1. Кликните на первую ячейку. Вызовите окно «Вставка функции». Выберите функцию «Если». Для вставки нажмите на «OK».

1. Затем нужно будет составить какое-нибудь логическое выражение. Его необходимо записать в первое поле. Например, можно сложить значения трех ячеек в одной строке и проверить, будет ли сумма больше 10. В случае «истины» указываем текст «Больше 10». Для ложного результата – «Меньше 10». Затем для возврата в рабочее пространство нажимаем на «OK».

1. В итоге мы видим следующее – редактор выдал, что сумма ячеек в третьей строке меньше 10. И это правильно. Значит, наш код работает.

=ЕСЛИ(СУММ(B3:D3)>10;»Больше 10″;»Меньше 10″)

1. Теперь нужно настроить и следующие клетки. В этом случае наша формула просто протягивается дальше. Для этого сначала необходимо навести курсор на правый нижний угол ячейки. После того как изменится курсор, нужно сделать левый клик и скопировать её до самого низа.

1. В итоге редактор пересчитывает наше выражение для каждой строки.

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

Во всём остальном всё выглядит практически точно так же. В качестве примера, посчитаем сумму нескольких ячеек. Для этого необходимо:

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

2. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

3. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

4. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Суммирование строк и столбцов

Шаг 1. Создаем ряд чисел в столбце и делаем активной ячейку суммирования.

Шаг 2. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 3. Щелкаем по первой ячейке ряда чисел, которые следует просуммировать, и протягиваем курсор вниз, то есть выделяем диапазон ячеек.

Шаг 1. Нажимаем Enter

Обратите внимание, что диапазон ячеек обозначен двоеточием «:» (Excel 5). Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма)

Эта кнопка продублирована на ленте Формулы

Есть более простой способ: воспользоваться кнопкой Автосумма (AutoSum) на ленте Главная (Лента Главная → группа команд Редактирование → команда Автосумма). Эта кнопка продублирована на ленте Формулы.

Если открыть выпадающее меню, то можно увидеть другие функции, которые полезно иметь под рукой.

Щелкаем ЛМ на первой пустой ячейке, находящейся ниже столбца или справа от строки, содержимое в ячейках которых нужно сложить.

Шаг 1. Нажимаем кнопку Автосумма на ленте Главная:

Диапазон ячеек, который суммируется, будет обведен «бегущей» границей, а в строке формул появится формула «=СУММ(С1:С4)».

Шаг 2. Нажимаем Enter

Но по жизни часто надо просуммировать расходы, которые записаны по разным столбцам (например, при определении общих семейных расходов). То есть необходимо узнать сумму по разным диапазонам.

Создаем ряд чисел по двум столбцам и делаем активной ячейку суммирования.

Шаг 1. Повторяем Шаги 1÷2 из пункта 4:

1. Знак «=» и набираем «сум»
2. Выбираем функцию «СУММА» из предложенного списка

Шаг 7. Выбираем первый диапазон (1), затем второй диапазон (2) простым перетаскиванием ЛМ:

Формула 1: ВПР

Эта функция дает возможность найти требуемую информацию в таблице, а возвращенный результат вывести в определенную ячейку. Аббревиатура, обозначающая название функции, расшифровывается, как «вертикальный просмотр».

Синтаксис

Это довольно сложная формула, имеющая 4 аргумента, а ее использование имеет множество особенностей.

Синтаксис такой:

=ВПР(искомое_значение;таблица;номер_столбца;)

Давайте более подробно рассмотрим все аргументы:

1. Значение, которое ищется.
2. Таблица. Необходимо, чтобы было искомое значение, находящееся в первой колонке, а также значение, которое возвращается. Последнее располагается где-угодно. Пользователь может самостоятельно определиться, куда вставлять результат формулы.
3. Номер столбца.
4. Интервальный просмотр. Если в этом нет необходимости, то можно не приводить значение этого аргумента. Оно являет собой логическое выражение, обозначающее степень точности совпадения, которое должно быть обнаружено функцией. Если указан параметр «Истина», то тогда Excel будет искать самое близкое значение к указанному в качестве искомого. Если же указан параметр «Ложь», то функция будет искать только те значения, которые находятся в первой колонке.

На этом скриншоте мы пробуем с помощью формулы понять, сколько просмотров было осуществлено по запросу «купить планшет».