Формула предписывает программе Excel порядок действий с числами, значениями в ячейке или группе ячеек. Без формул электронные таблицы не нужны в принципе.
Конструкция формулы включает в себя: константы, операторы, ссылки, функции, имена диапазонов, круглые скобки содержащие аргументы и другие формулы. На примере разберем практическое применение формул для начинающих пользователей.
Формулы в Excel для чайников
Чтобы задать формулу для ячейки, необходимо активизировать ее (поставить курсор) и ввести равно (=). Так же можно вводить знак равенства в строку формул. После введения формулы нажать Enter. В ячейке появится результат вычислений.
В Excel применяются стандартные математические операторы:
Символ «*» используется обязательно при умножении. Опускать его, как принято во время письменных арифметических вычислений, недопустимо. То есть запись (2+3)5 Excel не поймет.
Программу Excel можно использовать как калькулятор. То есть вводить в формулу числа и операторы математических вычислений и сразу получать результат.
Но чаще вводятся адреса ячеек. То есть пользователь вводит ссылку на ячейку, со значением которой будет оперировать формула.
При изменении значений в ячейках формула автоматически пересчитывает результат.
Оператор умножил значение ячейки В2 на 0,5. Чтобы ввести в формулу ссылку на ячейку, достаточно щелкнуть по этой ячейке.
В нашем примере:
- Поставили курсор в ячейку В3 и ввели =.
- Щелкнули по ячейке В2 – Excel «обозначил» ее (имя ячейки появилось в формуле, вокруг ячейки образовался «мелькающий» прямоугольник).
- Ввели знак *, значение 0,5 с клавиатуры и нажали ВВОД.
Если в одной формуле применяется несколько операторов, то программа обработает их в следующей последовательности:
- %, ^;
- *, /;
- +, -.
Поменять последовательность можно посредством круглых скобок: Excel в первую очередь вычисляет значение выражения в скобках.
Как в формуле Excel обозначить постоянную ячейку
Различают два вида ссылок на ячейки: относительные и абсолютные. При копировании формулы эти ссылки ведут себя по-разному: относительные изменяются, абсолютные остаются постоянными.
Находим в правом нижнем углу первой ячейки столбца маркер автозаполнения. Нажимаем на эту точку левой кнопкой мыши, держим ее и «тащим» вниз по столбцу.
Отпускаем кнопку мыши – формула скопируется в выбранные ячейки с относительными ссылками. То есть в каждой ячейке будет своя формула со своими аргументами.
Начните создавать формулы и использовать встроенные функции, чтобы выполнять расчеты и решать задачи.
Важно: Вычисляемые результаты формул и некоторые функции листа Excel могут несколько отличаться на компьютерах под управлением Windows с архитектурой x86 или x86-64 и компьютерах под управлением Windows RT с архитектурой ARM.
Создание формулы, ссылающейся на значения в других ячейках
Просмотр формулы
Ввод формулы, содержащей встроенную функцию
Скачивание книги "Учебник по формулам"
Мы подготовили для вас книгу Начало работы с формулами , которая доступна для скачивания. Если вы впервые пользуетесь Excel или даже имеете некоторый опыт работы с этой программой, данный учебник поможет вам ознакомиться с самыми распространенными формулами. Благодаря наглядным примерам вы сможете вычислять сумму, количество, среднее значение и подставлять данные не хуже профессионалов.
Подробные сведения о формулах
Чтобы узнать больше об определенных элементах формулы, просмотрите соответствующие разделы ниже.
Части формулы Excel
Формула также может содержать один или несколько таких элементов, как функции , ссылки , операторы и константы .
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте
Во второй части цикла Excel 2010 для начинающих вы научитесь связывать ячейки таблиц математическими формулами, добавлять строки и столбцы к уже готовой таблице, узнаете о функции автозаполнения и многое другое.
Введение
В первой части цикла «Excel 2010 для начинающих» мы познакомились с самыми азами программы Excel, научившись в ней создавать обычные таблицы. Строго говоря, дело это нехитрое и конечно возможности этой программы намного шире.
Основное преимущество электронных таблиц состоит в том, что отдельные клетки с данными можно связать между собой математическими формулами. То есть при изменении значения одной из связанных между собой ячеек, данные других будут пересчитаны автоматически.
В этой части мы разберемся, какую же пользу могут принести такие возможности на примере уже созданной нами таблицы бюджетных расходов, для чего нам придется научиться составлять простые формулы. Так же мы познакомимся с функцией автозаполнения ячеек и узнаем, каким образом можно вставлять в таблицу дополнительные строки и столбцы, а так же объединять в ней ячейки.
Выполнение базовых арифметических операций
Помимо создания обычных таблиц, Excel можно использовать для выполнения в них арифметических операций, таких как: сложение, вычитание, умножение и деление.
Для выполнения расчетов в любой ячейке таблицы необходимо создать внутри нее простейшую формулу , которая всегда должна начинаться со знака равенства (=). Для указания математических операций внутри формулы используются обычные арифметические операторы:
Например, давайте представим, что нам необходимо сложить два числа - «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления - «19».
Чтобы узнать, что же на самом деле содержит ячейка - формулу или число, - необходимо ее выделить и посмотреть на строку формул - область находящуюся сразу же над наименованиями столбцов. В нашем случае в ней как раз отображается формула, которую мы только что вводили.
После проведения всех операций, обратите внимание на результат деления чисел 12 на 7, который получился не целым (1,714286) и содержит довольно много цифр после запятой. В большинстве случаев такая точность не требуется, да и столь длинные числа будут только загромождать таблицу.
Чтобы это исправить, выделите ячейку с числом, у которого необходимо изменить количество десятичных знаков после запятой и на вкладке Главная в группе Число выберите команду Уменьшить разрядность . Каждое нажатие на эту кнопку убирает один знак.
Слева от команды Уменьшить разрядность находится кнопка, выполняющая обратную операцию - увеличивает число знаков после запятой для отображения более точных значений.
Составление формул
Теперь давайте вернемся к таблице бюджетных расходов, которую мы создавали в первой части этого цикла.
.png)
На данный момент в ней зафиксированы ежемесячные личные расходы по конкретным статьям. Например, можно узнать, сколько было истрачено в феврале на продукты питания или в марте на обслуживание автомобиля. А вот общие ежемесячные расходы здесь не указаны, хотя именно эти показатели для многих являются самыми важными. Давайте исправим эту ситуацию, добавив внизу таблицы строчку «Ежемесячные расходы» и рассчитаем ее значения.
Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.
А теперь представьте, что при указании значений одной или нескольких статей расходов вы допустили ошибку. В этом случае, вам придется скорректировать не только данные в ячейках с указанием расходов, но и формулу вычисления суммарных трат. Конечно, это очень неудобно и поэтому в Excel при составлении формул часто используются не конкретные числовые значения, а адреса и диапазоны ячеек .
С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.
В ячейку B7, введите знак равно (=) и… вместо того, чтобы вручную вбивать значение клетки B2, щелкните по ней левой кнопкой мыши. После этого вокруг ячейки появится пунктирная выделительная рамка, которая показывает, что ее значение попало в формулу. Теперь введите знак «+» и щелкните по ячейке B3. Далее проделайте тоже самое с ячейками B4, B5 и B6, а затем нажмите клавишу ВВОД (Enter), после чего появится то же значение суммы, что и в первом случае.
Выделите вновь ячейку B7 и посмотрите на строку формул. Видно, что вместо цифр - значений ячеек, в формуле содержатся их адреса. Это очень важный момент, так как мы только что построили формулу не из конкретных чисел, а из значений ячеек, которые могут со временем изменяться. Например, если теперь поменять сумму расходов на покупку вещей в январе, то весь ежемесячный суммарный расход будет пересчитан автоматически. Попробуйте.
Теперь давайте предположим, что просуммировать нужно не пять значений, как в нашем примере, а сто или двести. Как вы понимаете, использовать вышеописанный метод построения формул в таком случае очень неудобно. В этом случае лучше воспользоваться специальной кнопкой «Автосумма», которая позволяет вычислить сумму нескольких ячеек в пределах одного столбца или строки. В Excel можно считать не только суммы столбцов, но и строк, так что используем ее для вычисления, например, общих расходов на продукты питания за полгода.
Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование . Теперь, вернемся к таблице и посмотрим, что же произошло.
В выбранной нами ячейке появилась формула с интервалом ячеек, значения которых требуется просуммировать. При этом опять появилась пунктирная выделительная рамка. Только в этот раз она обрамляет не одну клетку, а весь диапазон ячеек, сумму которых требуется посчитать.
Теперь посмотрим на саму формулу. Как и раньше, вначале идет знак равенства, но на этот раз за ним следует функция «СУММ» - заранее определенная формула, которая выполнит сложение значений указанных ячеек. Сразу за функцией идут скобки расположенные вокруг адресов клеток, значения которых нужно просуммировать, называемые аргументом формулы . Обратите внимание, что в формуле не указаны все адреса суммируемых ячеек, а лишь первой и последней. Двоеточие между ними обозначает, что указан диапазон клеток от B2 до G2.
После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.
Итак, в нашей таблице мы посчитали общие траты за январь и суммарный расход на продукты питания за полгода. При этом сделали это двумя разными способами - сначала с использованием в формуле адресов ячеек, а затем, функции и диапазона. Теперь, самое время закончить расчеты для оставшихся ячеек, посчитав общие затраты по остальным месяцам и статьям расходов.
Автозаполнение
Для расчета оставшихся сумм воспользуемся одной замечательной особенностью программы Excel, которая заключается в возможности автоматизировать процесс заполнения ячеек систематизированными данными.
Иногда в Excel приходится вводить похожие однотипные данные в определенной последовательности, например дни недели, даты или порядковые номера строк. Помните, в первой части этого цикла в шапке таблицы мы вводили название месяца в каждый столбец по отдельности? На самом деле, совершенно необязательно было вводить весь этот список вручную, так как приложение во многих случаях может сделать это за вас.
Давайте сотрем все названия месяцев в шапке нашей таблицы, кроме первого. Теперь выделите ячейку с надписью «Январь» и переместите указатель мыши в правый ее нижний угол, что бы он принял форму крестика, который называется маркером заполнения . Зажмите левую кнопку мыши и перетащите его вправо.
.png)
На экране появится всплывающая подсказка, которая сообщит вам то значение, которое программа собирается вставить в следующую клетку. В нашем случае это «Февраль». По мере перемещения маркера вниз она будет меняться на названия других месяцев, что поможет вам понять, где нужно остановиться. После того как кнопка будет отпущена, список заполнится автоматически.
Конечно, Excel не всегда верно «понимает», как нужно заполнить последующие клетки, так как последовательности могут быть довольно разнообразными. Представим себе, что нам необходимо заполнить строку четными числовыми значениями: 2, 4, 6, 8 и так далее. Если мы введем число «2» и попробуем переместить маркер автозаполнения вправо, то окажется, что программа предлагает, как в следующую, так и в другие ячейки вставить опять значение «2».
В этом случае, приложению необходимо предоставить несколько больше данных. Для этого в следующей ячейке справа введем цифру «4». Теперь выделим обе заполненные клетки и вновь переместим курсор в правый нижний угол области выделения, что бы он принял форму маркера выделения. Перемещая маркер вниз, мы видим, что теперь программа поняла нашу последовательность и показывает в подсказках нужные значения.
Таким образом, для сложных последовательностей, перед применением автозаполнения, необходимо самостоятельно заполнить сразу несколько ячеек, что бы Excel правильно смог определить общий алгоритм вычисления их значений.
Теперь давайте применим эту полезную возможность программы к нашей таблице, что бы ни вводить формулы вручную для оставшихся клеток. Сначала выделите ячейку с уже посчитанной суммой (B7).
Теперь «зацепите» курсором правый нижний угол квадратика и перетащите маркер вправо до ячейки G7. После того как вы отпустите клавишу, приложение само скопирует формулу в отмеченные ячейки, при этом автоматически изменив адреса клеток, содержащихся в выражении, подставив правильные значения.
При этом если маркер перемещать вправо, как в нашем случае, или вниз, то ячейки будут заполняться в порядке возрастания, а влево или вверх - в порядке убывания.
Существует так же способ заполнения ряда с помощью ленты. Воспользуемся им для вычисления сумм затрат по всем расходным статьям (столбец H).
Выделяем диапазон, который следует заполнить, начиная с ячейки с уже введенными данными. Затем на вкладке Главная в группе Редактирование нажимаем кнопку Заполнить и выбираем направление заполнения.
Добавление строк, столбцов и объединение ячеек
Чтобы получить больше практики в составлении формул, давайте расширим нашу таблицу и заодно освоим несколько основных операций ее форматирования. Например, добавим к расходной части, статьи доходов, а затем проведем расчет возможных бюджетных накоплений.
Предположим, что доходная часть таблицы будет располагаться сверху над расходной. Для этого нам придется вставить несколько дополнительных строк. Как всегда, сделать это можно двумя путями: используя команды на ленте или в контекстном меню, что быстрее и проще.
Щелкните в любой ячейке второй строки правой кнопкой мыши и в открывшемся меню выберите команду Вставить… , а затем в окне - Добавить строку .
После вставки строки обратите внимание на тот факт, что по умолчанию она вставляется над выбранной строкой и имеет формат (цвет фона ячеек, настройки размера, цвета текста и т. д.) ряда располагающегося над ней.
Если нужно изменить форматирование, выбранное по умолчанию, сразу после вставки щелкните по кнопке Параметры добавления , которая автоматически отобразится рядом с правым нижним углом выбранной ячейки и выберите нужный вариант.
Аналогичным методом в таблицу можно вставлять столбцы, которые будут размещаться слева от выбранного и отдельные ячейки.
Кстати, если в итоге строка или столбец после вставки оказались на ненужном месте, их легко можно удалить. Щелкните правой кнопкой мыши на любой ячейке, принадлежащей удаляемому объекту и в открывшемся меню выберите команду Удалить . В завершении укажите, что именно необходимо удалить: строку, столбец или отдельную ячейку.
На ленте для операций добавления можно использовать кнопку Вставить , расположенную в группе Ячейки на закладке Главная , а для удаления, одноименную команду в той же группе.
В нашем случае нам необходимо вставить пять новых строк в верхнюю часть таблицы сразу после шапки. Для этого можно повторить операцию добавления несколько раз, а можно выполнив ее единожды использовать клавишу «F4», которая повторяет самую последнюю операцию.
В итоге после вставки пяти горизонтальных рядов в верхнюю часть таблицы, приводим ее к следующему виду:
Белые неформатированные ряды в таблице мы оставили специально, что бы отделить доходную, расходную и итоговую часть друг от друга, написав в них соответствующие заголовки. Но перед тем как это сделать, мы изучим еще одну операцию в Excel - объединение ячеек .
При объединении нескольких смежных ячеек образуется одна, которая может занимать сразу несколько столбцов или строк. При этом именем объединенной ячейки становится адрес верхней девой ячейки объединяемого диапазона. В любой момент вы можете снова разбить объединенную ячейку, а вот клетку, которая никогда не была объединена, разбить не удастся.
При объединении ячеек, сохраняются данные только верхней левой, данные же всех остальных объединяемых ячеек будут удалены. Помните это и сначала лучше производите объединение, а лишь потом вводите информацию.
Вернемся к нашей таблице. Для того, что бы написать заголовки в белых строчках нам понадобится лишь одна ячейка, в то время как сейчас они состоят из восьми. Давайте исправим это. Выделите все восемь ячеек второго ряда таблицы и на вкладке Главная в группе Выравнивание щелкните на кнопку Объединить и поместить в центре .
После выполнения команды, все выделенные ячейки в строке объединятся в одну большую ячейку.
Рядом с кнопкой объединения расположена стрелочка, нажатие на которую вызовет меню с дополнительными командами, позволяющими: объединять ячейки без центрального выравнивания, объединять целые группы ячеек по горизонтали и вертикали, а так же отменить объединение.
После добавления заголовков, а так же заполнения строк: зарплата, бонусы и ежемесячные доходы, наша таблица стала выглядеть вот так:
Заключение
В заключении давайте рассчитаем последнюю строчку нашей таблицы, воспользовавшись полученными знаниями в этой статье, вычисления значений ячеек которой будут происходить по следующей формуле. В первом месяце баланс будет складываться из обычной разницы между доходом, полученным за месяц и общими расходами в нем. А вот во втором месяце мы к этой разнице приплюсуем баланс первого, так как мы ведем расчет именно накоплений. Расчёты для последующих месяцев будут выполняться по такой же схеме - к текущему ежемесячному балансу будут прибавляться накопления за предыдущий период.
Теперь переведем эти расчеты в формулы понятные Excel. Для января (ячейки B14) формула очень проста и будет выглядеть так: «=B5-B12». А вот для ячейки С14 (февраль) выражение можно записать двумя разными способами: «=(B5-B12)+(C5-C12)» или «=B14+C5-C12». В первом случае мы опять проводим расчет баланса предыдущего месяца и затем прибавляем к нему баланс текущего, а во втором в формулу включается уже рассчитанный результат по предыдущему месяцу. Конечно, использование второго варианта для построения формулы в нашем случае гораздо предпочтительнее. Ведь если следовать логике первого варианта, то в выражении для мартовского расчета будет фигурировать уже 6 адресов ячеек, в апреле - 8, в мае - 10 и так далее, а при использовании второго варианта их всегда будет три.
Для заполнения оставшихся ячеек с D14 по G14 применим возможность их автоматического заполнения, так же как мы это делали в случае с суммами.
Кстати, для проверки значения итоговых накоплений на июнь, находящегося в клетке G14, в ячейке H14 можно вывести разницу между общей суммой ежемесячных доходов (H5) и ежемесячных расходов (H12). Как вы понимаете, они должны быть равны.
Как видно из последних расчетов, в формулах можно использовать не только адреса смежных ячеек, но и любых других, вне зависимости от их расположения в документе или принадлежности к той или иной таблице. Более того вы вправе связывать ячейки находящиеся на разных листах документа и даже в разных книгах, но об этом мы уже поговорим в следующей публикации.
А вот и наша итоговая таблица с выполненными расчётами:
Теперь, при желании, вы уже самостоятельно сможете продолжать ее наполнение, вставляя как дополнительные статьи расходов или доходов (строки), так и добавляя новые месяцы (столбцы).
В следующем материале мы более подробно поговорим о функциях, разберемся с понятием относительных и абсолютных ссылок, обязательно освоим еще несколько полезных элементов редактирования таблиц и многое другое.
Формулы в Excel - это его основная суть, то, ради чего и была создана эта программа компанией Microsoft. Формулы позволяют произвести расчеты значений ячеек на основе данных других ячеек, причем если исходные данные поменяются, то результат вычислений в ячейке, где стоит формула пересчитается автоматически!
Создание формул в Excel
Рассмотрим работу формул на самом простом примере - сумме двух чисел. Пусть в одной ячейке Excel введено число 2, а в другой 3. Нужно, чтобы в третье ячейке появилась сумма этих чисел.
Суммой 2 и 3 является, конечно же, 5, но вносить пятерку вручную в следующую ячейку не надо, иначе теряется смысл расчетов в Excel. В ячейку с итогом необходимо ввести формулу суммы и тогда результат будет вычислен программой автоматически.
В примере вычисление выглядит простым, но когда числа большие или дробные без формулы просто не обойтись.
Фомулы в Excel могут содержать арифметические операции (сложение +, вычитание -, умножение *, деление /), координаты ячеек исходных данных (как по отдельности, так и диапазон) и функции вычисления.
Рассмотрим формулу для суммы чисел в примере выше:
СУММ(A2;B2)
Каждая формула начинается со знака «равно». Если Вы хотите добавить в ячейку формулу, написав ее вручную, то именно этот знак следует написать первым.
Далее в примере идет функция СУММ, которая означает что необходимо произвести суммирование некоторых данных, а уже в скобках у функции, разделенные точкой с запятой, указываются некоторые аргументы, в данном случае координаты ячеек (A2 и B2), значения которых необходимо сложить и поместить результат в ту ячейку, где написана формула. Если бы Вам требовалось сложить три ячейки, то можно было бы написать три аргумента у функции СУММ, разделяя их точкой с запятой, например:
СУММ(А4;B4;C4)
Когда требуется сложить большое количество ячеек, то указывать каждую из них в формуле займет очень много времени, поэтому вместо простого перечисления можно использовать указание диапазона ячеек:
СУММ(B2:B7)
Диапазон ячеек в Экселе указывается с помощью координат первой и последней ячеек, разделенных знаком «двоеточие». В данном примере производится сложение значений ячеек, начиная с ячейки B2 до ячейки B7.
Функции в формулах можно соединять и комбинировать как Вам необходимо для получение требуемого результата. Например, стоит задача сложить три числа и в зависимости от того, меньше ли результат числа 100 или больше, домножить сумму на коэффициент 1.2 или 1.3. Решить задачу поможет следующая формула:
ЕСЛИ(СУММ(А2:С2)
Разберем решение задачи подробнее. Использовалось две функции ЕСЛИ и СУММ. Функция ЕСЛИ всегда имеет три аргумента: первый - условие, второй - действие в случае, если условие верно, третий - действие в случае, если условие неверно. Напоминаем, что аргументы разделяются знаком «точка с запятой».
ЕСЛИ(условие; верно; неверно)
В качестве условия указано, что сумма диапазона ячеек A2:C2 меньше 100. Если при расчете, условие выполнится и сумма ячеек диапазона будет равна, например, 98, то Эксель выполнить действие указанное во втором аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,2. В случае же, если сумма превысит число 100, то выполнится уже действие в третьем аргументе функции ЕСЛИ, т.е. СУММ(А2:С2)*1,3.
Встроенные функции в Excel
Функций в Excel огромное количество и знать все просто невозможно. Некоторые часто используемые запомнить можно, а некоторые Вам понадобятся лишь изредка и помнить их название и тем более форму записи очень сложно.
Но в Экселе имеется стандартный способ вставки функций с их полным списком. Если Вы хотите добавить какую-то функцию в ячейку, то кликните по ячейке и выберите в главном меню вставку функции. Программа отобразит список функций и Вы сможете выбрать ту, которая необходима для решения задачи.
Чтобы вставить функцию в Excel 2007 выберите в главном меню пункт «Формулы» и кликните на значок «Вставить функцию», либо нажмите на клавиатуре комбинацию клавиш Shift+F3.
В Excel 2003 функция вставляется через меню «Вставка»->«Функция». Так же работает и комбинация клавиш Shift+F3.
В ячейке на которой стоял курсор появится знак равенства, а поверх листа отобразится окно «Мастер функций».
Функция в Excel разделены по категориям. Если Вы знаете к какой категории может относится предполагаемая Вами функция, то выбирайте отбор по ней. В противном случае выберите «Полный алфавитный перечень». Программа отобразит все имеющиеся функции в списке функций.
Пролистывайте список и выделяйте мышью наименование, заинтересовавшей Вас функции. Чуть ниже списка появится ее форма записи, требуемые аргументы и краткое описание, которое разъяснит Вам предназначение функции. Когда найдете то, что нужно, кликните по кнопке «OK» для перехода к указанию аргументов.
В окне аргументов имеются поля с названиями «Число 1», «Число 2» и т.д. Их необходимо заполнить координатами ячеек (либо диапазонами) в которых требуется взять данные. Заполнять можно вручную, но гораздо удобнее нажать в конце поля на значок таблицы для того, чтобы указать исходную ячейку или диапазон.
Окно аргументов примет упрощенный вид. Теперь необходимо кликнуть мышью на первую исходную ячейку с данными, а затем снова на значок таблица в окне аргументов.
Поле «Число 1» заполнится координатами выбранной ячейки. Ту же самую процедуру следует проделать для поля «Число 2» и для следующих полей, если число аргументов функции у вас более двух.
Заполнив все аргументы, Вы уже можете предварительно посмотреть результат расчета полученной формулы. Чтобы он появился в ячейке на листе, нажмите кнопку «OK». В рассмотренном примере в ячейку D2 помещено произведение чисел в ячейках B2 и C2.
Рассмотренный способ вставки функции является универсальным и позволяет добавлять любую функцию из общего списка стандартных функций Excel.
| Нравится |
Speaker Deck SlideShare
Основы использования формул в Excel. Правила ввода формул, разрешенные математические операции, порядок вычисления. Функция и формула, часто используемые функции группы Автосумма.
Навыки экзамена Microsoft Office Specialist (77-420):
Теоретическая часть:
- Построение простых формул
Видеоверсия
Текстовая версия
Именно возможность производить различного рода вычисления и снискали мировую популярность табличному процессору Excel из пакета Microsoft Office.
До этого занятия мы фактически не использовали Excel для проведения расчетов, начиная с данного занятия попытаемся если не полностью раскрыть, то продемонстрировать тот потенциал, который заложен в эту программу.
Основы построения формул
Если в начале ячейки поставить знак «=», то Excel начинает воспринимать ячейку как формулу.
В качестве простейшего примера формулы можно представить простую операцию суммирования двух чисел, вычитания, умножения и деления. В Excel работают стандартные математические операции и последовательность их применения.
По умолчанию, в ячейке, содержащей формулу, для пользователя отображается результат ее вычисления, если нужно посмотреть сами формулы, сделать этом можно командой отображения формул «Показать формулы», группа «Зависимости формул», вкладка «Формулы», либо нажать горячее сочетание Ctrl+` (клавиша тильда стоит в начале цифрового ряда на клавиатуре).
Горячее сочетание
Ctrl+` переключает режимы отображения данные/ формулы в Excel
Формула представляет собой уравнение, которое производит вычисления такие как: сложение, вычитание, умножение, деление. В Excel, в качестве значений формулы может быть число, адрес ячейки, дата, текст, булево значение (правда или ложь). чаще всего это либо число, либо адрес ячейки.
Константа – это значение, которое вводится непосредственно в формулу (число, текст, дата).
Переменная – это символьное обозначение значения, которое находится вне формулы.
Оператор вычисления определяет само вычисление, для того, чтобы Excel начал производить вычисление в формуле в начале надо поставить знак «=». Excel поддерживает работу со следующими типами арифметических операторов:
Во время введения формулы, операторы и значения отображаются и в ячейке, и в самой формуле, когда ввод закончен в строке формул будет формула, тогда как в ячейке – результат ее работы.
Порядок вычисления формулы
Когда в формуле применяется несколько вычислительных операторов, то порядок их вычисления подчинен общим математическим правилам. Следующий порядок применяется при расчете формулы (записано в порядке убывания приоритета вычисления):
- Отрицательные значения (-);
- Проценты (%);
- Возведение в степень (^);
- Умножение (*) и деление (/);
- Сумма (+) и разность (-).
Если рядом находятся два или более вычислительных операторов, которые находятся на одном уровне, то порядок их вычисления идет слева на право. Например, «=5+5+3-2», хотя здесь этот порядок имеет значение сугубо для логики произведения вычисления, т.к. отнимите вы число «2» в конце вычисления или в начале на результат не повлияет. Если возникает необходимость повысить приоритет вычисления отдельной части формулы, то, как и в математике, нужно эту часть заключить в круглые скобки.
Несколько примеров работы вычислений в Excel:
Горячее сочетание
Если во время ввода формулы вы передумали, то просто нажмите «Esc» и значение в ячейке не будет изменено. Если формулу уже изменили (завершили ввод с помощью Enter), то вернуть старое значение можно с помощью команды «Отменить» на панели быстрого доступа или сочетания Ctrl+Z.
Для того, чтобы отредактировать существующую формулу, необходимо установить курсор в ячейку с формулой и нажать клавишу «F2», либо кликнуть в строку формул, либо дважды кликнуть по целевой ячейке.
- Использование ссылок в формулах
Видеоверсия
Текстовая версия
Ранее упоминалось, что одним из значений, которые используются в формулах, являются переменные. Переменные используются намного чаще нежели константы, более того, рекомендуется все константы заменять переменными (подробнее об этом поговорим в разрезе рассмотрения абсолютных ссылок).
Каждая ячейка находится на пересечении строк и столбцов и имеет соответствующее обозначение:
- «E2» – столбец «E», вторая строка;
- «F2» – столбец «F», вторая строка;
- «G2» – столбец «F», вторая строка.
Преимущество такой записи заключается в том, что при изменении значений в ячейках, задействованных в формуле, результат вычисления формулы также изменится. Естественно формулы могут содержать и ссылки (переменные), и числа (константы) одновременно.
В рассмотренном примере был рассмотрен самый простой тип ссылок – относительные ссылки, принцип его работы легко понять на примере пересчета дневного заработка в другою валюту.
Простая формула пересчета актуального заработка в рубли вводится в ячейку C2, а потом, с помощью маркера автозаполнения растягивается на остальные дни, как можно заметить, для ячейки C3 в формуле используется заработок за 02.03.2016, хотя формула вводилась «=B2*71», т.е. при сдвиге на одну ячейку вниз, введенная относительная ссылка, также меняет свой адрес. То же самое происходит, если растянуть маркер автозаполнения в любую из сторон (влево, вправо, вверх или вниз), относительная ссылка всегда будет находится на одном расстоянии от ячейки с формулой, где она используется. В данном случае, это на одну ячейку влево, т.е., если потянуть маркер автозаполнения вправо, то в формуле будет использоваться столбец «C» (и число в зависимости от текущей строки).
Ссылки в формулы Excel можно вводить непосредственно с клавиатуры (только не забыть указывать названия столбцов в английской раскладке), но более распространенным способом является клик левой клавиши мышки по той ячейке, использование которой предполагается в формуле. Например, в примере выше можно посчитать общую сумму заработка просто кликая на целевые ячейки и плюсуя их.
Для улучшенного визуального восприятия ссылок в формуле, ячейки подсвечиваются различными цветами.
В примере перерасчета заработка из долларов в рубли мы использовали курс пересчета как константу, т.е. вводили число непосредственно в саму формулу. Если курс будет меняться (а он будет меняться), то придется переделать формулу и не забыть воспользоваться автозаполнением для размножения ее на другие ячейки, что довольно неудобно.
Абсолютная ссылка – это ссылка на ячейку, адрес которой не меняется при автозаполнении диапазонов. Для того, чтобы сделать ссылку абсолютной нужно в обозначении ссылки перед идентификатором столбца и строки поставить по знаку доллара.
Знак доллара можно ввести с клавиатуры, но быстрее будет нажать клавишу «F4» на относительной ссылке, т.е. на ссылке на которой установлен курсор. Повторное нажатие на клавишу «F4» будет убирать/добавлять знак $ возле строки/столбца. Будут появляться промежуточные значения, ссылки с одним знаком доллара – это смешанные ссылки, их рассмотрение будет чуть позже.
Теперь, когда курс в формуле зафиксирован абсолютной ссылкой, то при его изменении, достаточно единожды изменить ячейку F1 и все формулы, которые используют значение данной ячейки будут пересчитаны автоматически, а при растягивании формулы в любую из сторон формула всегда будет ссылаться на одну и ту же конкретную ячейку.
В начале рассмотрения данного вопроса мы упоминали, что в формулах использование констант лучше свести к минимуму. Лучшим решением будет заменить их абсолютными ссылками, которую, к тому же, можно еще и будет подписать.
При автозаполнении относительная ссылка будет изменяться всегда, вне зависимости от направления автозаполнения, абсолютная никогда не будет изменяться. Как быть, если нужно, чтобы при автозаполнении формулы вниз или вверх ссылка менялась, а при движении влево или вправо – нет, или наоборот, при растягивании влево или вправо изменялась, а при движении вверх или вниз оставалась неизменной?
В таком случае следует использовать смешанные ссылки. Смешанная ссылка – это ссылка, которая по одному из направлений (строке или столбце) абсолютная, а по другому – относительная. Какая часть ссылки ведет себя как абсолютная ссылка определяется знаком доллара.
Например, в рассмотренном примере перевода дневного заработка, абсолютную ссылку курса рубля к доллару можно заменить на F$1 и ничего не поменяется, т.к. строку мы зафиксировали, а столбец и так не менялся. Чтобы сделать более наглядную презентацию, можно расширить таблицу пересчетами в дополнительные валюты.
Здесь используется два вида смешанных ссылок: первый ссылается на сумму заработка в долларах и зафиксирован по столбцам позволяет, растягивая формулу вниз, использовать для каждого дня свой заработок, а второй ссылается на курс зафиксирован по строкам позволяет изменять курс при автозаполнении формулы влево или вправо. Здесь важно понимать, что для пересчета заработка в три валюты формула была введена всего ОДИН! раз в ячейку C4. Все остальные ячейки были заполнены с помощью автозаполнения.
Какие еще бывают типы ссылок
Все ссылки, которые мы рассматривали, ссылались на ячейки того же листа, где была расположена формула, однако пользователь может делать ссылки на ячейки другого листа. При этом все правила, рассмотренные ранее об относительны, абсолютных и смешанных ссылках, работают также, например, можно вынести курсы валют на другой лист, единственное что изменится, так это то, что перед ссылкой будет имя листа, которое заключено в одинарные кавычки и отделено от самой ссылки восклицательным знаком.
Это еще не все ссылаться можно и на ячейки, которые находятся в других книгах, это уже внешние ссылки, в этом случае в записи ссылки будет фигурировать и имя книги.
- Использование диапазонов данных в формулах
Видеоверсия
Текстовая версия
В Excel группа ячеек именуется диапазоном. Группы ячеек могут быть смежными, а могут находится на расстоянии друг от друга. Вы можете назначать имена группам ячеек, менять границы диапазонов, после определения их имени и использовать имена в формулах вместо простых безымянных ссылок на ячейки.
Именование диапазонов данных
Если вы часто работаете с определенным диапазоном, дать ему имя бывает достаточно удобно, чтобы в последствии оперировать не безымянным диапазоном/ячейкой: «C3:C15», а понятным «прибыль_2015» и т.п.
Для именования и навигации по именованным диапазонам и ячейкам используется группа «Определенные имена» вкладки «Формулы», а также окно «Имя» (Name box), которое мы начали рассматривать во втором вопросе второго занятия.
Самый простой способ именования диапазона с помощью окошка «Имя» присвоит имя диапазону с параметрами по умолчанию, т.е. именованный диапазон будет работать на всех листах книги, если давать имя с помощью команды «Присвоить имя», то в появившемся диалоговом окне можно настроить дополнительные параметры, самым важным из которых является область действия именованного диапазона.
По умолчанию область действия определена на уровне книги, т.е., если вы создаете на одном из листов книги именованный диапазон, то его можно будет использовать на любом листе книги, а также нельзя создать второй именованный диапазон с таким именем. Пользователь может изменить область действия именованного диапазона во время его создания, выбрав один из существующих листов в книге. Этот лист не обязан совпадать с листом, на котором расположен сам диапазон, в этом случае можно создать несколько диапазонов с одинаковыми именами, но каждый будет действовать на «своем» листе.
Еще один способ быстрого создания именованного диапазона – это с использованием команды «Создать из выделенного», в той же группе «Определенные имена». Суть заключается в том, что выделяется диапазон вместе с заголовком, который и будет носить имя диапазона остальной части диапазона. Excel, кстати, сам определил какую ячейку выделить под имя диапазона.
Выделение столбца «В евро» и использование команды «Создать из выделенного» равнозначно выделению диапазона E4:E13 и присвоение ему имени «В_евро».
Посмотреть все именованные диапазоны, удалить ненужные, изменить у какого-нибудь диапазона его диапазон можно в диспетчере имен, который вызывается одноименной командой из группы «Определенные имена».
Как использовать именованные диапазоны
Именованные диапазоны используются в формулах наравне со стандартными диапазонами, но есть некоторые особенности. Во-первых, именованный диапазон ВСЕГДА является абсолютной ссылкой, во-вторых, в формуле достаточно начать писать имя именованного диапазона и Excel его предложит, если есть несколько похожих («заработок_доллары», «заработок_гривны» и т.д.) то можно мышкой будет выбрать нужный.
Именованный диапазон в формулу можно вставить с помощью команды «Использовать в формуле» группы «Определенные имена».
Именованные диапазоны идеально подходят для использования совместно с функциями, работающими с диапазонами, плотно к рассмотрению функций мы подойдем чуть позже, а начнем уже сейчас.
Например, в примере расчета и перевода в различные валюты, дневного заработка мы подсчитывали общую сумму в долларах простым сложением дневных доходов. В реальной работе Excel так делать неправильно, поскольку есть функции автоматизирующие данные операции. Подсчитаем суму доходов в разных валютах, используя ссылки на диапазоны, а потом сделаем тоже, только в качестве аргументов введем имена диапазонов.
Если не использовать имена ячеек, то можно подсчитать сумму заработка в одной валюте, а для других заполнить с помощью автозаполнения. С именованными диапазонами такой «фокус» не пройдет, т.к. диапазону присваивается абсолютная ссылка, однако, если нужно будет просуммировать диапазон, который находится вне поля зрения, то использование имени, вместо ссылок будет предпочтительнее.
Если в книге создано много именованных диапазонов, или вы просто не помните имя нужного диапазона ячеек, в помощь придет диалоговое окно «Вставка имени», которое удобно использовать при составлении формулы и которое вызывается с помощью команды «Использовать в формуле», группа «Определенные имена».
В заключение рассмотрение темы именования диапазонов данных можно указать правила для создания диапазонов:
- Длина имени не более 255 символов.
- Начинаться имя диапазона может либо с буквы, либо с символов «_» и «\». В самом имени можно использовать цифры, буквы, знак подчеркивания. Например, «\имя_ячейки», или «_имя\ячейки.1» – допустимые названия, а «1имя_ячейки» – нельзя т.к. начинается с цифры.
- Имя не может состоять из одиночных букв: «R», «r», «C»,»c», поскольку их ввод в окошко «Имя» приводит к выделению строки (row) или столбца (column).
- В именах нельзя использовать пробелы, Microsoft рекомендует их заменять символом подчеркивания «_» или точкой «.». Например, «доходы_октябрь», «прибыль.год».
- Имя диапазона не может быть таким же как имя ссылки, например, «A5» или «$B$5».
- Введение в функции. Отображение дат и времени с помощью функций
Видеоверсия
Текстовая версия
Некоторые вычисления пользователи совершают чаще остальных, некоторые вычисления являются обязательными для определенной области. Чтобы каждый раз не вводить формулу вручную в Excel разработали функции. Функция фактически являет собой предопределенную формулу, например, формулу суммы, которую мы ранее уже немного рассматривали.
В Excel предусмотрено большое количество функций от достаточно простых, например, среднее, подсчет количества ячеек, минимальное, сумма и т.п., до сложных статистических или финансовых вычислений, например, дисперсия, среднеквадратическое отклонение и т.д.
Удобный справочник по работе с функциями представлен на нашем сайте:
Введение в функций
На заметку
Названия функций не чувствительны к регистру, однако, в Excel они пишутся заглавными буквами для удобочитаемости. Правда пользователю писать заглавными нет надобности, поскольку Excel самостоятельно преобразует в имени функции строчные буквы в прописные.
Вызвать функцию для вставки в формулу можно несколькими способами:
Как вы уже заметили функции суммы, среднего, максимума и минимума используются чаще остальных, поэтому они даже выведены в отдельную группу. На самом деле очень быстро узнать эти величины можно даже без ввода формул. На строке состояния у пользователя есть возможность вывести быстрый подсчет среднего, количества, количества числе, максимум и минимум (галочкой отмечены выведенные на панель состояния подсчеты в данный момент).
Для суммы есть горячее сочетание клавиш «Alt+=», можно просто ввести его возле диапазона с данными и Excel выделит наиболее подходящий (по мнению программы) диапазон, который можно откорректировать, а можно выделить диапазон и нажать горячее сочетание и Excel в зависимости от диапазона (горизонтальный или вертикальный) поместит суммы в ячейку под, или над диапазоном.
Функции даты и времени
В Excel даты фактически являют собой числовые значения, но отформатированы так чтобы выглядеть как дата. То де самое относится и ко времени. Данное утверждение легко проверить просто, введя любое положительное число и отформатировав его соответствующим образом. Именно по этой причине возможны арифметические операции с датами, например, можно вычислить количество дней между двумя датами просто вычтя из одной даты другую.
Отправной точкой для дат является 1 января 1900 года, т.е. именно в эту дату будет преобразовано число «1», если выставить соответствующе форматирование, «2» = это 2 января 1900 года и т.д. Даты можно складывать, вычитать, умножать, делить ровно также, как это делается с числами.
В Excel по умолчанию все ячейки имеют числовой формат «Общий», это означает, что табличный процессор попытается подстроить форматирование в зависимости от введенных данных.
Например, введя «15.03.2016» числовой формат будет установлен на «Дата», если потом просто удалить дату с помощью клавиши Del (равнозначно очистке содержимого) и ввести число, например, 5, то в ячейке отобразится дата: 05.01.1900. Поэтому нужно либо полностью очищать ячейку (вместе с форматами), либо после ввода данных изменить числовой формат ячейки.
Функция СЕГОДНЯ (TODAY)
Функция СЕГОДНЯ () или TODAY () в английской версии Excel проста в понимании и ее синтаксист предельно прост, т.к. аргументы отсутствуют в принципе. Данная функция возвращает текущую дату. При открытии книги всегда будет отображена актуальная дата, поэтому если нужно зафиксировать конкретную дату ее придется ввести вручную.
Ввод любой формулы должен начинаться со знака «=», если формула состоит из одной функции, то перед ней следует поставить «=», если функция добавляется в середине формулы повторно равно ставить нельзя. В формуле может присутствовать только один знак «=».
Само по себе использование функции СЕГОДНЯ выглядит не самым интересным занятием, но нужно понимать, что при построении формул мы может использовать несколько функций. Для примера можно использовать СЕГОДНЯ, при вычислении своего возраста, в этом случае формула примет вид:
Функция ТДАТА (NOW)
Функция ТДАТА () или NOW () в английской версии Excel, очень похожа на предыдущую за тем лишь исключением, что возвращает не просто текущую дату, но и точное время. Время в Excel представлено дробным числом.
Обновление текущего времени происходит каждый раз при открытии файла, или при пересчете листа.
- Работа с часто используемыми функциями (Автосумма)
Видеоверсия
Текстовая версия
В Excel множество функций, некоторые из них используются редко, некоторые будут интересны только определенной категории работников, а есть функции, которые будут полезны всегда и везде, не зависимо от вашей специальности и вида расчетов.
В Microsoft это тоже понимают, поэтому собрали такие функции не просто в отдельный набор, который называется «Автосумма» но и разместили его в различных местах интерфейса программы Excel.
В «Автосумму» входят функции: , и .
Группу функций «Автосумма» можно найти на вкладке «Главная» в группе «Редактирование».
Группа функций «Автосумма» представлена на вкладке «Формулы» в группе «Библиотека функций».
Функция СУММ (SUM)