Проведение расчетов

Основное назначение Excel — выполнение расчетов с данными. Обработка данных происходит в ячейках содержащих формулы. Правила ввода простейших формул вы уже изучили в начале раздела. В данном подразделе мы рассмотрим общие принципы создания формул любой сложности и изучим примеры типичных расчетов в Excel.

Ввод любой формулы всегда нужно начинать со знака равенства «=». В формуле могут находиться:

=D1^2+1 — возведение числа из ячейки D1 в квадрат и прибавление единицы к результату;

=СУММ(А1:А5) — суммирование значений из диапазона ячеек А1:А5. Это пример использования встроенной функции. Здесь СУММ — имя функции, А1:А5 — диапазон ячеек, ее единственный аргумент, заключенный в скобки;

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

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

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

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

Рис. 17. Просмотр назначения функции

Часто применяемые на практике функции вынесены в меню кнопки , которая находится в группе Редактирование на вкладке Главная. Рассмотрим задачи, связанные с их использованием.

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

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

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

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

1. Выделите щелчком ячейку, в которой нужно отобразить результат суммирования.

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

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

Рис. 18. Суммирование диапазона ячеек

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

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

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

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

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

Рис. 19. Результаты вычислений в строке состояния для выделенного диапазона

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

Задача 1. Выбрать оптимальный тарифный план при подключении к сети сотовой связи, если в месяц планируется 2,5 часа разговоров внутри сети и 0,5 часа разговоров с абонентами городской сети и других сотовых операторов. Цены на услуги представлены в таблице на рис. 20 без учета НДС.

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

Рис. 20. Таблица для расчета оптимальной стоимости услуг связи

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

Рис. 21. Готовая таблица расчета оптимального тарифного плана

Относительная и абсолютная адресация

Рассмотрим использование абсолютной адресации на конкретном примере.

Задача 2. Рассчитать конечную стоимость товара для оптового покупателя в зависимости от оговоренного размера скидки.

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

Рис. 23. Сообщение об ошибке, не распознанной Excel

В этом случае вернитесь к ячейке с формулой, перепроверьте ее и исправьте ошибку.

Рис. 24. Сообщение о распознанной ошибке

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

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

Второй способ. Вы можете просто записать эту функцию в ячейку так же, как вы это делаете обычно. Когда вы начинаете вводить имя, Excel покажет вам имя пользовательской в списке соответствующих функций. В приведенном ниже примере, когда я ввел = cou , Excel показал мне список подходящих функций, среди которых вы видите и CountWords.

Электронные таблицы Excel, стр. 1

Чтобы понять, что такое необязательный аргумент, вспомните функцию ВПР (VLOOKUP). Её четвертый аргумент [range_lookup] является необязательным. Если вы не укажете один из обязательных аргументов, получите ошибку. Но если вы пропустите необязательный аргумент, всё будет работать.

Дата	№ школы	Кол –во учащихся	Кол – во отличн.	Кол-во хорошистов	Кол – во троечн.	Кол – во двоечников
1 сентября	№ 30
1 сентября	№29
1 сентября	№40
1 сентября	№6

Формула суммы в Excel — несколько полезных советов и примеров

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

Как посчитать сумму одним кликом.

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

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

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

Что такое автосумма в таблице Excel?

Вторым по простоте способом является автосуммирование. Установите курсор в то место, где вы хотите увидеть расчет, а затем используйте кнопку «Автосумма» на вкладке Главная, или комбинацию клавиш ALT + =.

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

А если подходящих для расчета чисел рядом не окажется, то просто появится предложение самостоятельно указать область подсчета.

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

Кроме того, ALT + = можно просто нажать, чтобы быстро поставить формулу в ячейку Excel и не вводить ее руками. Согласитесь, этот небольшой хак в Экселе может сильно ускорить работу.

А если вам нужно быстро сосчитать итоги по вертикали и горизонтали, то здесь также может помочь автосуммирование. Посмотрите это короткое видео, чтобы узнать, как это сделать.

Под видео на всякий случай дано короткое пояснение.

Итак, выберите диапазон ячеек и дополнительно ряд пустых ячеек снизу и справа (то есть, В2:Е14 в приведенном примере).

Нажмите кнопку «Автосумма» на вкладке «Главная» ленты. Формула сложения будет сразу введена.

Ввод функции СУММ вручную.

Самый традиционный способ создать формулу в MS Excel – ввести функцию с клавиатуры. Как обычно, выбираем нужную ячейку и вводим знак =. Затем начинаем набирать СУММ. По первой же букве «С» раскроется список доступных функций, из которых можно сразу выбрать нужную.

Теперь определите диапазон с числами, которые вы хотите сложить, при помощи мыши или же просто введите его с клавиатуры. Если у вас большая область данных для расчетов, то, конечно, руками указать её будет гораздо проще (например, B2:B300). Нажмите Enter.

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

Правила использования формулы суммы в Excel (синтаксис):

Есть еще и необязательные аргументы (заключенные в квадратные скобки): [число2], ..

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

К примеру, в выражении =СУММ(D2:D13) только один аргумент — ссылка на ячейки D2:D13.

Кстати, в этом случае также имеется возможность воспользоваться автосуммированием.

Накопительное значение, или нарастающий итог.

Задача: По прошествии каждого месяца нужно автоматически определять прибыль, заработанную с начала года. Каждый месяц она будет меняться в зависимости от достигнутого результата.

