Расчетные операции в excel кратко

Обновлено: 05.07.2024

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

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

Для удобства восприятия материала сгруппируем эти функции в три блока:

1. Функционал расчетных формул в Excel.

2. Функционал Excel для обработки табличных данных.

3. Инструменты Excel для моделирования и анализа экономических данных.

ФУНКЦИОНАЛ РАСЧЕТНЫХ ФОРМУЛ В EXCEL

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

Пакет встроенных расчетных формул включает в себя десятки наименований, но самыми востребованными в работе экономистов являются следующие формулы: ЕСЛИ, СУММЕСЛИ и СУММЕСЛИМН, ВПР и ГПР, СУММПРОИЗВ, СЧЕТЕСЛИ.

Решение экономической задачи с помощью формулы ЕСЛИ

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

С помощью формулы ЕСЛИ можно сравнить числовые или текстовые значения по прописанным в формуле условиям.

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

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

значение_если_истина — результат, который появится в расчетной ячейке, если значение будет верным;

значение_если_ложь — результат, который появится в расчетной ячейке при неверном значении.

Задача № 1. Предприятие реализует три номенклатурные группы продукции: лимонад, минеральная вода и пиво. С 01.09.2020 запланировано установить скидку в размере 15 % на пиво.

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

Для решения задачи создаем в таблице третий столбец и прописываем в первой ячейке номенклатуры формулу: =ЕСЛИ(C4="пиво";15%;0).

Эту формулу продлеваем до конца перечня номенклатуры продукции. В итоге получаем сведения о продукции, на которую с сентября снизится цена (табл. 1).

В данном примере показано использование формулы ЕСЛИ для обработки текстовых значений в исходных данных.

Решение экономической задачи с помощью формулы СУММЕСЛИ

Формулы СУММЕСЛИ и СУММЕСЛИМН также используют для экономических расчетов, но они обладают более широкими возможностями для выборки и обработки данных. Можно задать не одно, а несколько условий отборов и диапазонов.

Задача № 2. На основе ведомости начисления заработной платы сотрудникам магазина нужно определить общую сумму зарплаты продавцов.

Чтобы решить эту задачу, сохраняем ведомость из учетной базы данных в виде таблицы Excel. В данном случае нам нужно не просто произвести выборку значений, но и суммировать их результат. Поэтому будем использовать более сложную разновидность формулы ЕСЛИ — СУММЕСЛИ.

Результат решения задачи — в табл. 2.

Решение экономической задачи с помощью формул ВПР и ГПР

Формулы ВПР и ГПР используют для решения более сложных экономических задач. Они популярны среди экономистов, так как существенно облегчают поиск необходимых значений в больших массивах данных. Разница между формулами:

ВПР предназначена для поиска значений в вертикальных списках (по строкам) исходных данных;
ГПР используют для поиска значений в горизонтальных списках (по столбцам) исходных данных.

Формулы прописывают в общем виде следующим образом:

=ВПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер столбца, из которого будут подставлены данные; [интервал просмотра данных]);

=ГПР(искомое значение, которое требуется найти; таблица и диапазон ячеек для выборки данных; номер строки, из которой будут подставлены данные; [интервал просмотра данных]).

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

Задача № 3. У экономиста есть данные в виде таблицы Excel о реализации продукции за сентябрь в натуральном измерении (декалитрах) и данные о реализации продукции в сумме (рублях) в другой таблице Excel. Экономисту нужно предоставить руководству отчет о реализации продукции с тремя параметрами:

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

Для решения этой задачи с помощью формулы ВПР нужно последовательно выполнить следующие действия.

Шаг 1. Добавляем к таблице с данными о продажах в натуральном измерении два новых столбца. Первый — для показателя продаж в рублях, второй — для показателя цены реализации единицы продукции.

Шаг 2. В первой ячейке столбца с данными о продажах в рублях прописываем расчетную формулу: =ВПР(B4:B13;Табл.4!B4:D13;3;ЛОЖЬ).

Пояснения к формуле:

В4:В13 — диапазон поиска значений по номенклатуре продукции в создаваемом отчете;

Табл.4!B4:D13 — диапазон ячеек, где будет производиться поиск, с наименованием таблицы, в которой будет организован поиск;

3 — номер столбца, по которому нужно выбрать данные;

ЛОЖЬ — значение критерия поиска, которое означает необходимость строгого соответствия отбора наименований номенклатуры таблицы с суммовыми данными наименованиям номенклатуры в таблице с натуральными показателями.

