Математические вычисления в excel. Формулы и адреса ячеек. Оператор объединения текста

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

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

Формулы в Excel для чайников

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

В Excel применяются стандартные математические операторы:

Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.

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

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

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

Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.

В нашем примере:

1. Поставили курсор в ячейку В3 и ввели =.
2. Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
3. Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.

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

• %, ^;
• *, /;
• +, -.

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

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

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

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

Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.

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

Формулы и адреса ячеек

Формулой называется равенство, с помощью которого можно непосредственно в таблице производить сложение, вычитание, умножение и деление значений. Чтобы сделать вычисления более удобными, следует хорошо знать понятие «адрес ячейки». Адресом называется обозначение отдельной ячейки таблицы. На рисунке присутствует небольшая таблица, состоящая из шести строк и четырех столбцов. Каждая ячейка имеет свой адрес. Он обозначается латинской буквой и цифрой. Например, ячейка «Молоко» имеет адрес «А4». То есть «А» — это номер столбца, а «4» — номер строки.

Применение формул при расчете

Теперь можно приступать к изучению формул. В состав формулы входит знак равенства, « + », « — », « / » и «*». Чтобы создать какое-либо уравнение, требуется выбрать ячейку и написать в ней функцию. Например, если нужно посчитать общую стоимость четырех булок хлеба, то ставим курсор на ячейку D3 и пишем знак «равно». Далее следует написать формулу из адресов ячеек или просто числа со знаком умножения. Рассмотрим самый простой способ – нахождение общей стоимости по известным числам. Для этого вписываем в ячейку «=4*15» и нажимаем «Enter».

Теперь разберем универсальный метод нахождения различных значений. При использовании адресов ячеек при написании формулы можно менять значения в других клетках. При этом Excel будет автоматически пересчитывать значение в той графе, где написана формула. Для этого нужно вместо числовых значений вписать в ячейку D3 адресные данные. Количество хлеба имеет адрес «B3», а цена одной булки – «С3». Теперь вписываем эти адреса в графу, где будет производиться вычисление.

По мере написания формулы происходит выделение ячеек. Это позволит правильно указать адреса значений. Еще одним важным преимуществом создания формулы с использованием наименований строк и столбцов является расчет сразу всех значений. То есть, если вписать формулу в ячейку «D3» и нажать «Enter», то Excel автоматически произведет расчет общей стоимости всех остальных продуктов.

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

Основное назначение 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). Если был указан неправильный адрес, перетащите рамку на нужную ячейку (или измените ее размеры протаскиванием маркера при увеличении или уменьшении диапазона).

Последнее десятилетие компьютер в бухгалтерии стал просто незаменимым инструментом. При этом его применение разнопланово. В первую очередь это, конечно, использование бухгалтерской программы. На сегодняшний день разработано довольно много программных средств, как специализированных («1С», «Инфо-Бухгалтер», «БЭСТ» и т. д.), так и универсальных, подобно Microsoft Office. На работе, да и в быту часто приходится делать массу различных расчётов, вести многострочные таблицы с числовой и текстовой информацией, проделывая с данными всяческие вычисления, выводя на печать варианты. Для решения ряда экономических и финансовых задач целесообразно использовать многочисленные возможности электронных таблиц. Рассмотрим в этой связи вычислительные функции MS Excel.

Источник: Журнал "Бухгалтер и Компьютер" №4 2004г.

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

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

Все операторы делятся на несколько групп (см. таблицу).

Арифметические операторы используются для обозначения основных математических операций над числами. Результатом выполнения арифметической операции всегда является число. Операторы сравнения используются для обозначения операций сравнения двух чисел. Результатом выполнения операции сравнения является логическое значение ИСТИНА или ЛОЖЬ.

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

При вводе формул можно применять обычные числовые и текстовые значения. Напомним, что числовые значения могут содержать только цифры от 0 до 9 и специальные символы: (плюс, минус, косая черта, круглые скобки, точка, запятая, знаки процента и доллара). Текстовые значения могут содержать любые символы. Необходимо отметить, что используемые в формулах текстовые выражения должны заключаться в двойные кавычки, например “константа1”. Кроме того, в формулах можно использовать ссылки на ячейки (в том числе в виде имён) и многочисленные функции, которые соединяются между собой операторами.

Ссылки представляют собой включаемые в формулу адреса ячеек или диапазоны ячеек. Ссылки на ячейки задаются обычным образом, т. е. в виде A1, B1, C1. Например, для того, чтобы получить в ячейке A3 сумму ячеек A1 и A2, в неё достаточно ввести формулу =A1+A2 (рис. 1).

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

