Математические расчеты в excel. Расчет NPV в Excel (пример)

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

Правила ввода формул

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

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

А2+В2 - сложение значений двух ячеек;

А1*0,8 - умножение числа из ячейки А1 на 0,8;

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

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

МУМНОЖ(B1:B2;B7:C7) - вычисление произведения матриц B1:B2 и B7:C7. Как видно, данная функция имеет два аргумента, которые являются массивами данных из выделенных диапазонов. Если функция имеет несколько аргументов, они отделяются друг от друга точкой с запятой. В качестве аргументов функций вы можете использовать ссылки на ячейки и диапазоны на текущем листе и на других листах. В последнем случае перед адресом ячейки или диапазона следует ввести название листа, отделенное штрихами, и поставить разделитель «!», например ‘Лист1’!В2, ‘Лист 3’!А1:С4. Штрих можно ввести, нажав клавишу «Э» при активной английской раскладке.

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

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

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

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

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

Простейшие расчеты

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

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

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

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

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

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

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

4. Нажмите для получения результата.

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

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

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

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

3. Нажмите для подсчета нового результата.

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

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

Комплексные расчеты

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

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

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

Выделите ячейку А1 и наберите в ней название работы. В ячейках следующей строки создайте заголовки таблицы. Воспользуйтесь кнопкой Перенос текста из группы Выравнивание, чтобы отрегулировать высоту строки. Ширину столбцов подберите вручную перетаскиванием границ их заголовков. С помощью кнопок выравнивания поместите текст в «шапке» посередине (относительно верхней и нижней границы ячейки) и по центру (относительно левой и правой границ).

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

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

Суммарные затраты будут складываться из абонентской платы, стоимости разговоров внутри сети и с абонентами других сетей, а также из налога на добавленную стоимость. Это нужно представить в виде формулы. Начнем расчеты с первого тарифного плана Минутка. К содержимому ячейки В3 (абонплата) нужно прибавить произведение ячеек С3 и Е3 (общая стоимость разговоров внутри сети) и произведение ячеек D3 и F3 (общая стоимость разговоров с абонентами других сетей). Так будет подсчитана сумма затрат без учета НДС. Чтобы прибавить к полученной сумме налоговую ставку, необходимо умножить результат на 18% и прибавить его к стоимости услуг. Это результат и должен отобразиться в ячейках последнего столбца.

Выделите ячейку G3, наберите в ней знак «=» и нажмите кнопку . При этом будет вставлена функция суммирования, автоматически определен диапазон ячеек, а его адрес будет выделен в скобках функции. Поскольку данный диапазон нам не подходит, нажмите, чтобы удалить ссылку из скобок. Затем выполните щелчок на ячейке В3, чтобы поместить ее адрес в формулу, и поставьте «;» для отделения следующего аргумента (слагаемого). Далее щелкните на ячейке С3 для ввода ссылки на нее в формулу, наберите знак умножения «*» и выделите ячейку Е3. Отделите новый аргумент точкой с запятой. Для ввода последнего слагаемого щелкните на ячейке D3, введите «*» и выделите ячейку F3. Так мы создали часть формулы, которая отвечает за подсчет суммарных затрат без учета налогов. Она должна выглядеть следующим образом: =СУММ(B3;C3*E3;D3*F3). Если вы где-то допустили ошибку, установите курсор возле нее щелчком мыши или с помощью клавиш движения и внесите исправления. Далее нужно прибавить налоговую часть формулы. Переместите курсор в конец за скобку и наберите «+». Ставка НДС составляет 18%. Следовательно, нужно умножить 18% на результат, полученный в первой части формулы. Наберите 18% и знак умножения «*», затем выделите первую часть формулы, не включая знака «=» (точно так же, как вы бы выделили текстовый фрагмент в Word), выполните на ней правый щелчок и задействуйте команду Копировать контекстного меню. Щелчком установите курсор в конец формулы, выполните правый щелчок и обратитесь к команде Вставить. Расчетная формула готова: =СУММ(B3;C3*E3;D3*F3)+18%*СУММ(B3;C3*E3;D3*F3). Нажмите, чтобы увидеть итоговую сумму.

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

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

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

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

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

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

