Доброго времени суток дорогой читатель. Продолжаем обсуждать тему работы с офисными программами. Эксель — мощнейший инструмент офисного пакета, позволяющий автоматизировать многие математические операции. В этой статье мы поговорим о том
Как использовать формулы в Exel
Для удобства восприятия я разделил формулы на несколько групп и буду показывать работу с ними от простого к сложному.
Нужно запомнить: вне зависимости от задачи все формулы начинаются со знака «=».
Сумма ячеек в экселе
Пожалуй самая востребованная формула. Пример простого написания формулы: =A1+B1
Давайте разберем ее. Вы уже наверное знаете, что таблица делится на столбцы, строки и ячейки. Столбцы маркируются с помощью букв «A, B, C» и т.д. Для строк используются цифры «1, 2, 3…».
Для определения ячейки используется комбинация из буквы и цифры, что соответствует столбцу и номеру строки в нем. Таким образом первая ячейка будет иметь индекс «А1». Ячейка ниже первой будет иметь обозначение «А2», так как находится в столбце «А» и во второй строке.
Давайте проверим как Вы восприняли данную информацию. Какой индекс будет иметь ячейка, которая находится справа от ячейки «А1» и расположена с ней в одной строке? Правильно! Эта ячейка будет иметь обозначение «B1», так как находится в столбце «В» и в первой строке.
Давайте вернемся к нашей формуле
Сумма двух ячеек в Exel
Данная формура суммирует значение двух ячеек. В нашем случае это ячейки «А1» и «В1». Результат выводится в ту ячейку где Вы прописали формулу. Принцип я думаю понятен. Теперь покажу как это сделать на практике.
Деление и умножение ячеек в экселе
Так же довольно проста в написании. Общую теорию мы уже разобрали, поэтому сразу переходим к практике.
Среднее значение в экселе
Еще одна полезная функция.
Логические функции в экселе
Самая сложная группа формул, которая позволяет значительно упростить процедуру обработки данных. Работает по такому принципу: если значение в ячейке соответствует заданным критериям — выполнить определенное действие.
Тема логических функций довольно обширная и подробно я разберу ее в отдельной статье, но базовые принципы я Вам покажу.
Excel необходим в случаях, когда вам нужно упорядочить, обработать и сохранить много информации. Он поможет автоматизировать вычисления, делает их проще и надежнее. Формулы в Excel позволяют проводить сколь угодно сложные вычисления и получать результаты моментально.
Как написать формулу в Excel
Прежде чем учиться этому, следует понять несколько базовых принципов.
- Каждая начинается со знака «=».
- Участвовать в вычислениях могут значения из ячеек и функции.
- В качестве привычных нам математических знаков операций используются операторы.
- При вставке записи в ячейке по умолчанию отражается результат вычислений.
- Посмотреть конструкцию можно в строке над таблицей.
Каждая ячейка в 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 – очень полезная программа, а вычисления в нем дают практически неограниченные возможности.
Отличного Вам дня!
Формула - это математическое выражение, которое создается для вычисления результата и которое может зависеть от содержимого других ячеек. Формула в ячейке может содержать данные, ссылки на другие ячейки, а также обозначение действий, которые необходимо выполнить.
Использование ссылок на ячейки позволяет пересчитывать результат по формулам, когда происходят изменения содержимого ячеек, включенных в формулы.
В Excel формулы начинаются со знака =. Скобки () могут использоваться для определения порядка математических операции.
Excel поддерживает следующие операторы:
- Арифметические операции:
- сложение (+);
- умножение (*);
- нахождение процента (%);
- вычитание (-);
- деление (/);
- экспонента (^).
- Операторы сравнения:
- = равно;
- < меньше;
- > больше;
- <= меньше или равно;
- >= больше или равно;
- <> не равно.
- Операторы связи:
- : диапазон;
- ; объединение;
- & оператор соединения текстов.
Таблица 22. Примеры формул
Упражнение
Вставка формулы -25-А1+АЗ
Предварительно введите любые числа в ячейки А1 и A3.
- Выберите необходимую ячейку, например В1.
- Начните ввод формулы со знака=.
- Введите число 25, затем оператор (знак -).
- Введите ссылку на первый операнд, например щелчком мыши на нужную ячейку А1.
- Введите следующий оператор(знак +).
- Щелкните мышью в той ячейке, которая является вторым операндом в формуле.
- Завершите ввод формулы нажатием клавиши Enter . В ячейке В1 получите результат.
Автосуммирование
Кнопка Автосумма (AutoSum) - ∑ может использоваться для автоматического создания формулы, которая суммирует область соседних ячеек, находящихся непосредственно слева в данной строке и непосредственно выше в данном столбце.
- Выберите ячейку, в которую надо поместить результат суммирования.
- Щелкните кнопку Автосумма - ∑ или нажмите комбинацию клавиш Alt+=. Excel примет решение, какую область включить в диапазон суммирования, и выделит ее пунктирной движущейся рамкой, называемой границей.
- Нажмите Enter для принятия области, которую выбрала программа Excel, или выберите с помощью мыши новую область и затем нажмите Enter.
Функция "Автосумма" автоматически трансформируется в случае добавления и удаления ячеек внутри области.
Упражнение
Создание таблицы и расчет по формулам
- Введите числовые данные в ячейки, как показано в табл. 23.
| А | В | С | D | Б | F | |
| 1 | ||||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| в | Другое | 8 | 10 | 9 | ||
| 7 | Всего | |||||
| 8 | Без высшего |
Таблица 23. Исходная таблица данных
- Выберите ячейку В7, в которой будет вычислена сумма по вертикали.
- Щелкните кнопку Автосумма - ∑ или нажмите Alt+= .
- Повторите действия пунктов 2 и 3 для ячеек С7 и D7.
Вычислите количество сотрудников без высшего образования (по формуле В7-ВЗ).
- Выберите ячейку В8 и наберите знак (=).
- Щелкните мышью в ячейке В7, которая является первым операндом в формуле.
- Введите с клавиатуры знак (-) и щелкните мышью в ячейке ВЗ, которая является вторым операндом в формуле (будет введена формула).
- Нажмите Enter (в ячейке В8 будет вычислен результат).
- Повторите пункты 5-8 для вычислений по соответствующим формулам в ячейках С8 и 08.
- Сохраните файл с именем Образование_сотрудников.х1s.
Таблица 24. Результат расчета
| А | B | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | ||
| 4 | Среднее спец. | 28 | 23 | 21 | ||
| 5 | ПТУ | 27 | 58 | 20 | ||
| 6 | Другое | 8 | 10 | 9 | ||
| 7 | Всего | 88 | 111 | 59 | ||
| 8 | Без высшего | 63 | 91 | 50 | ||
Тиражирование формул при помощи маркера заполнения
Область ячеек (ячейка) может быть размножена при помощи использования маркера заполнения. Как было показано в предыдущем разделе, маркер заполнения представляет собой контрольную точку в правом нижнем углу выделенной ячейки.
Часто бывает необходимо размножать не только данные, но и формулы, содержащие адресные ссылки. Процесс тиражирования формул при помощи маркера заполнения позволяет колировать формулу при одновременном изменении адресных ссылок в формуле.
- Выберите ячейку, содержащую формулу для тиражирования.
- Перетащите маркер заполнения в нужном направлении. Формула будет размножена во всех ячейках.
Обычно этот процесс используется при копировании формул внутри строк или столбцов, содержащих однотипные данные. При тиражировании формул с помощью маркера заполнения меняются так называемые относительные адреса ячеек в формуле (подробно относительные и абсолютные ссылки будут описаны далее).
Упражнение
Тиражирование формул
1.Откройте файл Образование_сотрудников.х1s.
- Введите в ячейку ЕЗ формулу для автосуммирования ячеек =СУММ(ВЗ:03).
- Скопируйте, перетащив маркер заполнения, формулу в ячейки Е4:Е8.
- Просмотрите как меняются относительные адреса ячеек в полученных формулах (табл. 25) и сохраните файл.
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | ||
| 3 | Высшее | 25 | 20 | 9 | =СУММ{ВЗ:03) | |
| 4 | Среднее спец. | 28 | 23 | 21 | =СУММ(В4:04) | |
| 5 | ПТУ | 27 | 58 | 20 | =СУММ(В5:05) | |
| 6 | Другое | 8 | 10 | 9 | =СУММ(В6:06) | |
| 7 | Всего | 88 | 111 | 58 | =СУММ(В7:07) | |
| 8 | Без высшего | 63 | 91 | 49 | =СУММ(В8:08) | |
Таблица 25. Изменение адресов ячеек при тиражировании формул
Относительные и абсолютные ссылки
Формулы, реализующие вычисления в таблицах, для адресации ячеек используют так называемые ссылки. Ссылка на ячейку может быть относительной или абсолютной.
Использование относительных ссылок аналогично указанию направления движения по улице - "идти три квартала на север, затем два квартала на запад". Следование этим инструкциям из различных начальных мест будет приводить в разные места назначения.
Например, формула, которая суммирует числа в столбце или строке, затем часто копируется для других номеров строк или столбцов. В таких формулах используются относительные ссылки (см. предыдущий пример в табл. 25).
Абсолютная ссылка на ячейку.иди область ячеек будет всегда ссылаться на один и тот же адрес строки и столбца. При сравнении с направлениями улиц это будет примерно следующее: "Идите на пересечение Арбата и Бульварного кольца". Вне зависимости от места старта это будет приводить к одному и тому же месту. Если формула требует, чтобы адрес ячейки оставался неизменным при копировании, то должна использоваться абсолютная ссылка (формат записи $А$1). Например, когда формула вычисляет доли от общей суммы, ссылка на ячейку, содержащую общую сумму, не должна изменяться при копировании.
Знак доллара ($) появится как перед ссылкой на столбец, так и перед ссылкой на строку (например, $С$2), Последовательное нажатие F4 будет добавлять или убирать знак перед номером столбца или строки в ссылке (С$2 или $С2 - так называемые смешанные ссылки).
- Создайте таблицу, аналогичную представленной ниже.
Таблица 26. Расчет зарплаты
- В ячейку СЗ введите формулу для расчета зарплаты Иванова =В1*ВЗ.
При тиражировании формулы данного примера с относительными ссылками в ячейке С4 появляется сообщение об ошибке (#ЗНАЧ!), так как изменится относительный адрес ячейки В1, и в ячейку С4 скопируется формула =В2*В4;
- Задайте абсолютную ссылку на ячейку В1, поставив курсор в строке формул на В1 и нажав клавишу F4, Формула в ячейке СЗ будет иметь вид =$В$1*ВЗ.
- Скопируйте формулу в ячейки С4 и С5.
- Сохраните файл (табл. 27) под именем Зарплата.xls.
Таблица 27. Итоги расчета зарплаты
Имена в формулах
Имена в формулах легче запомнить, чем адреса ячеек, поэтому вместо абсолютных ссылок можно использовать именованные области (одна или несколько ячеек). Необходимо соблюдать следующие правила при создании имен:
- имена могут содержать не более 255 символов;
- имена должны начинаться с буквы и могут содержать любой символ, кроме пробела;
- имена не должны быть похожи на ссылки, такие, как ВЗ, С4;
- имена не должны использовать функции Excel, такие, как СУММ, ЕСЛИ и т. п.
В меню Вставка, Имя существуют две различные команды создания именованных областей: Создать и Присвоить.
Команда Создать позволяет задать (ввести) требуемое имя (только одно ), команда Присвоить использует метки, размещенные на рабочем листе, в качестве имен областей (разрешается создавать сразу несколько имен ).
Создание имени
- Выделите ячейку В1 (табл. 26).
- Выберите в меню Вставка, Имя (Insert, Name) команду Присвоить (Define) .
- Введите имя Часовая ставка и нажмите ОК .
- Выделите ячейку В1 и убедитесь, что в поле имени указано Часовая ставка .
Создание нескольких имен
- Выделите ячейки ВЗ:С5 (табл. 27).
- Выберите в меню Вставка, Имя (Insert, Name) команду Создать (Create) , появится диалоговое окно Создать имена (рис. 88).
- Убедитесь, что переключатель в столбце слева помечен и нажмите ОК .
- Выделите ячейки ВЗ:СЗ и убедитесь, что в поле имени указано Иванов.
Рис. 88. Диалоговое окно Создать имена
Можно в формулу вставить имя вместо абсолютной ссылки.
- В строке формул установите курсор в то место, где будет добавлено имя.
- Выберите в меню Вставка, Имя (Insert, Name) команду Вставить (Paste), появится диалоговое окно Вставить имена.
- Выберите нужное имя из списка и нажмите ОК.
Ошибки в формулах
Бели при вводе формул или данных допущена ошибка, то в результирующей ячейке появляется сообщение об ошибке. Первым символом всех значений ошибок является символ #. Значения ошибок зависят от вида допущенной ошибки.
Excel может распознать далеко не все ошибки, но те, которые обнаружены, надо уметь исправить.
Ошибка # # # # появляется, когда вводимое число не умещается в ячейке. В этом случае следует увеличить ширину столбца.
Ошибка #ДЕЛ/0! появляется, когда в формуле делается попытка деления на нуль. Чаще всего это случается, когда в качестве делителя используется ссылка на ячейку, содержащую нулевое или пустое значение.
Ошибка #Н/Д! является сокращением термина "неопределенные данные". Эта ошибка указывает на использование в формуле ссылки на пустую ячейку.
Ошибка #ИМЯ? появляется, когда имя, используемое в формуле, было удалено или не было ранее определено. Для исправления определите или исправьте имя области данных, имя функции и др.
Ошибка #ПУСТО! появляется, когда задано пересечение двух областей, которые в действительности не имеют общих ячеек. Чаще всего ошибка указывает, что допущена ошибка при вводе ссылок на диапазоны ячеек.
Ошибка #ЧИСЛО! появляется, когда в функции с числовым аргументом используется неверный формат или значение аргумента.
Ошибка #ЗНАЧ! появляется, когда в формуле используется недопустимый тип аргумента или операнда. Например, вместо числового или логического значения для оператора или функции введен текст.
Кроме перечисленных ошибок, при вводе формул может появиться циклическая ссылка.
Циклическая ссылка возникает тогда, когда формула прямо или косвенно включает ссылки на свою собственную ячейку. Циклическая ссылка может вызывать искажения в вычислениях на рабочем листе и поэтому рассматривается как ошибка в большинстве приложений. При вводе циклической ссылки появляется предупредительное сообщение (рис. 89).
Для исправления ошибки удалите ячейку, которая вызвала циклическую ссылку, отредактируйте или введите заново формулу.
Функции в Excel
Более сложные вычисления в таблицах Excel осуществляются с помощью специальных функций (рис. 90). Список категорий функций доступен при выборе команды Функция в меню Вставка (Insert, Function).
Финансовые функции осуществляют такие расчеты, как вычисление суммы платежа по ссуде, величину выплаты прибыли на вложения и др.
Функции Дата и время позволяют работать со значениями даты и времени в формулах. Например, можно использовать в формуле текущую дату, воспользовавшись функцией СЕГОДНЯ .
Рис. 90. Мастер функций
Математические функции выполняют простые и сложные математические вычисления, например вычисление суммы диапазона ячеек, абсолютной величины числа, округление чисел и др.
Статистические функции позволяют выполнять статистический анализ данных. Например, можно определить среднее значение и дисперсию по выборке и многое другое.
Функции работы с базами данных можно использовать для выполнения расчетов и для отбора записей по условию.
Текстовые функции предоставляют пользователю возможность обработки текста. Например, можно объединить несколько строк с помощью функции СЦЕПИТЬ .
Логические функции предназначены для проверки одного или нескольких условий. Например, функция ЕСЛИ позволяет определить, выполняется ли указанное условие, и возвращает одно значение, если условие истинно, и другое, если оно ложно.
Функции Проверка свойств и значений предназначены для определения данных, хранимых в ячейке. Эти функции проверяют значения в ячейке по условию и возвращают в зависимости от результата значения ИСТИНА или ЛОЖЬ .
Для вычислений в таблице с помощью встроенных функций рекомендуется использовать мастер функций. Диалоговое окно мастера функций доступно при выборе команды Функция в меню Вставка или нажатии кнопки, на стандартной панели инструментов. В процессе диалога с мастером требуется задать аргументы выбранной функции, для этого необходимо заполнить поля в диалоговом окне соответствующими значениями или адресами ячеек таблицы.
Упражнение
Вычисление величины среднего значения для каждой строки в файле Образование.хls.
- Выделите ячейку F3 и нажмите на кнопку мастера функций.
- В первом окне диалога мастера функций из категории Статистические выберите функцию СРЗНАЧ , нажмите на кнопку Далее .
- Во втором диалоговом окне мастера функций должны быть заданы аргументы. Курсор ввода находится в поле ввода первого аргумента. В это поле в качестве аргумента число! введите адрес диапазона B3:D3 (рис. 91).
- Нажмите ОК .
- Скопируйте полученную формулу в ячейки F4:F6 и сохраните файл (табл. 28).
Рис. 91. Ввод аргумента в мастере функций
Таблица 28. Таблица результатов расчета с помощью мастера функций
| А | В | С | D | Е | F | |
| 1 | Распределение сотрудников по образованию | |||||
| 2 | Магнолия | Лилия | Фиалка | Всего | Среднее | |
| 3 | Высшее | 25 | 20 | 9 | 54 | 18 |
| 4 | Среднее спец. | 28 | 23 | 21 | 72 | 24 |
| 8 | ПТУ | 27 | 58 | 20 | 105 | 35 |
| в | Другое | 8 | 10 | 9 | 27 | 9 |
| 7 | Всего | 88 | 111 | 59 | 258 | 129 |
Для ввода диапазона ячеек в окно мастера функций можно мышью обвести на рабочем листе таблицы этот диапазон (в примере B3:D3). Если окно мастера функций закрывает нужные ячейки, можно передвинуть окно диалога. После выделения диапазона ячеек (B3:D3) вокруг него появится бегущая пунктирная рамка, а в поле аргумента автоматически появится адрес выделенного диапазона ячеек.
- Наиболее распространенная координатная ссылка - это использование буквы или букв, представляющих столбец, а за ней номер строки, в которой находится ячейка: например, А1 указывает на ячейку в столбце А и строке 1. Если вы добавите строки над ячейкой, то ссылка на ячейку изменится, чтобы отобразить ее новую позицию; добавление строки над ячейкой А1 и столбца слева от нее, изменит ссылку на нее на В2 во всех формулах, которые ее используют.
- Разновидность этой формулы - сделать строковую либо столбцовую ссылки абсолютными, добавив знак доллара ($) перед ними. Хотя ссылка на ячейку A1 изменится, если будет добавлена строка над ней или столбец слева от нее, ссылка $A$1 всегда будет указывать на верхнюю левую левую ячейку на листе; таким образом, в формуле, ячейка $A$1 может иметь другое или даже недопустимое значение в формуле, если строки или столбцы вставляются на лист. (При желании, вы можете использовать абсолютную ссылку для столбца или строки отдельно, например, $A1 или A$1).
- Другой способ сделать ссылку на ячейку - это числовой метод, в формате RxCy, где "R" указывает на "строку," "C" указывает на "столбец," а "x" и "y" - номера строки и столбца соответственно. Например, ссылка R5C4 в этом формате указывает на то же место, что и ссылка $D$5. Ссылка типа RxCy указывает на ячейку относительно левого верхнего угла листа, то есть есть если вы вставите строку над ячейкой или столбец слева от ячейки, то ссылка на нее изменится.
- Если вы используете в формуле только знак равно и ссылку на единственную ячейку, то вы, фактически, копируете значение из другой ячейки в новую ячейку. Например, ввод "=A2" в ячейку B3 скопирует значение, введенное в ячейку А2, в ячейку В3. Чтобы скопировать значение из ячейки на другом листе, добавьте имя листа, а за ним восклицательный знак (!). Ввод "=Лист1!B6" in Cell F7 на Лист2 отобразит значение ячейки В6 на Лист1 в ячейке F7 на Лист2.
-
Используйте арифметические операторы для базовых операций. Microsoft Excel может выполнить все базовые арифметические операции: сложение, вычитание, умножение и деление, а также возведение в степень. Некоторые операции требуют других символов, чем те, которые мы используем при написании вручную. Список операторов дан ниже, в порядке приоритета (то есть порядок, в котором Excel обрабатывает арифметические операции):
- Отрицание: Знак минус (-). Эта операция возвращает число, противоположное по знаку числу или ссылке на ячейку (это эквивалентно умножению на -1). Этот оператор нужно ставить перед числом.
- Процент: Знак процента (%). Эта операция вернет десятичный эквивалент процента числовой константы.Этот оператор нужно ставить после числа.
- Возведение в степень: Знак вставки (^). Эта операция возводит число (либо значение ссылки), стоящее до знака вставки, в степень, равную числу (либо значению ссылки) после знака вставки. Например, "=3^2" - это 9.
- Умножение: Звездочка (*). Звездочка используется для умножения, чтобы умножение не путали с буквой "x."
- Деление: Косая черта (/). Умножение и деление имеют одинаковый приоритет, они выполняются слева направо.
- Сложение: Знак плюс (+).
- Вычитание: Знак минус (-). У сложения и вычитания одинаковый приоритет, они выполняются слева направо.
-
Используйте операторы сравнения, чтобы сравнить значения в ячейках. Чаще всего, вы буде использовать операторы сравнения с функцией ЕСЛИ. Вы ставите ссылку на ячейку, числовую константу или функцию, которая возвращает числовое значение, по обе стороны оператора сравнения. Операторы сравнения указаны ниже:
- Равно: Знак равно (=).
- Не равно (<>).
- Меньше (<).
- Меньше или равно (<=).
- Больше (>).
- Больше или равно (>=).
-
Используйте амперсанд (&), чтобы соединить текстовые строки. Соединение текстовых строк в одну называется конкатенация, и амперсанд - это оператор, который делает в Excel конкатенацию. Можно использовать амперсанд со строками или ссылками на строки; например, ввод "=A1&B2" в ячейку C3 отобразит "АВТОЗАВОД", если в ячейку A1 введено "АВТО", а в ячейку B2 введено "ЗАВОД".
-
Используйте ссылочные операторы при работе с областью ячеек. Наиболее часто вы будете использовать область ячеек с функциями Excel, такими как СУММ, которая находит сумму значений области ячеек. Excel использует 3 ссылочных оператор:
- Оператор области: двоеточие (:). Оператор области указывает на все ячейки в области, которая начинается с ячейки перед двоеточием и заканчивается ячейкой после двоеточия. Обычно, все ячейки в той же строке или столбце; "=СУММ(B6:B12)" отобразит результат сложения значений ячеек B6, B7, B8, B9, B10, B11, B12, в то время как "=СРЗНАЧ(B6:F6)" отобразит среднее арифметическое значений ячеек с B6 до F6.
- Оператор объединения: запятая (,). Оператор объединения включает все ячейки или области ячеек до и после него; "=СУММ(B6:B12, C6:C12)" суммирует значения ячеек с B6 до B12 и с C6 до C12.
- Оператор пересечения: пробел (). Оператор пересечения ищет ячейки, общие для 2-х или более областей; например, "=B5:D5 C4:C6" это только значение ячейки C5, поскольку она встречается и с первой, и второй области.
-
Используйте скобки, чтобы указать аргументы функций и переопределить порядок вычисления операторов. Скобки в Excel используются в двух случаях: определить аргументы функции и указать иной порядок вычисления.
- Функции - это заранее определенные формулы. Такие, как SIN, COS или TAN, требуют один аргумент, в то время как ЕСЛИ, СУММ или СРЗНАЧ могут принимать много аргументов. Аргументы внутри функции отделяются запятой, например, "=ЕСЛИ (A4 >=0, "ПОЛОЖИТЕЛЬНОЕ," "ОТРИЦАТЕЛЬНОЕ")" для функции ЕСЛИ. Функции могут быть вложены в другие функции, до 64-х уровней.
- В формулах с математическими операциями, операции внутри скобок выполняются раньше, чем вне их; например, в "=A4+B4*C4," B4 умножается на C4 и результат прибавляется к A4, а в "=(A4+B4)*C4," сначала складываются A4 и B4, а затем результат умножается на C4. Скобки в операциях могут быть вложены одна в другую, операция внутри самой внутренней пары скобок будет выполнена первой.
- Не имеет значения встречаются ли вложенные скобки в математических операциях или во вложенных скобках, всегда следите за тем, чтобы количество открывающихся скобок равнялось количеству закрывающихся, иначе получите сообщение об ошибке.
Начинайте любую формулу со знака равно (=). Знак равно говорит Excel, что набор символов, которые вы вводите в ячейку - это математическая формула. Если вы забудете знак равно, то Excel будет трактовать ввод как набор символов.
Используйте координатные ссылки на ячейки, которые содержат значения, используемые в формуле. Хотя вы можете вводить числовые константы в свои формулы, но в большинстве случаев вам нужно будет использовать значения в других ячейках (или результаты других формул, отображаемые в тех ячейках) в формулах. Вы обращаетесь к тем ячейкам, используя координатную ссылку строки и столбца, в которых находится ячейка. Есть несколько форматов:
Если вы читали предыдущие , то должны быть в курсе, что запись формулы начинается со знака «Равно». Когда возникает необходимость написать в ячейке этот знак без формулы, программа настойчиво продолжает считать такую запись началом формулы. При клике в другую ячейку после знака прописывается адрес ячейки. В этом случае есть несколько способов перехитрить Excel.
Пример использования знаков "умножение" и "равно"Решение:
Перед написанием знака равно, плюс (сложение), минус (вычитание), наклонная черта (деление) или звездочки(умножение) поставить пробел или апостроф.
Почему в экселе формула не считает
Если вам приходится работать на разных компьютерах, то возможно придется столкнуться с тем, что необходимые в работе файлы Excel не производят расчет по формулам.
Неверный формат ячеек или неправильные настройки диапазонов ячеек
В Excel возникают различные ошибки с хештегом (#), такие как #ЗНАЧ!, #ССЫЛКА!, #ЧИСЛО!, #Н/Д, #ДЕЛ/0!, #ИМЯ? и #ПУСТО!. Они указывают на то, что что-то в формуле работает неправильно. Причин может быть несколько.
Вместо результата выдается #ЗНАЧ! (в версии 2010) или отображается формула в текстовом формате (в версии 2016).
Примеры ошибок в формулах В данном примере видно, что перемножается содержимое ячеек с разным типом данных =C4*D4.
Исправление ошибки: указание правильного адреса =C4*E4 и копирование формулы на весь диапазон.
- Ошибка #ССЫЛКА! возникает, когда формула ссылается на ячейки, которые были удалены или заменены другими данными.
- Ошибка #ЧИСЛО! возникает тогда, когда формула или функция содержит недопустимое числовое значение.
- Ошибка #Н/Д обычно означает, что формула не находит запрашиваемое значение.
- Ошибка #ДЕЛ/0! возникает, когда число делится на ноль (0).
- Ошибка #ИМЯ? возникает из-за опечатки в имени формулы, то есть формула содержит ссылку на имя, которое не определено в Excel.
- Ошибка #ПУСТО! возникает, если задано пересечение двух областей, которые в действительности не пересекаются или использован неправильный разделитель между ссылками при указании диапазона.
Примечание: #### не указывает на ошибку, связанную с формулой, а означает, что столбец недостаточно широк для отображения содержимого ячеек. Просто перетащите границу столбца, чтобы расширить его, или воспользуйтесь параметром Главная - Формат - Автоподбор ширины столбца .
Ошибки в формулах
Зеленые треугольники в углу ячейки могут указывать на ошибку: числа записаны как текст. Числа, хранящиеся как текст, могут приводить к непредвиденным результатам.
Исправление: Выделите ячейку или диапазон ячеек. Нажмите знак "Ошибка" (смотри рисунок) и выберите нужное действие.
Пример исправления ошибок в Excel Включен режим показа формул
Так как в обычном режиме в ячейках отображаются расчетные значения, то чтобы увидеть непосредственно расчетные формулы в Excel предусмотрен режим отображения всех формул на листе. Включение и отключение данного режима можно вызвать командой Показать формулы из вкладки Формулы в разделе Зависимости формул.
Отключен автоматический расчет по формулам
Такое возможно в файлах с большим объемом вычислений. Для того чтобы слабый компьютер не тормозил, автор файла может отключить автоматический расчет в свойствах файла.
Исправление: после изменения данных нажать кнопку F9 для обновления результатов или включить автоматический расчет. Файл – Параметры – Формулы – Параметры вычислений – Вычисления в книге: автоматически.
Формула сложения в Excel
Выполнить сложение в электронных таблицах достаточно просто. Нужно написать формулу, в которой будут указаны все ячейки, содержащие данные для сложения. Конечно же, между адресами ячеек ставим плюс. Например, =C6+C7+C8+C9+C10+C11 .
Пример вычисления суммы в Excel Но если ячеек слишком много, то лучше воспользоваться встроенной функцией Автосумма . Для этого кликните ячейку, в которой будет выведен результат, а затем нажмите кнопку Автосумма на вкладке Формулы (выделено красной рамкой).
Пример использования функции Автосумма Будет выделен диапазон ячеек для суммирования. Если диапазон выделился неправильно, например, выделены вертикальные ячейки, а необходимы горизонтальные, то выделите новый диапазон. Для этого нажмите левой кнопкой мыши в крайнюю ячейку нового диапазона и не отпуская кнопку проведите указатель по всем ячейкам диапазона до конечной. Завершите ввод формулы нажатием клавиши Enter на клавиатуре.
Формула округления в Excel до целого числа
Начинающие пользователи используют форматирование, с помощью которого некоторые пытаются округлить число. Однако, это никак не влияет на содержимое ячейки, о чем и указывается во всплывающей подсказке. При нажатии на кнопочку (см. рисунок) произойдет изменение формата числа, то есть изменение его видимой части, а содержимое ячейки останется неизменным. Это видно в строке формул.
Уменьшение разрядности не округляет число Для округления числа по математическим правилам необходимо использовать встроенную функцию =ОКРУГЛ(число;число_разрядов).
Математическое округление числа с помощью встроенной функции Написать её можно вручную или воспользоваться мастером функций на вкладке Формулы в группе Математические (смотрите рисунок).
Мастер функций Excel Данная функция может округлять не только дробную часть числа, но и целые числа до нужного разряда. Для этого при записи формулы укажите число разрядов со знаком «минус».
Как считать проценты от числа
Для подсчета процентов в электронной таблице выберите ячейку для ввода расчетной формулы. Поставьте знак «равно», затем напишите адрес ячейки (используйте английскую раскладку), в которой находится число, процент от которого будете вычислять. Можно просто кликнуть мышкой в эту ячейку и адрес вставится автоматически. Далее ставим знак умножения и вводим число процентов, которое необходимо вычислить. Посмотрите на пример вычисления скидки при покупке товара.
Формула =C4*(1-D4)
Вычисление стоимости товара с учетом скидки В C4 записана цена пылесоса, а в D4 – скидка в %. Необходимо вычислить стоимость товара с вычетом скидки, для этого в нашей формуле используется конструкция (1-D4) . Здесь вычисляется значение процента, на которое умножается цена товара. Для Excel запись вида 15% означает число 0.15, поэтому оно вычитается из единицы. В итоге получаем остаточную стоимость товара в 85% от первоначальной.
Вот таким нехитрым способом с помощью электронных таблиц можно быстро вычислить проценты от любого числа.
Шпаргалка с формулами Excel
Шпаргалка выполнена в виде PDF-файла. В нее включены наиболее востребованные формулы из следующих категорий: математические, текстовые, логические, статистические. Чтобы получить шпаргалку, кликните ссылку ниже.
PS: Интересные факты о реальной стоимости популярных товаров
Дорогой читатель! Вы посмотрели статью до конца.
Получили вы ответ на свой вопрос? Напишите в комментариях пару слов.
Если ответа не нашли, укажите что искали .