Выделить ячейку, в которую нужно ввести формулу;

Начать ввод формулы, нажав клавишу “=” (равно);

Щёлкнуть мышью на ячейке A1;

Ввести символ “+”;

Щёлкнуть мышью на ячейке B2;

Закончить ввод формулы, нажав клавишу Enter.

Диапазон ячеек представляет собой некоторую прямоугольную область рабочего листа и однозначно определяется адресами ячеек, расположенными в противоположных углах диапазона. Разделённые символом “:” (двоеточие), эти две координаты составляют адрес диапазона. Например, чтобы получить сумму значений ячеек диапазона C3:D7, используйте формулу =СУММ(C3:D7).

В частном случае, когда диапазон состоит целиком из нескольких столбцов, например от В до D, его адрес записывается в виде В:D. Аналогично если диапазон целиком состоит из строк с 6-й по 15-ю, то он имеет адрес 6:15. Кроме того, при записи формул можно использовать объединение нескольких диапазонов или ячеек, разделяя их символом “;” (точка с запятой), например C3:D7; E5;F3:G7.

Редактирование уже введённой формулы можно сделать несколькими способами:

Двойным щелчком левой кнопки мыши на ячейке, чтобы корректировать формулу непосредственно в этой ячейке;

Выбрать ячейку и нажать клавишу F2 (рис. 2);

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

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

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

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

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

Например, вам нужно складывать построчно значения столбцов А и В (рис. 8) и поместить результат в столбец С. Если вы копируете формулу =А2+В2 из ячейки С2 в ячейку С3* (и далее вниз по С), то Excel сам преобразует адреса формулы соответственно как =А3+В3 (и т. д.). Но если вам нужно поместить формулу, скажем, из С2 в ячейку D4, то формула уже будет выглядеть как =В4+С4 (вместо нужной =А4+В4), и соответственно результат вычислений будет неправильным! Иными словами, обратите особое внимание на процесс копирования и при необходимости вручную корректируйте формулы. Кстати, само копирование из С2 в С3 делается следующим образом:

1) выбираем ячейку С2, из которой нужно скопировать формулу;

2) нажимаем кнопку “Копировать” на панели инструментов, или клавиши Ctrl+C, или выбираем в меню “Правка ® Копировать”;

3) выбираем ячейку С3, в которую будем копировать формулу;

4) нажимаем кнопку “Вставить” на панели инструментов, или клавиши Ctrl+V, или через меню “Правка ® Вставить” с нажатием Enter.

Рассмотрим режим автозаполнения. Если необходимо перенести (скопировать) формулу в несколько ячеек (например, в С3:С5) вниз по столбцу, то это удобнее и проще сделать так: повторить предыдущую последовательность действий до пункта 3 выбора ячейки С3, далее курсор мыши подвести к начальной ячейке диапазона (С3), нажать левую кнопку мыши и, не отпуская её, протащить ниже до требуемой последней ячейки диапазона. В нашем случае это ячейка С5. Затем отпускаем левую кнопку мыши, переводим курсор на кнопку “Вставить” панели инструментов и нажимаем её, а потом Enter. Excel сам преобразует адреса формул в выделенном нами диапазоне по соответствующим адресам строк.

Иногда возникает необходимость скопировать только числовое значение ячейки (диапазона ячеек). Для этого нужно проделать следующее:

1) выбрать ячейку (диапазон), из которой нужно скопировать данные;

2) нажать кнопку “Копировать” на панели инструментов или выбрать в меню “Правка ® Копировать”;

3) выбрать ячейку (левую верхнюю нового диапазона), в которую будут копироваться данные;

4) выбрать в меню “Правка ® Специальная вставка” и нажать Enter.

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

:: Функции в Excel

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

В таблице часто требуется вычислить итоговую сумму по столбцу или строке. Для этого Excel предлагает функцию автоматической суммы, выполняемой нажатием кнопки (“Автосумма”) на панели инструментов.

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

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

:: Автоматические вычисления

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

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

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

Чтобы увидеть результат промежуточного суммирования, достаточно просто выделить необходимые ячейки. Этот результат отражается и в строке состояния в нижней части экрана. Если сумма там не появилась, подведите курсор к строке состояния на нижней части рамки, щёлкните правой кнопкой мыши и в выпавшем меню у строки Сумма нажмите левую кнопку мыши. Более того, в этом меню на строке состояния вы можете выбрать различные варианты рассчитываемых результатов: сумму, среднее арифметическое значение, количество элементов или минимальное значение в выделенном диапазоне.