Адреса ячеек и диапазонов в Excel могут быть относительными и абсолютными. До сих пор мы говорили об относительных ссылках на ячейки и диапазоны, которые состоят только из номера строки и буквы столбца, например В2 или D4:D8. Преимущество относительной адресации состоит в том, что при копировании ячеек и использовании автозаполнения ссылки в скопированных формулах меняются автоматически (относятся к ячейкам текущей, а не исходной строки), поэтому нет необходимости набирать вручную каждую формулу. Наглядный пример: в предыдущем примере мы набрали только одну формулу в первой ячейке столбца Суммарно с учетом НДС 18%, а затем воспользовались автозаполнением. Однако на практике встречаются ситуации, когда адрес ячейки или диапазона необходимо зафиксировать, чтобы он не изменялся при копировании или автозаполнении ячеек. Для этого необходимо добавить перед номером строки и буквой столбца знак «$». Так, если сделать адрес ячейки В2 абсолютным, он будет выглядеть как $B$2. Также можно зафиксировать в ссылке адрес только столбца ($B2) или только строки (B$2). Это называется смешанной адресацией. Чтобы быстро изменить адресацию в готовой формуле, выполните по ней двойной щелчок, установите курсор на нужную ссылку и последовательно нажимайте клавишу для изменения типа адреса. Знак «$» можно добавлять в формулы и вручную с клавиатуры.

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

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

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

Чтобы рассчитать цену с учетом скидки, необходимо от текущей цены отнять процент скидки. В нашем примере он равняется десяти. На первый взгляд решение задачи очень похоже на предыдущее, где прибавлялась процентная ставка НДС. Глядя на рис. 22, можно предположить, что в ячейке C4 необходимо из цены в ячейке В4 вычесть скидку, которая представляет собой произведение размера скидки из ячейки С1 на цену товара (В4). В виде формулы это запишется как =В4–С1*В4. Останется только распространить формулу на остальные ячейки столбца с помощью автозаполнения. Попробуйте выполнить описанные действия, и в конечном итоге вы обнаружите, что в вычислениях произошла ошибка. А заключается она в следующем.

При использовании автозаполнения ссылки в ячейках автоматически изменяются по мере копирования формул. В нашем случае формула, введенная в первую ячейку прайса, была правильной, но при попытке распространить ее на остальные ячейки столбца ссылка на ячейку с размером скидки стала меняться «сползая вниз» (превращаясь в С2, С3 и т.д.). Чтобы этого не происходило, ее адрес необходимо зафиксировать - сделать абсолютным. Для этого выполните на первой ячейке столбца скидки двойной щелчок (С4), поместите курсор на адрес ячейки, где находится величина скидки (в нашем случае это ячейка С1) и нажмите клавишу. При этом к номеру строки и букве столбца будет добавлен знак $ ($C$1), и адрес ячейки станет абсолютным - не изменится при автозаполнении столбца. В результате конечная формула будет выглядеть так: =В4–$C$1*В4. Теперь можно повторить процедуру автозаполнения, чтобы получить корректный результат. Щелкните на любой ячейке столбца скидок, чтобы убедиться в неизменности абсолютной ссылки. При изменении величины скидки весь ряд будет автоматически пересчитан.

Гораздо нагляднее использовать вместо абсолютной ссылки, содержащей знаки «$», имя ячейки, которое можно присвоить следующим образом: выделить щелчком ячейку и слева в строке формул набрать для нее уникальное имя. Щелкните на ячейке С1 (где указан размер скидки), слева в строке формул наберите Скидка. Затем в первой ячейке столбца цены со скидкой исправьте абсолютную ссылку $C$1 на имя ячейки Скидка. В результате должна получиться формула =В4–Скидка*В4. Осталось распространить формулу на все ячейки столбца с помощью автозаполнения.

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

Ошибки в формулах

