Расчетные операции в MS Excel

Формула в MS Excel

В Excel используется 4 вида операторов:

• Арифметические операторы (+, –, *, /, %, ^) используют для выполнения основных математических вычислений над числами. Результатом вычисления формул, которые содержат арифметические операторы, всегда является число.
• Операторы сравнения ($= , >, =, $) используют для сравнения двух чисел. Результатом вычисления формул, которые содержат операторы сравнения, являются логические значения ИСТИНА или ЛОЖЬ.
• Текстовый оператор (&) используют для объединения последовательностей символов в одну последовательность.
• Адресные операторы используют для объединения диапазонов ячеек с целью осуществления вычислений. К ним относятся:
  • : – Оператор диапазона, который используется для обозначения диапазона ячеек, к которым применяется формула. Например, А3:В6.
  • , – Оператор объединения, который используется для объединения ячеек диапазона. Например, МИН(В1:В10,С1:С15) ищет минимальное значение в диапазонах В1:В10 и С1:С15.

Пробел – оператор пересечения, который используется для ссылки на общие ячейки диапазона. Например, в формуле МАКС(А1:В2 А1:А4) ячейки А1 и А2 – общие для обоих диапазонов. Таким образом, результатом вычисления формулы будет нахождение максимального значения из этих двух ячеек.

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

1. адресные операторы «:», «,», « »;
2. знаковый минус «–»;
3. вычисление процента %;
4. арифметические операторы ^, *, /, +, –;
5. текстовый оператор &;
6. операторы сравнения =, , =, .

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

Можно включить режим отображения формул с помощью команды Показать формулы в группе Зависимости формул на вкладке Формулы.

Использование ссылок в формулах

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

Excel использует 3 типа ссылок:

• Относительные ссылки указывают на ячейку относительно той ячейки, в которой находится формула. При копировании или перемещении формулы относительные ссылки изменяются. Например, если в ячейку А3 записано =А1+А2, то при копировании этой формулы в ячейку А4 формула изменится на =А2+А3, а при копировании в В3 запишется =В1+В2.

Рисунок 1. Режим отображения формул

Рисунок 2. Режим вычислений

• Абсолютными являются ссылки на ячейки, которые фиксируются при копировании или перемещении. В абсолютных ссылках используется знак $ \$\ $ перед именами столбцов и именами строк. Например, в ячейке А3 записано: $= \$\ A\$\ 1+ \$\ А\$\ 2$. Тогда при копировании в любую ячейку рабочего листа формула останется неизменной: = \$\ A\$\ 1+ \$\ А \$\ 2$.

• Смешанные ссылки – это ссылки, которые являются комбинацией относительных и абсолютных ссылок. Например, $А3. В этом случае при копировании или перемещении такой ссылки будет изменяться только номер строки, а имя столбца изменяться не будет.А3. В этом случае при копировании или перемещении такой ссылки будет изменяться только номер строки, а имя столбца изменяться не будет.

Можно также использовать следующие примеры ссылок:

В:В – ссылка на все ячейки столбца В; 3:5 – ссылка на все ячейки строк 3, 4, 5.

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

имя раб.листа! ссылка на ячейку Пример: Лист3!В3:В13.

Если в имени рабочего листа содержатся пробелы, то его нужно заключить в одинарные кавычки (например, ‘общие значения’!В3:В13.

В формулах можно использовать объемные ссылки (ссылки на диапазон ячеек нескольких рабочих листов). Например, Лист2:Лист6!$В$3:$С$5.

Формат ссылок на ячейки других книг:

[имя книги]имя раб.листа! ссылка на ячейку Пример: [книга4]Лист5!В3:В13.

Имя ячейки

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

Для присвоения имени ячейке или диапазону необходимо:

1. Выделить нужную ячейку или диапазон ячеек и выбрать команду Присвоить имя в группе Присвоить имя на вкладке Формулы.
2. В открывшемся окне Создание имени ввести имя ячейки или диапазона ячеек. В именах вместо пробела нужно использовать знак нижнего подчеркивания.

Основные функции Excel

Общий вид любой функции: =(аргументы)

Правила ввода функций:

1. Имя функции всегда следует после знака «=».
2. Аргументы помещаются в круглые скобки, которые указывают на начало и конец списка аргументов.
3. Между именем функции и аргументами в скобках не ставится пробел.

Excel содержит широкий набор функций, которые объединены в категории:

• Математические – используются для простых и сложных математических вычислений (вычисление суммы, максимального и минимального значения, округление чисел и т.д.).
• Инженерные – используются для выполнения инженерного анализа (работа с комплексными переменными, преобразование чисел из одной системы счисления в другую и т.п.).
• Логические – используются для проверки выполнения условия (функции ЕСЛИ, И, ИЛИ, НЕ, ИСТИНА, ЛОЖЬ).
• Статистические, которые предназначаются для выполнения статистического анализа данных.
• Финансовые – для осуществления типичных финансовых расчетов.
• Текстовые – для обработки текста (преобразование, сравнение, объединение строк текста и т.д.).
• Дата и время.
• Ссылки и массивы – для поиска в списках или таблицах.
• Проверка свойств и значений – для перехвата ошибок и значений #Н/Д и предотвращения их распространения по рабочему листу.
• Аналитические – для работы с аналитическими кубами, позволяющими создавать отчеты по базе данных оперативной аналитической обработки (OLAP).
• Работа с базой данных – для анализа данных из списков или баз данных.
• Определенные пользователем – для расширения возможностей MS Excel за счет добавления пользовательских команд и новых функций.

Для ввода функций можно использовать несколько возможностей, которые запускают Мастер функций:

1. Команда Вставить функцию в группе Библиотека функций на вкладке Формулы.
2. Список команды Сумма в группе Редактирование на вкладке Главная.
3. Кнопка Вставить функцию в начале Строки формул.