Для примера рассчитаем с помощью этой функции сумму значений для диапазона В3:В9. Выделите числа в диапазоне ячеек В3:В9. Обратите внимание, что в строке состояния, расположенной внизу рабочего окна, появилась надпись Сумма=X, где X — число, равное сумме выделенных чисел диапазона (рис. 5).

Как видим, результаты обычного расчёта по формуле в ячейке В10 и автовычисления совпадают.

:: Мастер функций

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

Вызвать окно диалога “Мастера” можно с помощью команды “Вставка ® Функция”, комбинацией клавиш Shift+F3 или кнопкой на стандартной панели инструментов.

Первый диалог “Мастера функций” организован по тематическому принципу. Выбрав категорию, в нижнем окне мы увидим список имён функций, содержащихся в данной группе. Например, функцию СУММ () вы сможете отыскать в группе “Математические”, а в группе “Дата и время” находятся функции ЧИСЛО(), МЕСЯЦ(), ГОД(), СЕГОДНЯ().

Кроме того, для ускорения выбора функций Excel “помнит” имена 10 недавно использованных функций в соответствующей группе. Обратите внимание, что в нижней части окна отображается краткая справка о назначении функции и её аргументах. Если вы нажмёте кнопку “Справка” в нижней части диалогового окна, то Excel откроет соответствующий раздел справочной системы.

Предположим, что необходимо произвести расчёт амортизации имущества. В этом случае следует в зоне поиска функции ввести слово “амортизация”. Программа подберёт все функции по амортизации (рис. 7).

После заполнения соответствующих полей функции будет произведён расчёт амортизации имущества.

Нередко нужно произвести сложение чисел, удовлетворяющих какому-либо условию. В этом случае следует использовать функцию СУММЕСЛИ. Рассмотрим конкретный пример. Допустим необходимо подсчитать сумму комиссионных, если стоимость имущества превышает 75 000 руб. Для этого используем данные таблицы зависимости комиссионных от стоимости имущества (рис. 8).

Наши действия в этом случае таковы. Устанавливаем курсор в ячейку В6, кнопкой запускаем “Мастера функций”, в категории “Математические” выбираем функцию СУММЕСЛИ, задаём параметры, как на рис. 9.

Обратите внимание, что в качестве диапазона для проверки условия мы выбираем интервал ячеек А2:А6 (стоимость имущества), а в качестве диапазона суммирования — В2:В6 (комиссионные), при этом условие имеет вид (>75000). Результат нашего расчёта составит 27 000 руб.

:: Дадим имя ячейке

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

Присваивая имена, необходимо помнить, что они могут состоять из букв (в том числе русского алфавита), цифр, точек и символов подчёркивания. Первый знак в имени должен быть буквой или знаком подчёркивания. Имена не могут иметь такой же вид, как и ссылки на ячейки, например Z$100 или R1C1. В имени может быть больше одного слова, но пробелы недопустимы. В качестве разделителей слов могут быть использованы знаки подчёркивания и точки, например Налог_на_продажи или Первый.Квартал. Имя может содержать до 255 знаков. При этом прописные и строчные буквы воспринимаются одинаково.

Чтобы вставить имя в формулу, можно воспользоваться командой “Вставка ® Имя ® Вставить”, выбрав нужное имя в списке имён.

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

Имена в Excel можно определять не только для отдельных ячеек, но и для диапазонов (в том числе несмежных). Для присвоения имени достаточно выделить диапазон, а затем ввести название в поле имени. Кроме того, для задания имён диапазонов, содержащих заголовки, удобно использовать специальную команду “Создать” в меню “Вставка ® Имя”.

Чтобы удалить имя, выберите его в списке и нажмите кнопку “Удалить”.

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

то для подсчёта общей суммы для столбца “Комиссионные” используется формула =СУММ(Комиссионные) (рис. 11).

:: Дополнительные возможности Excel — шаблоны

В состав MS Excel входит набор шаблонов — таблицы Excel, которые предназначены для анализа хозяйственной деятельности предприятия, составления счёта, наряда и даже для учёта личного бюджета. Они могут быть использованы для автоматизации решения часто встречающихся задач. Так, можно создавать документы на основе шаблонов “Авансовый отчёт”, “Счёт”, “Заказ”, которые содержат бланки используемых в хозяйственной деятельности документов. Эти бланки по своему внешнему виду и при печати не отличаются от стандартных, и единственное, что нужно сделать для получения документа, — заполнить его поля.