При работе с формулами в Excel нередко возникают ошибки, связанные не только с правильностью написания формулы, но и с корректным определением адресов ячеек и диапазонов с данными. Проведем краткий обзор способов выявления и устранения ошибок. Если Excel обнаружит ошибку в синтаксисе формулы (например, если между аргументами функции будет отсутствовать разделитель «;», будут пропущены или поставлены лишние скобки, обнаружится меньше аргументов, чем того требуется для данной функции), он выдаст сообщение об ошибке. Текст сообщения будет различным в зависимости от того, удалось Excel определить источник ошибки или нет. Когда система не может определить источник ошибки, появляется сообщение, представленное на рис. 23.

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

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

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

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

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

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

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

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

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

Подробная информация о базовых функциях и возможностях табличного процессора MS Excel. Описание основных элементов документа и инструкция для работы с ними в нашем материале.

Работа с ячейками. Заполнение и форматирование

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

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

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

Рис. 1 – пример заполнения ячеек

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

Выберем ячейку «Доля акций». Адрес её расположения – А3. Эта информация указана в открывшейся панели свойств. Также мы можем увидеть содержимое. Формул у этой клетки нет, поэтому они не показываются.

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

Рис. 2 – контекстное меню ячейки и ее основные свойства

Сортировка данных

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

Перед вами уже заполненная таблица (как её создать разберемся дальше в статье). Представьте, что вам нужно отсортировать данные за январь по возрастанию. Как бы вы это сделали? Банальное перепечатывание таблицы – это лишняя работа, к тому же, если она объемная, делать этого никто не будет.

Для сортировки в Excel есть специально отведенная функция. От пользователя требуется только:

• Выделить таблицу или блок информации;
• Открыть кладку «Данные»;
• Кликнуть на иконку «Сортировка»;

Рис. 3 – вкладка «Данные»

• В открывшемся окошке выберите колонку таблицы, над которой будем проводить действия (Январь).
• Далее тип сортировки (мы выполняем группировку по значению) и, наконец, порядок – по возрастанию.
• Подтвердите действие, нажав на «ОК».

Рис. 4 – настройка параметров сортировки

Произойдет автоматическая сортировка данных:

Рис. 5 – результат сортировки цифр в столбце «Январь»

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

Математические расчеты

Главное преимущество Excel – возможность автоматического проведения расчетов в процессе заполнения таблицы. К примеру, у нас есть две ячейки со значениями 2 и 17. Как в третью ячейку вписать их результат, не делая расчеты самостоятельно?

Для этого, вам необходимо кликнуть на третью ячейку, в которую будет вписан конечный результат вычислений. Затем нажмите на значок функции f(x), как показано на рисунке ниже. В открывшемся окошке выберите действие, которое хотите применить. СУММ – это сумма, СРЗНАЧ – среднее значение и так далее. Полный список функций и их наименований в редакторе Excel можно найти на официальном сайте компании Microsoft.

Нам нужно найти сумму двух ячеек, поэтому нажимаем на «СУММ».

Рис. 6 – выбор функции «СУММ»

В окне аргументов функции есть два поля: «Число 1» и «Число 2». Выберите первое поле и кликните мышкой на ячейку с цифрой «2». Её адрес запишется в строку аргумента. Кликните на «Число 2» и нажмите на ячейку с цифрой «17». Затем подтвердите действие и закройте окно. Если необходимо выполнить математические действия с тремя или большим количеством клеточек, просто продолжайте вводить значения аргументов в поля «Число 3», «Число 4» и так далее.

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

Рис. 7 – результат выполнения подсчетов

Создание таблиц

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

Таблицы в Excel имеют преимущество перед аналогичной опцией в Word и других офисных программах. Здесь у вас есть возможность создать таблицу любой размерности. Данные заполняются легко. Присутствует панель функций для редактирования содержимого. К тому же, готовую таблицу можно интегрировать в файл docx с помощью обычной функции копирование-вставка.

Чтобы создать таблицу, следуйте инструкции:

• Откройте вкладку «Вставка». В левой части панели опций выберите пункт «Таблица». Если вам необходимо провести сведение каких-либо данных, выбирайте пункт «Сводная таблица»;
• С помощью мышки выделите место на листе, которое будет отведено для таблицы. А также вы можете ввести расположение данных в окно создания элемента;
• Нажмите ОК, чтобы подтвердить действие.

Рис. 8 – создание стандартной таблицы

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

Рис. 9 – форматирование таблицы

Результат заполнения таблицы данными:

Рис. 10 – заполненная таблица

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

Добавление графиков/диаграмм

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

Чтобы создать диаграмму/график, нужно:

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

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

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

Тематические видеоролики:

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

Исходные данные: ежемесячные объёмы продаж товара в 2015 году, стоимость единицы товара, выручка за 2014 год помесячно. Приступаем к расчетам.

Как создать документ Эксель

Если Excel у вас еще не запущен – нажмите Пуск — Все программы — Microsoft Office , и в открывшейся папке – Excel . Программа будет запущена. В открывшемся окне выберите Пустая книга .

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

Если программа уже запущена – нажмите комбинацию клавиш Ctrl+N для создания новой рабочей книги.

Заполняем лист Excel данными

В результате, перед вами новый пустой документ с активным листом «Лист 1» . Давайте заполним его исходными данными. Начнём с шапки, т.е. определимся со структурой таблицы. Пусть в первом столбце будут месяцы года.

• Кликните левой кнопкой мыши в ячейку А1, она будет активизирована для заполнения. Напишите: «Месяц»
• Аналогично в колонке В1 запишем «Выручка 2015»
• С1 – «Продажи»
• D1 – «Цена»
• E1 – «Выручка 2014»

Шапка готова.

Теперь напишем названия месяцев в столбце А. Установите курсор в ячейку А2, напишите «Январь» и нажмите Enter . Табличный курсор перенесётся на одну строчку вниз. Чтобы заполнить последующие месяцы – воспользуемся функцией Эксель – автозаполнение. Верните курсор в ячейку А2 и наведите мышью на квадратик в нижнем правом углу активной клетки. При этом, курсор превратится в чёрный крестик. Зажмите левую кнопку мыши и тяните курсор вниз, как бы растягивая надпись «Январь» вниз. Эксель автоматически будет записывать новые месяца в каждой следующей строчке.

Пока вы не отпустили мышь – программа показывает подсказку о том, какой месяц будет в соответствующей ячейке. Посмотрите, на рисунке я дотянул список месяцев до мая. Вы же протяните курсор на 11 строчек вниз, на подсказке отобразится «декабрь». Отпускайте мышь и «вуа-ля», одним взмахом руки мы прописали целых 12 месяцев!

Теперь заполним количества и цены. Поставим курсор в клетку С2, запишем количество и нажмём Ввод на клавиатуре. Выделение переместится в С3, там тоже запишем количество. Так, заполняем все продажи до декабря (С13). Аналогично поступим с ценами в столбце D. В нашем примере цены содержат копейки. В русифицированной версии Excel они отделаются от целой части запятой. В оригинальной версии – точкой. Еще заполним выручку 2014 года. Вот так будет выглядеть заполненная таблица данных:

Расчеты в Эксель

Важно! Все формулы в Excel начинаются со знака «=» . Если первый символ в ячейке – этот знак, программа понимает, что дальше будет следовать вычисляемая формула.

Активизируем ячейку B2 и вычислим выручку в январе 2015 года. Напишем «=» и кликнем левой кнопкой мыши на ячейку С2, задав ссылку на продажи января. Дальше ставим знак умножения «*» и кликаем на D2 – ссылка на стоимость товара. Жмём Enter и видим, что в выделенной ячейке отобразился результат расчётов, а в строке формул для этой ячейки – введенная нами формула. Excel извлечёт значение из С2 и умножит его на значение в D2. Если изменить любое из них – формула сразу же будет пересчитана. Больше информации о правилах расчета я написал в статьях и . Там всё это расписано более детально.

Давайте протянем эту формулу на все строки таблицы, как мы делали с заполнением месяцев. Тянем за квадратик в правом нижнем углу ячейки с формулой. Результат – программа просчитала выручку для всех месяцев. Обратите внимание, при протягивании изменяются адреса ссылок в формуле. Например, в ячейке В12 у нас формула: =C12*D12 , а не =C2*D2 . Это потому, что у нас по умолчанию применились .

Теперь вычислим разницу между выручкой 2014 и 2015 годов. Назовём столбец F – «Разница». Методика написания формул не меняется, только вычислим =В2-Е2 , протянем формулу для всех месяцев. Видим, например, что в январе 2015 года наша выручка на 7,5 ед. меньше, чем в 2014.

Графики Excel

Осталась последняя задача – построить временную диаграмму и визуально оценить динамику продаж (более детально вопрос диаграмм я рассмотрел в ).

Чтобы построить график, нужно выделить диапазон (почитайте ) данных, на основании которых будет выстроена кривая. Наведите курсор мышки на ячейку А2. Зажмите левую кнопку и тяните курсор к ячейке В13. После этого отпустите кнопку мыши. Если вы всё сделали правильно, диапазон А2:В13 будет выделен тёмным цветом.

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

Сохраняем расчеты

Чтобы сохранить наш документ – нажмите комбинацию клавиш Ctrl+S , в открывшемся окне нажмите Обзор . Открывается стандартное окно сохранения, где нужно указать расположение файла и задать его имя. Нажатием кнопки Сохранить, подтверждаем создание нового файла.

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

Всегда ваш, Александр Томм.

Практическая работа № 20

Выполнение расчетов в Excel

Цель: научиться выполнять расчеты в Excel

Сведения из теории

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

+ сложение / деление * умножение

− вычитание ^ возведение в степень

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

Примеры записи формул:

=(А5+В5)/2

=С6^2* (D 6- D 7)

В формулах используют относительные, абсолютные и смешанные ссылки на адреса ячеек. Относительные ссылки при копировании формулы изменяются. При копировании формулы знак $ замораживает: номер строки (А$2 - смешанная ссылка), номер столбца ($F25- смешанная ссылка) или то и другое ($A$2- абсолютная ссылка).

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

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

Автозаполнение ячеек формулами:

ввести формулу в первую ячейку и нажать Enter;

выделить ячейку с формулой;

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

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

установить курсор в ячейку, где будет осуществляться вычисление;

раскрыть список команд кнопки Сумма (рис. 20.1) и выбрать нужную функцию. При выборе Другие функции вызывается Мастер функций.

Рисунок 20.1 – Выбор функции

Задания для выполнения

Задание 1 Подсчет котировок курса доллара

В папке с названием своей группы создайте документ MS Excel, имя задайте по номеру практической работы.

Лист 1 назовите по номеру задания;

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

Рисунок 20.2 – Образец таблицы (Лист 1)

Выполните расчет дохода в ячейке D4. Формула для расчета: Доход = Курс продажи – Курс покупки

Сделайте автозаполнение ячеек D5:D23 формулами.

В столбце «Доход» задайте Денежный (р) формат чисел;

Отформатируйте таблицу.

Сохраните книгу.

Задание 2 Расчет суммарной выручки

Лист 2.

Создайте таблицу расчета суммарной выручки по образцу, выполните расчеты (рис. 20.3).

В диапазоне B3:E9 задайте денежный формат с двумя знаками после запятой.

Формулы для расчета:

Всего за день = Отделение 1 + Отделение 2 + Отделение 3;

Итого за неделю = Сумма значений по каждому столбцу

Отформатируйте таблицу. Сохраните книгу.

Рисунок 20.3 – Образец таблицы (Лист 2)

Задание 3 Расчет надбавки

В этом же документе перейдите на Лист 3. Назовите его по номеру задания.

Создайте таблицу расчета надбавки по образцу (рис. 20.4). Колонке Процент надбавки задайте формат Процентный , самостоятельно задайте верные форматы данных для колонок Сумма зарплаты и Сумма надбавки.

Рисунок 20.4 - Образец таблицы (Лист 3)

Выполните расчеты.

Формула для расчета

Сумма надбавки = Процент надбавки * Сумма зарплаты

После колонки Сумма надбавки добавьте еще одну колонку Итого . Выведите в ней итоговую сумму зарплаты с надбавкой.

Введите: в ячейку Е10 Максимальная сумма надбавки, в ячейку Е11 − Минимальная сумма надбавки. В ячейку Е12 − Средняя сумма надбавки.

В ячейки F10:F12 введите соответствующие формулы для вычисления.

Сохраните книгу.

Задание 4 Использование в формулах абсолютных и относительных адресов

В этом же документе перейдите на Лист 4. Назовите его по номеру задания.