Шаг 3. Продлеваем формулу первой ячейки до конца списка номенклатуры в создаваемом нами отчете.

Шаг 4. В первой ячейке столбца с данными о цене реализации единицы продукции прописываем простую формулу деления значения ячейки столбца с суммой продаж на значение ячейки столбца с объемом продаж (=E4/D4).

Шаг 5. Продлим формулу с расчетом цены реализации до конца списка номенклатуры в создаваемом нами отчете.

В результате выполненных действий появился искомый отчет о продажах (табл. 3).

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

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

Решение экономической задачи с помощью формулы СУММПРОИЗВ

Задача № 4. Есть реестр продаж различной номенклатуры продукции за сентябрь 2020 г. Нужно рассчитать из общего реестра данные о суммах реализации по основным номенклатурным группам продукции.

Выполнив указанные действия, получим искомое решение задачи в табл. 4.

Решение экономической задачи с помощью формулы СЧЕТЕСЛИ

Формула СЧЕТЕСЛИ используется не так широко, как предыдущие, но она выручает экономистов, если нужно минимизировать ошибки при работе с таблицами Excel. Эта формула удобна для проверки корректности вводимых данных и установке различного рода запретов, что особенно важно, если с данными работает несколько пользователей.

Задача № 5. Экономисту поручили провести корректировку справочника номенклатуры ТМЦ в учетной базе данных компании. Справочник долгое время не проверяли, данные в него вносили порядка 10 человек, поэтому появилось много некорректных и дублирующих наименований.

Экономист может решить эту проблему с помощью формулы СЧЕТЕСЛИ. Нужно выполнить следующие действия:

Такими экономическими задачами могут быть:

расчет оптимального объема выпуска продукции при ограниченности сырья;
минимизация транспортных расходов на доставку продукции покупателям;
решение по оптимизации фонда оплаты труда.

Задача № 9. Туристической компании необходимо организовать доставку 45 туристов в четыре гостиницы города с трех пунктов прибытия при минимально возможной сумме затрат. Для решения задачи составляем таблицу с исходными данными:

1. Количество прибывающих с каждого пункта — железнодорожный вокзал, аэропорт и автовокзал (ячейки Н6:Н8).

2. Количество забронированных для туристов мест в каждой из четырех гостиниц (ячейки D9:G9).

3. Стоимость доставки одного туриста с каждого пункта прибытия до каждой гостиницы размещения (диапазон ячеек D6:G8).

Исходные данные, размещенные таким образом, показаны в табл. 8.1.

Далее приступаем к подготовке поиска решения.

1. Создаем внизу исходной таблицы такую же таблицу для расчета оптимального количества доставки туристов при условии минимизации затрат на доставку с диапазоном ячеек D15:G17.

2. Выбираем на листе ячейку для расчета искомой функции минимизации затрат (J4) и прописываем в ячейке расчетную формулу: =СУММПРОИЗВ(D6:G8;D15:G17).

оптимизировать целевую функцию — ячейка J4;
цель оптимизации — до минимума;
изменения ячейки переменных — диапазон ячеек второй таблицы D15:G17;
ограничения поиска решения:

– в диапазоне ячеек второй таблицы D15:G17 должны быть только целые значения (D15:G17=целое);

– значения диапазона ячеек второй таблицы D15:G17 должны быть только положительными (D15:G17>=0);

– количество мест для туристов в каждой гостинице таблицы для поиска решения должно быть равно количеству мест в исходной таблице (D18:G18 = D9:G9);

– количество туристов, прибывающих с каждого пункта, в таблице для поиска решения должно быть равно количеству туристов в исходной таблице (Н15:Н17 = Н6:Н8).

Далее даем команду найти решение, и надстройка рассчитывает нам результат оптимальной доставки туристов (табл. 8.2).

При такой схеме доставки целевое значение общей суммы расходов действительно минимальное и составляет 1750 руб.

Пакет расширенного анализа данных

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

Пакет является надстройкой к Excel, устанавливается в основное меню аналогично функции поиска решений (Файл → Параметры → Надстройки → Пакет анализа). Вызвать его можно командой Данные → Анализ данных через диалоговое окно, в котором отражены все заложенные в надстройке виды анализа (рис. 3).

Специализированные надстройки для финансово-экономической работы

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

Не будем останавливаться на таких надстройках, как Power Query, Power Pivot, Power Quick, так как они в большей степени используются в целях бизнес-аналитики, чем для решения экономических задач.

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

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