Для создания документа на основе шаблона выполните команду “Создать” из меню “Файл”, затем выберите необходимый шаблон на вкладке “Решения” (рис. 12).

Шаблоны копируются на диск при обычной установке Excel. Если шаблоны не отображаются в окне диалога “Создание документа”, запустите программу установки Excel и установите шаблоны. Чтобы получить подробные сведения об установке шаблонов, посмотрите раздел “Установка компонентов Microsoft Office” в справке Excel.

Например, для создания ряда финансовых документов выберите шаблон “Финансовые шаблоны” (рис. 13).

Эта группа шаблонов содержит формы следующих документов:

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

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

Excel позволяет пользователю самому создавать шаблоны документов, а также редактировать уже имеющиеся.

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

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

Ликвидация без проверок

www.law-russia.ru

Добровольная ликвидация с гарантией отсутствия проверки. Проводим во всех регионах РФ, работаем со всеми сложными случаями, предоставляем финансовые гарантии. Бухгалтерам, аудиторам и юридическим компаниям предоставляем специальные условия. Снимаем поручительство физических лиц за долги юридических лиц. Обеспечиваем полный комплекс "под ключ".

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

Как написать формулу в Excel

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

1. Каждая начинается со знака «=».
2. Участвовать в вычислениях могут значения из ячеек и функции.
3. В качестве привычных нам математических знаков операций используются операторы.
4. При вставке записи в ячейке по умолчанию отражается результат вычислений.
5. Посмотреть конструкцию можно в строке над таблицей.

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

Итак, как создать и вставить формулу в Excel? Действуйте по следующему алгоритму:

Обозначение Значение

Сложение
- Вычитание
/ Деление
* Умножение

Если вам необходимо указать число, а не адрес ячейки – вводите его с клавиатуры. Чтобы указать отрицательный знак в формуле Excel, нажмите «-».

Как вводить и скопировать формулы в Excel

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


Нажмите левую кнопку и тяните.


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


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


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


Цена с НДС высчитывается как цена*(1+НДС). Введем последовательность в первую ячейку.


Попробуем скопировать запись.


Результат получился странный.


Проверим содержимое во второй ячейке.


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


Нажмите F4. Адрес будет разбавлен знаком «$». Это и есть признак абсолютно ячейки.


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

Использование функций для вычислений

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


Расскажем о некоторых функциях.

Как задать формулы «Если» в Excel

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


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


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


Укажем условие. Для этого необходимо щелкнуть в первую строку и выбрать первую ячейку «Продано». Далее поставим знак «>» и укажем число 4.


Во второй строке напишем «Закупить». Эта надпись будет появляться для тех товаров, которые были распроданы. Последнюю строку можно оставить пустой, так как у нас нет действий, если условие ложно.


Нажмите ОК и скопируйте запись для всего столбца.


Чтобы в ячейке не выводилось «ЛОЖЬ» снова откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строки формул. Вставьте курсор на третью строку и поставьте пробел в кавычках.


Затем ОК и снова скопируйте.


Теперь мы видим, какой товар следует закупить.

Формула текст в Excel

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


В первую ячейку введем функцию (кнопка «Текстовые» в разделе «Формулы»).


В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».


Нажмем ОК и скопируем.


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

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

Формула даты в Excel

Excel предоставляет много возможностей по работе с датами. Одна из них, ДАТА, позволяет построить дату из трех чисел. Это удобно, если вы имеете три разных столбца – день, месяц, год.

Поставьте указатель на первую ячейку четвертого столбца и выберите функцию из списка «Дата и время».

Расставьте адреса ячеек соответствующим образом и нажмите ОК.


Скопируйте запись.

Автосумма в Excel

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


Перейдите на панель «Формулы» и нажмите «Автосумма».


Excel автоматически выделит ближайший числовой диапазон.


Вы можете выделить другой диапазон. В данном примере Excel все сделал правильно. Нажмите ОК. Обратите внимание на содержимое ячейки. Функция СУММ подставилась автоматически.


При вставке диапазона указывается адрес первой ячейки, двоеточие и адрес последней ячейки. «:» означает «Взять все ячейки между первой и последней. Если вам надо перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Работа в Excel с формулами: пример

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


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

Чтобы переименовать лист, два раза на нем щелкните и введите имя.

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

Отличного Вам дня!