Посмотрите это небольшое видео, а далее поясним все наши действия.

Обратите внимание, что мы использовали в первом адресе абсолютную ссылку на строку 2. То есть, при копировании ячейки с формулой мы всегда будем начинать со 2-й строки.

Устанавливаем курсор в правый нижний угол ячейки и «протаскиваем» до С13. В результате наша формула будет скопирована по всему выделению. И при этом начальный адрес B$2 не изменится. К примеру, в С13 это выглядит так: =СУММ(B$2:B13).

Как видите, нарастающий итог то увеличивается, то уменьшается в зависимости от результата прошедшего месяца.

Использование для проверки введенных значений.

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

Посмотрите это видео, в котором используется инструмент «Проверка данных».

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

В этом случае нам нужно условие, которое возвращает ЛОЖЬ до тех пор, пока затраты в смете меньше 3500. Мы используем функцию сложения для обработки фиксированного диапазона, а затем просто сравниваем результат с 3500.

Каждый раз, когда записывается число, запускается проверка. Пока результат остается меньше 3500, проверка успешна. Если какое-то действие приводит к тому, что он превышает 3500, проверка завершается неудачно и выводится сообщение об ошибке.

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

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

Сумма по цвету и подсчёт по цвету в Excel — В этой статье вы узнаете, как посчитать ячейки по цвету и получить сумму по цвету ячеек в Excel. Эти решения работают как для окрашенных вручную, так и с условным форматированием. Если…

Формула ПРОМЕЖУТОЧНЫЕ ИТОГИ — основные функции с примерами. — В статье объясняются особенности функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel и показано, как использовать формулы промежуточных итогов для суммирования данных в видимых ячейках. В предыдущей статье мы обсудили автоматический способ вставки промежуточных…

Промежуточные итоги в Excel — В руководстве объясняется, как использовать инструмент промежуточных итогов Excel для автоматического суммирования, подсчета или усреднения различных групп ячеек. Вы также узнаете, как отображать или скрывать детали промежуточных итогов, копировать только строки…

Как посчитать количество пустых и непустых ячеек в Excel — Если ваша задача — заставить Excel подсчитывать пустые ячейки на листе, прочтите эту статью, чтобы найти 3 способа для этого. Узнайте, как искать и выбирать среди них нужные с помощью стандартных…

Как сравнить два столбца на совпадения и различия — На прочтение этой статьи у вас уйдет около 10 минут, а в следующие 5 минут (или даже быстрее) вы легко сравните два столбца Excel на наличие дубликатов и выделите найденные…

Функция СЧЁТЕСЛИМН в Excel с несколькими условиями — объясняем на примерах. — В этом руководстве объясняется, как использовать функцию СЧЕТЕСЛИМН с несколькими критериями в Excel на основе логики И и ИЛИ. Вы найдете примеры для разных типов данных — числа, даты, текст,…

СЧЕТЕСЛИ в Excel — примеры функции с одним и несколькими условиями — В этой статье мы сосредоточимся на функции Excel СЧЕТЕСЛИ (COUNTIF в английском варианте), которая предназначена для подсчета ячеек с определённым условием. Сначала мы кратко рассмотрим синтаксис и общее использование, а затем я…

Функция СУММЕСЛИМН — как суммировать ячейки в Excel, когда много условий? — В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями…

7 примеров использования формулы СУММЕСЛИ в Excel с несколькими условиями — В таблицах Excel можно не просто находить сумму чисел, но и делать это в зависимости от заранее определённых критериев отбора. Мы рассмотрим, как правильно применить функцию СУММЕСЛИ (Sumif) в таблицах…

8 простых способов как посчитать в Excel сумму столбца — Как посчитать сумму в Excel быстро и просто? Чаще всего нас интересует итог по столбцу либо строке. Попробуйте различные способы найти сумму по столбцу, используйте функцию СУММ или же преобразуйте…

Теперь, когда вы знаете, как создать формулу промежуточных итогов в Excel, главный вопрос: зачем вообще тратить силы на ее изучение? Почему бы просто не использовать обычные функции, такие как СУММ, СЧЁТ, МАКС и т.д.? Вы найдете ответ ниже.

Как создать пользовательскую функцию в Excel? | Mister-Office

Почему пользовательская функция не работает: проблемы и решения — Если существующих функций недостаточно, Excel позволяет добавить новые собственные функции. Мы ранее рассказали, как их создать и как использовать, чтобы ваша работа стала проще. В этой статье мы рассмотрим проблемы,…

Рис. 21. Готовая таблица расчета оптимального тарифного плана

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

Проведение расчетов

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

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

Как сделать формулу в Excel: пошаговая инструкция

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

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

Формула суммы в Excel — несколько полезных советов и примеров | Mister-Office

1. Те же манипуляции можно провести для каждого товара или же скопировать первую формулу и вставить ее в остальные ячейки по очереди. Но все делается проще: кликнуть на ячейку, в правом нижнем углу появится маркер заполнения, нажать на него и, не отпуская кнопку мышки, потянуть вниз.

В этом случае нам нужно условие, которое возвращает ЛОЖЬ до тех пор, пока затраты в смете меньше 3500. Мы используем функцию сложения для обработки фиксированного диапазона, а затем просто сравниваем результат с 3500.