Введите таблицу по образцу (рис. 20.5), задайте шрифт Bodoni MT, размер 12, остальные настройки форматирования − согласно образцу. Ячейкам С4:С9 задайте формат Денежный, обозначение «р.», ячейкам D4:D9 задайте формат Денежный, обозначение «$».

Рисунок 20.5 - Образец таблицы (Лист 4)

Вычислите Значения ячеек, помеченных знаком “?”

Сохраните книгу.

Контрольные вопросы

Как осуществляется ввод формул в Excel?

Какие операции можно использовать в формулах, как они обозначаются?

Как сделать автозаполнение ячеек формулой?

Как вызвать Мастер функций?

Какие функции вы использовали в этой практической работе?

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

Как формируется адрес ячейки?

Где в работе вы использовали абсолютные адреса, а где – относительные?

Какие форматы данных вы использовали в таблицах этой работы?

Здравствуйте!

Многие кто не пользуются Excel - даже не представляют, какие возможности дает эта программа! Подумать только: складывать в автоматическом режиме значения из одних формул в другие, искать нужные строки в тексте, складывать по условию и т.д. - в общем-то, по сути мини-язык программирования для решения "узких" задач (признаться честно, я сам долгое время Excel не рассматривал за программу, и почти его не использовал)...

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

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

Примечание : все скриншоты ниже представлены из программы Excel 2016 (как самой новой на сегодняшний день).

Многие начинающие пользователи, после запуска Excel - задают один странный вопрос: "ну и где тут таблица?". Между тем, все клеточки, что вы видите после запуска программы - это и есть одна большая таблица!

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

• слева : в ячейке (A1) написано простое число "6". Обратите внимание, когда вы выбираете эту ячейку, то в строке формулы (Fx) показывается просто число "6".
• справа : в ячейке (C1) с виду тоже простое число "6", но если выбрать эту ячейку, то вы увидите формулу "=3+3" - это и есть важная фишка в Excel!

Просто число (слева) и посчитанная формула (справа)

Суть в том, что Excel может считать как калькулятор, если выбрать какую нибудь ячейку, а потом написать формулу, например "=3+5+8" (без кавычек). Результат вам писать не нужно - Excel посчитает его сам и отобразит в ячейке (как в ячейке C1 в примере выше)!

Но писать в формулы и складывать можно не просто числа, но и числа, уже посчитанные в других ячейках. На скриншоте ниже в ячейке A1 и B1 числа 5 и 6 соответственно. В ячейке D1 я хочу получить их сумму - можно написать формулу двумя способами:

• первый: "=5+6" (не совсем удобно, представьте, что в ячейке A1 - у нас число тоже считается по какой-нибудь другой формуле и оно меняется. Не будете же вы подставлять вместо 5 каждый раз заново число?!);
• второй: "=A1+B1" - а вот это идеальный вариант, просто складываем значение ячеек A1 и B1 (несмотря даже какие числа в них!)

Сложение ячеек, в которых уже есть числа

Распространение формулы на другие ячейки

В примере выше мы сложили два числа в столбце A и B в первой строке. Но строк то у нас 6, и чаще всего в реальных задачах сложить числа нужно в каждой строке! Чтобы это сделать, можно:

1. в строке 2 написать формулу "=A2+B2" , в строке 3 - "=A3+B3" и т.д. (это долго и утомительно, этот вариант никогда не используют);
2. выбрать ячейку D1 (в которой уже есть формула), затем подвести указатель мышки к правому уголку ячейки, чтобы появился черный крестик (см. скрин ниже). Затем зажать левую кнопку и растянуть формулу на весь столбец. Удобно и быстро! (Примечание : так же можно использовать для формул комбинации Ctrl+C и Ctrl+V (скопировать и вставить соответственно)).

Кстати, обратите внимание на то, что Excel сам подставил формулы в каждую строку. То есть, если сейчас вы выберите ячейку, скажем, D2 - то увидите формулу "=A2+B2" (т.е. Excel автоматически подставляет формулы и сразу же выдает результат) .

Как задать константу (ячейку, которая не будет меняться при копировании формулы)

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

