Как прописать сложную формулу в экселе. Использование констант массива. Условное форматирование и объединение ячеек.
Вводить формулу надо со знака равенства. Это надо для того, чтобы Excel понял, что в ячейку вводится именно формула, а не данные.
Выделим произвольную ячейку, например А1. В строке формул введем =2+3 и нажмем Enter. В ячейке появится результат (5). А в строке формул останется сама формула.
Поэкспериментируйте с различными арифметическими операторами: сложения (+), вычитания (-), умножения (*), деления (/). Чтобы их правильно использовать надо четко представлять их приоритет.
- В первую очередь выполняются выражения внутри скобок.
- Умножение и деление имеют более высокий приоритет чем сложение и вычитание.
- Операторы с одинаковым приоритетом выполняются слева направо.
Мой вам совет - ИСПОЛЬЗУЙТЕ СКОБКИ. В этом случае вы обезопасите себя от случайной ошибки в вычислениях с одной стороны, а с другой - скобки значительно облегчают чтение и анализ формул. Если в формуле количество закрывающих и открывающих скобок не совпадает, Excel выдаст сообщение об ошибке и предложит вариант ее исправления. Сразу после ввода закрывающей скобки Excel отображает жирным шрифтом (или другим цветом) последнюю пару скобок, что очень удобно при наличии в формуле большого количества скобок.
Теперь давайте попробуем поработать с использованием в формулах ссылок на другие ячейки.
Введите в ячейку А1 число 10, а в ячейку А2 - число 15. В ячейке А3 введите формулу =А1+А2. В ячейке А3 появится сумма ячеек А1 и А2 - 25. Поменяйте значения ячеек А1 и А2 (но не А3!). После смены значений в ячейках А1 и А2 автоматически пересчитывается значение ячейки А3 (согласно формулы).
Дабы не ошибиться при вводе адресов ячеек, можно использовать при вводе ссылок мышь. В нашем случае надо проделать следующее:
- Выделить ячейку А3 и ввести в строке формул знак равенства.
- Щелкнуть на ячейке А1 и ввести знак плюс.
- Щелкнуть на ячейке А2 и нажать Enter.
Результат будет аналогичным.
Относительные, абсолютные и смешанные ссылки
Чтобы лучше разобраться в различиях ссылок, давайте поэкспериментируем.
- А1 - 20 В1 - 200
- А2 - 30 В2 - 300
В ячейку А3 введите формулу =А1+А2 и нажмите Enter.
Теперь установите курсор на правый нижний угол ячейки А3, нажмите правую кнопку мыши и протяните на ячейку В3 и отпустите кнопку мыши. Появится контекстное меню, в котором надо выбрать "Копировать ячейки".
После этого значение формулы из ячейки А3 будет скопировано в ячейку В3. Активизируйте ячейку В3 и посмотрите какая формула получилась - В1+В2. Почему так получилось? Когда мы записали в ячейке А3 формулу А1+А2, Excel интерпретировал эту запись так: "Взять значения из ячейки, расположенной в текущем столбце на две строки выше и сложить со значением ячейки, расположенной в текущем столбце на одну строку выше". Т.е. скопировав формулу из ячейки А3, например, в ячейку С43, мы получим - С41+С42. Вот в этом прелесть относительных ссылок, формула как бы сама подстраивается под наши задачи.
Введите в ячейки следующие значения:
- А1 - 20 В1 - 200
- А2 - 30 В2 - 300
- Введите в ячейку С1 число 5.
В ячейку А3 введите следующую формулу =А1+А2+$C$1. Аналогичным образом скопируйте формулу из А3 в В3. Посмотрите что получилось. Относительные ссылки "подстроились" под новые значения, а вот абсолютная - осталась неизменной.
Попробуйте теперь сами поэкспериментировать со смешанными ссылками и посмотрите как они работают. Можно ссылаться на другие листы той же книги точно так, как и на ячейки текущего листа. Даже можно ссылаться на листы других книг. В этом случае ссылка будет называться внешней ссылкой.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А5 (Лист2), надо проделать следующее:
- выделить ячейку А1 и ввести знак равенства;
- щелкните на ярлыке "Лист 2";
- щелкните на ячейке А5 и нажмите клавишу ввода;
- после этого будет активизирован опять Лист 1 и в ячейке А1 появится следующая формула =Лист2!А5.
Редактирование формул происходит аналогично редактированию текстовых значений в ячейках. Т.е. надо активизировать ячейку с формулой выделением или двойным щелчком мыши, а затем произвести редактирование, используя, при необходимости, клавиши Del, Backspace. Фиксация изменений выполняется клавишей Enter.
Использование текста в формулах
С текстовыми значениями можно выполнять математические операции, если текстовые значения содержат только следующие символы:
Цифры от 0 до 9 , + - е Е /
Еще можно использовать пять символов числового форматирования:
$ % () пробел
При этом текст должен быть заключен в двойные кавычки.
Неправильно: =$55+$33
Правильно: ="$55"+$"33"
При выполнении вычислений Excel преобразует числовой текст в числовые значения, так результатом вышеуказанной формулы будет значение 88.
Для объединения текстовых значений служит текстовый оператор & (амперсанд). Например, если ячейка А1 содержит текстовое значение "Иван", а ячейка А2 - "Петров", то введя в ячейку А3 следующую формулу =А1&А2 , получим "ИванПетров".
Для вставки пробела между именем и фамилией надо написать так =А1&" "&А2 .
Амперсанд можно использовать для объединения ячеек с разными типами данных. Так, если в ячейке А1 находится число 10, а в ячейке А2 - текст "мешков", то в результате действия формулы =А1&А2 , мы получим "10мешков". Причем результатом такого объединения будет текстовое значение.
Во второй части цикла Excel 2010 для начинающих вы научитесь связывать ячейки таблиц математическими формулами, добавлять строки и столбцы к уже готовой таблице, узнаете о функции автозаполнения и многое другое.
Введение
В первой части цикла «Excel 2010 для начинающих» мы познакомились с самыми азами программы Excel, научившись в ней создавать обычные таблицы. Строго говоря, дело это нехитрое и конечно возможности этой программы намного шире.
Основное преимущество электронных таблиц состоит в том, что отдельные клетки с данными можно связать между собой математическими формулами. То есть при изменении значения одной из связанных между собой ячеек, данные других будут пересчитаны автоматически.
В этой части мы разберемся, какую же пользу могут принести такие возможности на примере уже созданной нами таблицы бюджетных расходов, для чего нам придется научиться составлять простые формулы. Так же мы познакомимся с функцией автозаполнения ячеек и узнаем, каким образом можно вставлять в таблицу дополнительные строки и столбцы, а так же объединять в ней ячейки.
Выполнение базовых арифметических операций
Помимо создания обычных таблиц, Excel можно использовать для выполнения в них арифметических операций, таких как: сложение, вычитание, умножение и деление.
Для выполнения расчетов в любой ячейке таблицы необходимо создать внутри нее простейшую формулу , которая всегда должна начинаться со знака равенства (=). Для указания математических операций внутри формулы используются обычные арифметические операторы:
Например, давайте представим, что нам необходимо сложить два числа - «12» и «7». Установите курсор мыши в любую ячейку и напечатайте следующее выражение: «=12+7». По окончании ввода нажмите клавишу «Enter» и в ячейке отобразится результат вычисления - «19».
Чтобы узнать, что же на самом деле содержит ячейка - формулу или число, - необходимо ее выделить и посмотреть на строку формул - область находящуюся сразу же над наименованиями столбцов. В нашем случае в ней как раз отображается формула, которую мы только что вводили.
После проведения всех операций, обратите внимание на результат деления чисел 12 на 7, который получился не целым (1,714286) и содержит довольно много цифр после запятой. В большинстве случаев такая точность не требуется, да и столь длинные числа будут только загромождать таблицу.
Чтобы это исправить, выделите ячейку с числом, у которого необходимо изменить количество десятичных знаков после запятой и на вкладке Главная в группе Число выберите команду Уменьшить разрядность . Каждое нажатие на эту кнопку убирает один знак.
Слева от команды Уменьшить разрядность находится кнопка, выполняющая обратную операцию - увеличивает число знаков после запятой для отображения более точных значений.
Составление формул
Теперь давайте вернемся к таблице бюджетных расходов, которую мы создавали в первой части этого цикла.
На данный момент в ней зафиксированы ежемесячные личные расходы по конкретным статьям. Например, можно узнать, сколько было истрачено в феврале на продукты питания или в марте на обслуживание автомобиля. А вот общие ежемесячные расходы здесь не указаны, хотя именно эти показатели для многих являются самыми важными. Давайте исправим эту ситуацию, добавив внизу таблицы строчку «Ежемесячные расходы» и рассчитаем ее значения.
Чтобы посчитать суммарный расход за январь в ячейке B7 можно написать следующее выражение: «=18250+5100+6250+2500+3300» и нажать Enter, после чего вы увидите результат вычисления. Это является примером применения простейшей формулы, составление которой ничем не отличается от вычислений на калькуляторе. Разве что знак равно ставится вначале выражения, а не в конце.
А теперь представьте, что при указании значений одной или нескольких статей расходов вы допустили ошибку. В этом случае, вам придется скорректировать не только данные в ячейках с указанием расходов, но и формулу вычисления суммарных трат. Конечно, это очень неудобно и поэтому в Excel при составлении формул часто используются не конкретные числовые значения, а адреса и диапазоны ячеек .
С учетом этого давайте изменим нашу формулу вычисления суммарных ежемесячных расходов.
В ячейку B7, введите знак равно (=) и… вместо того, чтобы вручную вбивать значение клетки B2, щелкните по ней левой кнопкой мыши. После этого вокруг ячейки появится пунктирная выделительная рамка, которая показывает, что ее значение попало в формулу. Теперь введите знак «+» и щелкните по ячейке B3. Далее проделайте тоже самое с ячейками B4, B5 и B6, а затем нажмите клавишу ВВОД (Enter), после чего появится то же значение суммы, что и в первом случае.
Выделите вновь ячейку B7 и посмотрите на строку формул. Видно, что вместо цифр - значений ячеек, в формуле содержатся их адреса. Это очень важный момент, так как мы только что построили формулу не из конкретных чисел, а из значений ячеек, которые могут со временем изменяться. Например, если теперь поменять сумму расходов на покупку вещей в январе, то весь ежемесячный суммарный расход будет пересчитан автоматически. Попробуйте.
Теперь давайте предположим, что просуммировать нужно не пять значений, как в нашем примере, а сто или двести. Как вы понимаете, использовать вышеописанный метод построения формул в таком случае очень неудобно. В этом случае лучше воспользоваться специальной кнопкой «Автосумма», которая позволяет вычислить сумму нескольких ячеек в пределах одного столбца или строки. В Excel можно считать не только суммы столбцов, но и строк, так что используем ее для вычисления, например, общих расходов на продукты питания за полгода.
Установите курсор на пустой клетке сбоку нужной строки (в нашем случае это H2). Затем нажмите кнопку Сумма на закладке Главная в группе Редактирование . Теперь, вернемся к таблице и посмотрим, что же произошло.
В выбранной нами ячейке появилась формула с интервалом ячеек, значения которых требуется просуммировать. При этом опять появилась пунктирная выделительная рамка. Только в этот раз она обрамляет не одну клетку, а весь диапазон ячеек, сумму которых требуется посчитать.
Теперь посмотрим на саму формулу. Как и раньше, вначале идет знак равенства, но на этот раз за ним следует функция «СУММ» - заранее определенная формула, которая выполнит сложение значений указанных ячеек. Сразу за функцией идут скобки расположенные вокруг адресов клеток, значения которых нужно просуммировать, называемые аргументом формулы . Обратите внимание, что в формуле не указаны все адреса суммируемых ячеек, а лишь первой и последней. Двоеточие между ними обозначает, что указан диапазон клеток от B2 до G2.
После нажатия Enter, в выбранной ячейке появится результат, но на этом возможности кнопки Сумма не заканчиваются. Щелкните на стрелочку рядом с ней и откроется список, содержащий функции для вычисления средних значений (Среднее), количества введенных данных (Число), максимальных (Максимум) и минимальных (Минимум) значений.
Итак, в нашей таблице мы посчитали общие траты за январь и суммарный расход на продукты питания за полгода. При этом сделали это двумя разными способами - сначала с использованием в формуле адресов ячеек, а затем, функции и диапазона. Теперь, самое время закончить расчеты для оставшихся ячеек, посчитав общие затраты по остальным месяцам и статьям расходов.
Автозаполнение
Для расчета оставшихся сумм воспользуемся одной замечательной особенностью программы Excel, которая заключается в возможности автоматизировать процесс заполнения ячеек систематизированными данными.
Иногда в Excel приходится вводить похожие однотипные данные в определенной последовательности, например дни недели, даты или порядковые номера строк. Помните, в первой части этого цикла в шапке таблицы мы вводили название месяца в каждый столбец по отдельности? На самом деле, совершенно необязательно было вводить весь этот список вручную, так как приложение во многих случаях может сделать это за вас.
Давайте сотрем все названия месяцев в шапке нашей таблицы, кроме первого. Теперь выделите ячейку с надписью «Январь» и переместите указатель мыши в правый ее нижний угол, что бы он принял форму крестика, который называется маркером заполнения . Зажмите левую кнопку мыши и перетащите его вправо.
На экране появится всплывающая подсказка, которая сообщит вам то значение, которое программа собирается вставить в следующую клетку. В нашем случае это «Февраль». По мере перемещения маркера вниз она будет меняться на названия других месяцев, что поможет вам понять, где нужно остановиться. После того как кнопка будет отпущена, список заполнится автоматически.
Конечно, 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.
Умение создавать и работать с формулами в Excel – вот что отличает опытного пользователя от дилетанта. Именно в формулах заложены безграничные возможности и потенциал, который не виден со стороны. В этом уроке Вы научитесь создавать формулы в Excel несколькими способами. Это будут простые формулы, создать которые не составит большого труда.
Создаем первую простую формулу в Excel
В следующем примере мы посчитаем простенький бюджет за два месяца, для этого создадим несложную формулу со ссылками на ячейки.
Если результат вычислений не поместится в ячейку, то вместо ожидаемого результата могут появиться знаки решетки (#######). Это означает, что ширины столбца недостаточно для отображения всего содержимого. Просто увеличьте ширину столбца для отображения данных.
Основное преимущество формул со ссылками
Главное преимущество ссылок в том, что они позволяют вносить изменения в данные на листе Excel без необходимости переписывать сами формулы. В следующем примере мы изменим значение ячейки B1 с $1200 на $1800. Формула будет автоматически пересчитана и отобразится новое значение.
Excel не всегда предупреждает об ошибке в формуле, поэтому Вам необходимо самостоятельно проверять все ваши формулы. Чтобы узнать, как это можно сделать, изучите урок Двойная проверка формул в Excel.
Создаем формулу в Excel, выбирая ячейку мышкой
Вместо того, чтобы вводить адреса ячеек вручную, Вы можете всего лишь щелкнуть по ячейке, которую хотите включить в формулу. Этот прием может сэкономить вам много времени и сил при создании формул в Excel. В следующем примере мы создадим формулу для расчета стоимости заказа нескольких коробок пластиковой посуды.
Формулы можно копировать в смежные ячейки при помощи маркера автозаполнения. Это позволит сэкономить время, когда необходимо использовать одну и ту же формулу множество раз. Изучите уроки раздела Относительные и абсолютные ссылки, чтобы получить дополнительную информацию.
Вот и отшумели первомайские праздники. Пора посчитать , сколько средств мы потратили и сколько у нас осталось на День Победы.☺
Формулы являются основой вычислительных возможностей Excel. Сегодня хочу рассказать как правильно вводить формулы и какие дополнительные возможности можно использовать для удобства использования.
Основные действия
Для того, чтобы ввести формулу, установите курсор на нужную ячейку и нажмите клавишу равенства (=).
Ввод формулы вручную
После этого введите выражение, которое хотите вычислить (например =11+12 ) и нажмите клавишу Enter .
В итоге у вас в ячейке получиться готовый результат.
Если вы установите указатель мыши снова на эту ячейку, то в основном поле отобразится результат , а в строке под основным меню будет видна введенная формула .
Основные арифметические действия присутствуют на правой (цифровой) части клавиатуры. Это + — * / (сложение, вычитание,умножение, деление ).
Ввод формулы посредством выбора ячеек со значениями
Несмотря на длинное и непонятное название, это самый распространенный вариант расчета формул.
Например, имеется несколько значений стоимости товара, количество и требуется вычислить общую сумму затрат на его покупку.
В нужной ячейке нажмите «Равно» и затем отметьте нужную ячейку 1 , затем введите действие и укажите ячейку 2 (они выделяются цветной рамкой) . По окончании ввода нажмите клавишу «Enter» .
Данный способ очень удобен. Он позволяет менять значение начальных показателей (стоимость и количество ) и мгновенно получать новый результат вычислений (Сумма ).
При вводе таких формул, вы можете использовать ячейки и с других листов документа, а также других открытых файлов Excel.
Автозаполнение ячеек значениями
Если у вас в таблице несколько значений, к которым требуется применить одну и ту же формулу , то вы можете воспользоваться автоматическим заполнением ячеек.
Для этого, установите курсор на правый нижний угол ячейки с формулой, нажмите левую клавишу, «протяните» ячейки до последней необходимой и затем отпустите кнопку.
В итоге у вас получится значительная экономия времени на совершение однообразных операций.
Автосумма
Формула суммы в Excel выделена в виде отдельной кнопки на панели меню. Это неслучайно, ведь очень часто у нас есть огромный список (столбец) значений, сумму которых требуется вычислить.
Делается это так: установите курсор на ячейку ниже столбца с значениями и нажмите кнопку «Автосумма» .
В итоге получится сумма значений, которые находились в выделенной пунктиром области автосуммирования.
Бонус
Для удобства анализа и работы в Excel вы можете воспользоваться дополнительными возможностями представления данных.
Сортировка и Фильтр
В Excel существует возможность отсортировать значения в столбцах. Выделите нужный столбец и нажмите на кнопку . В открывшемся списке нажмите на нужный вам вариант сортировки (на увеличение или уменьшение значений). Особенно удобно пользоваться этой функцией, когда требуется найти значения в некотором диапазоне и отсечь другие.
Пункт меню Фильтр расположен рядом. Он позволяет показывать на экране лишь те значения, которые вы задали. Остальные будут скрыты.
Например, требуется показать значения стоимости равное 100.
Нажмите на кнопку фильтра (с треугольничком ) и отмечаем нужные значения, затем жмем ОК.
Закрепление областей в Excel
Эта функция полезна, когда столбцов или строк очень много . Встаньте на любую из обведенных ячеек (в зависимости от того, какую область вы хотите закрепить ). Перейдите в главном меню на вкладку «Вид» и выберите кнопку «Закрепить области» .
В итоге, вы сможете «пролистывать» значения, при этом заголовки строк или столбцов будут зафиксированы на экране. Попробуйте, в больших документах это очень удобная функция!
Конечно же, в статье описана лишь малая часть возможностей программы. Но использовании даже этих полезных мелочей ускоряет работу с документами и экономит ваше время.