Ранее мы уже описали, как формулы используются для расчетов в Excel по формулам. Заранее определенные формулы, которые выполняют вычисления по заданным величинам (аргументам) называются функциями. Эти функции позволяют выполнять простые и сложные вычисления. Функция имеет имя (например, SIN) и, как правило, аргументы, которые записываются в круглых скобках следом за именем функции. Скобки - обязательная принадлежность функции, даже если у нее нет аргументов. Если аргументов несколько, один аргумент отделяется от другого точкой с запятой. В качестве аргументов функции могут использоваться числа, адреса ячеек, диапазоны ячеек, арифметические выражения и функции. Смысл и порядок следования аргументов однозначно определены описанием функции, составленным ее автором. Например, если в ячейке F3 записана формула с функцией возведения в степень =СТЕПЕНЬ(ВЗ;2,3), значением этой ячейки будет значение ячейки ВЗ, возведенное в степень 2,3.

Работая с функциями, помните:

1. функция, записанная в формуле, как правило, возвращает уникальное значение (арифметическое или логическое);

2. существуют функции, которые не возвращают значение, а выполняют некоторые операции (например, объединяют текстовые строки);

3. существуют функции без аргументов (например, функция ПИ() возвращает число к = 3,1416. ).

Ниже будут рассмотрены функции И (AND) и ИЛИ (OR), которые принимают логические значения (True или False).

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

Логические функции

При решении некоторых задач значение ячейки необходимо вычислять одним из нескольких способов, в зависимости от выполнения или невыполнения одного или нескольких условий. Для решения таких задач применяют условную функцию ЕСЛИ. Она имеет формат:

Формат функций одинаков:

Лабораторная работа 1. Использование математических функций

Задание. Создать таблицу с использованием математических функций, которая рассчитывает значения функции y=sin(2x/ 3)*cos(x/2) на интервале значений х от -p до +2p с шагом 0,1, вычисляет максимальное и минимальное значения функции на данном интервале области определения, а также строит график данной функции.

Рис. 1. Таблица расчета значений функции y=sin(2x/3)*cos(x/2)

После просмотра справки закройте окно справки любым из стандартных способов.

В ячейку А1 введите заголовок таблицы «Таблица значений функции y=sin(2x/3)*cos(x/2). Задайте в ячейке А2 формулу = -ПИ() для ввода начального значения х.

В ячейке A3 задайте формулу = А2+0,1 для вычисления следующего значения х, изменяющегося с шагом 0,1. Скопируйте формулу из A3 в диапазон (А4:А97).

В ячейку В2 введите формулу расчета значения функции =SIN(2*A3/3)*COS(A3/2). Затем скопируйте формулу из ячейкиВ2 в диапазон (ВЗ:В97).
В ячейку С2 введите формулу определения минимума функции =МИН(В2:В97), а в ячейку СЗ - формулу определения максимума функции =МАКС(В2:В97).

В результате получится таблица, фрагмент которой показан на рис. 1.

Лабораторная работа 2. Использование логических функций

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

Пусть налог исчисляется по прогрессивной шкале следующим образом: с зарплаты, не превышающей 1000 руб., налог составляет 12%, а с части зарплаты, превышающей 1000 руб., взыскивается налог 20% от этой части.

1. Вначале определим исходные данные задачи: фамилии работников (текст) и размер зарплаты (число с двумя цифрами в дробной части).

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

2. Для выполнения расчетов запустите Excel, опишите структуру таблицы и введите исходные данные следующим образом.

А	В	С	D
Расчет зарплаты с прогрессивной шкалой подоходного налога
Фамилия	Зарплата	Налог	Получить
Иванов	1234,56
Петров
Сидоров	1563,35
Фролов	986,54
Итого	4784,45

3. В ячейку СЗ поместите формулу расчета величины подоходного налога, вычисляемого по прогрессивной шкале. Для этого выделите ячейку СЗ и введите формулу =ЕСЛИ(ВЗ ; ; ).

Формат функций одинаков:

Лабораторная работа 1. Использование математических функций

Рис. 1. Таблица расчета значений функции y=sin(2x/3)*cos(x/2)

После просмотра справки закройте окно справки любым из стандартных способов.

В ячейку А1 введите заголовок таблицы «Таблица значений функции y=sin(2x/3)*cos(x/2). Задайте в ячейке А2 формулу = -ПИ() для ввода начального значения х.