Далее в ячейке E2 пишется формула "=D2*G2" и получаем результат. Только вот если растянуть формулу, как мы это делали до этого, в других строках результата мы не увидим, т.к. Excel в строку 3 поставит формулу "D3*G3", в 4-ю строку: "D4*G4" и т.д. Надо же, чтобы G2 везде оставалась G2...

Чтобы это сделать - просто измените ячейку E2 - формула будет иметь вид "=D2*$G$2". Т.е. значок доллара $ - позволяет задавать ячейку, которая не будет меняться, когда вы будете копировать формулу (т.е. получаем константу, пример ниже)...

Как посчитать сумму (формулы СУММ и СУММЕСЛИМН)

Можно, конечно, составлять формулы в ручном режиме, печатая "=A1+B1+C1" и т.п. Но в Excel есть более быстрые и удобные инструменты.

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

1. сначала выделяем ячейки (см. скрин ниже);
2. далее открываем раздел "Формулы" ;
3. следующий шаг жмем кнопку "Автосумма" . Под выделенными вами ячейками появиться результат из сложения;
4. если выделить ячейку с результатом (в моем случае - это ячейка E8 ) - то вы увидите формулу "=СУММ(E2:E7)" .
5. таким образом, написав формулу "=СУММ(xx)" , где вместо xx поставить (или выделить) любые ячейки, можно считать самые разнообразные диапазоны ячеек, столбцов, строк...

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

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

1. "=СУММЕСЛИМН(F2:F7 ;A2:A7 ;"Саша") " - (прим .: обратите внимание на кавычки для условия - они должны быть как на скрине ниже, а не как у меня сейчас написано на блоге) . Так же обратите внимание, что Excel при вбивании начала формулы (к примеру "СУММ..."), сам подсказывает и подставляет возможные варианты - а формул в Excel"e сотни!;
2. F2:F7 - это диапазон, по которому будут складываться (суммироваться) числа из ячеек;
3. A2:A7 - это столбик, по которому будет проверяться наше условие;
4. "Саша" - это условие, те строки, в которых в столбце A будет "Саша" будут сложены (обратите внимание на показательный скриншот ниже).

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

Как посчитать количество строк (с одним, двумя и более условием)

Довольно типичная задача: посчитать не сумму в ячейках, а количество строк, удовлетворяющих какомe-либо условию. Ну, например, сколько раз имя "Саша" встречается в таблице ниже (см. скриншот). Очевидно, что 2 раза (но это потому, что таблица слишком маленькая и взята в качестве наглядного примера). А как это посчитать формулой? Формула:

"=СЧЁТЕСЛИ(A2:A7 ;A2 ) " - где:

• A2:A7 - диапазон, в котором будут проверяться и считаться строки;
• A2 - задается условие (обратите внимание, что можно было написать условие вида "Саша", а можно просто указать ячейку).

Результат показан в правой части на скрине ниже.

Теперь представьте более расширенную задачу: нужно посчитать строки где встречается имя "Саша", и где в столбце И - будет стоять цифра "6". Забегая вперед, скажу, что такая строка всего лишь одна (скрин с примером ниже).

Формула будет иметь вид:

=СЧЁТЕСЛИМН(A2:A7 ;A2 ;B2:B7 ;"6") (прим.: обратите внимание на кавычки - они должны быть как на скрине ниже, а не как у меня) , где:

A2:A7 ;A2 - первый диапазон и условие для поиска (аналогично примеру выше);

B2:B7 ;"6" - второй диапазон и условие для поиска (обратите внимание, что условие можно задавать по разному: либо указывать ячейку, либо просто написано в кавычках текст/число).

Как посчитать процент от суммы

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

Самый простой способ, в котором просто невозможно запутаться - это использовать правило "квадрата", или пропорции. Вся суть приведена на скрине ниже: если у вас есть общая сумма, допустим в моем примере это число 3060 - ячейка F8 (т.е. это 100% прибыль, и какую то ее часть сделал "Саша", нужно найти какую...).

По пропорции формула будет выглядеть так: =F10*G8/F8 (т.е. крест на крест: сначала перемножаем два известных числа по диагонали, а затем делим на оставшееся третье число). В принципе, используя это правило, запутаться в процентах практически невозможно .

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