В ячейку В2 введите формулу расчета значения функции =SIN(2*A3/3)*COS(A3/2). Затем скопируйте формулу из ячейкиВ2 в диапазон (ВЗ:В97).
В ячейку С2 введите формулу определения минимума функции =МИН(В2:В97), а в ячейку СЗ - формулу определения максимума функции =МАКС(В2:В97).

В результате получится таблица, фрагмент которой показан на рис. 1.

Лабораторная работа 2. Использование логических функций

А	В	С	D
Расчет зарплаты с прогрессивной шкалой подоходного налога
Фамилия	Зарплата	Налог	Получить
Иванов	1234,56
Петров
Сидоров	1563,35
Фролов	986,54
Итого	4784,45

3. В ячейку СЗ поместите формулу расчета величины подоходного налога, вычисляемого по прогрессивной шкале. Для этого выделите ячейку СЗ и введите формулу =ЕСЛИ(ВЗ

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

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

Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.

Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:

Ниже приведены примеры формул, которые можно использовать на листах.

=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.

=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.

=СЕГОДНЯ() Возвращает текущую дату.

=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.

=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.

Элементы формулы

Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.

1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.

2. Ссылки. A2 возвращает значение ячейки A2.

3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.

4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.

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

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

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

Типы операторов

Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.

Арифметические операторы

Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.

Арифметический оператор

Возведение в степень

Операторы сравнения

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

Оператор сравнения

Больше или равно

Текстовый оператор конкатенации

Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.

Текстовый оператор

Соединение или объединение последовательностей знаков в одну последовательность

Операторы ссылок

Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.

Оператор ссылки

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

; (точка с запятой)

Оператор объединения. Объединяет несколько ссылок в одну ссылку.

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

Порядок выполнения Excel в Интернете формулах

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

Порядок вычислений

Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.

Приоритет операторов

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

Возведение в степень

Умножение и деление

Сложение и вычитание

Объединение двух текстовых строк в одну

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

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

Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.

В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.

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

Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.

Синтаксис функций

Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.

1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.

2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.

4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.

Ввод функций

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

Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">

Вложенные функции

В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.

1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Предельное количество уровней вложенности функций . В формулах можно использовать до семи уровней вложенных функций. Если функция Б является аргументом функции А, функция Б находится на втором уровне вложенности. Например, в приведенном выше примере функции СРЗНАЧ и СУММ являются функциями второго уровня, поскольку обе они являются аргументами функции ЕСЛИ. Функция, вложенная в качестве аргумента в функцию СРЗНАЧ, будет функцией третьего уровня, и т. д.

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

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

Стиль ссылок A1

Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.

Ячейка или диапазон

Использование

Ячейка на пересечении столбца A и строки 10

Диапазон ячеек: столбец А, строки 10-20.

Диапазон ячеек: строка 15, столбцы B-E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек: столбцы А-E, строки 10-20

2. Ссылка на диапазон ячеек с B1 по B10 включительно.

3. Ссылка на лист, отделенная от ссылки на диапазон значений.

Различия между абсолютными, относительными и смешанными ссылками

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

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

Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.

Стиль трехмерных ссылок

Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.

При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.

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

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

Что происходит при перемещении, копировании, вставке или удалении листов . Нижеследующие примеры поясняют, какие изменения происходят в трехмерных ссылках при перемещении, копировании, вставке и удалении листов, на которые такие ссылки указывают. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для суммирования значений в ячейках с A2 по A5 на листах со второго по шестой.

Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.

Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.

Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.

Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.

Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.

Стиль ссылок R1C1

Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.

относительная ссылка на ячейку, расположенную на две строки выше в том же столбце

Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее

Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца

Относительная ссылка на строку, расположенную выше текущей ячейки

Абсолютная ссылка на текущую строку

При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку "Автоумма"), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.

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

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

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

Поиск перечня доступных функций в Excel

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

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

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

Вставка функции в таблицу

Использование математических операций в Excel

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

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

Растягивание функций и обозначение константы

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

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

В итоге вы должны увидеть, что функция растянулась на выбранный диапазон, а значения в ней подставлены автоматически. Так, изначальная функция имела вид =B2*C2, но после растягивания вниз последующие значения подставились автоматически (от B3*C3 до B13*C13, что видно на следующем изображении). Точно так же растягивание работает с СУММ и другими простыми формулами, где используется несколько аргументов.

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

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

В закрепление темы рассмотрим три константы, которые можно обозначить при записи функции:

$В$2 – при растяжении либо копировании остаются постоянными столбец и строка.

B$2 – неизменна строка.

$B2 – константа касается только столбца.

Построение графиков функций

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

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

Читайте также: