Формулы в ячейках в excel: Функции Excel (по категориям) — Служба поддержки Майкрософт

Как зафиксировать ссылку в Excel? | Что важно знать о

Очень важно знать для быстрого расчета прогноза в MS Excel — Как в формуле зафиксировать ссылку на ячейку или диапазон?

Это необходимо для того, чтобы, когда вы протягивали формулу, ссылка на ячейку не смещалась. Например, для расчета коэффициента сезонности января (см. вложение) мы средние продажи за январь (пункт 2 см. вложение) делим на среднегодовые продажи за 3 года (пункт 3 см. вложение). Если мы просто протянем ячейку вниз, чтобы рассчитать коэффициенты для других месяцев, то для февраля мы получим, что среднегодовые продажи за февраль разделятся на ноль, а не на среднегодовые продажи за 3 года.

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

Для этого в строке формул выделяете ссылку, которую хотите зафиксировать:

и нажимаете клавишу «F4». Ссылка станет со значками $, как на рисунке:

это означает, что если вы протяните формулу, то ссылка на ячейку $F$4 останется на месте, т.е. зафиксирована строка ‘4’ и столбец ‘F’. Если вы еще раз нажмёте клавишу F4, то ссылка станет F$4 — это означает, что зафиксирована строка 4, а столбец F будет перемещаться.

Если еще раз нажмете клавишу «F4», то ссылка станет $F4:

Это означает, что зафиксирован столбец F и он не будет перемещаться, когда вы будите протаскивать формулу, а ссылка на строку 4 будет двигаться.

Если ссылки имеют вид R1C1, то полностью зафиксированная ячейка будет иметь вид R4C6:

Если зафиксирована только строка (R), то ссылка будет R4C[-1]

Если зафиксирован только столбец (С), то ссылка будет иметь вид RC6

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

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

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

• Novo Forecast Lite — автоматический расчет прогноза в Excel.
• 4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
• Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

• Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

Добавить комментарий

Как показать формулы в ячейках или полностью скрыть их в Excel 2013

Перейти к содержанию

Search for:

Главная » Уроки MS Excel » Интерфейс и настройка

Автор Антон Андронов На чтение 3 мин Опубликовано 16. 12.2019

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

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

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

Вы также можете выбрать команду Show Formulas (Показать формулы) на вкладке Formulas (Формулы) в группе Formula Auditing (Зависимости формул), чтобы показать формулы в Excel.

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

1. Чтобы сделать это, выделите ячейки с формулами, которые требуется скрыть.
2. На вкладке Home (Главная) в группе команд Cells (Ячейки) нажмите Format (Формат) и из выпадающего меню выберите пункт Format Cells (Формат ячеек).
3. Появится диалоговое окно Format Cells (Формат ячеек). Установите флажок Hidden (Скрыть формулы) на вкладке Protection (Защита). Затем нажмите OK.
4. Чтобы окончательно скрыть ячейки, Вы должны защитить лист. Для этого снова нажмите Format (Формат) на вкладке Home (Главная) и из выпадающего меню выберите пункт Protect Sheet (Защитить лист).
5. Откроется диалоговое окно Protect Sheet (Защита листа). Убедитесь, что установлен флажок у пункта Protect worksheet and contents of locked cells (Защитить лист и содержимое защищаемых ячеек). Введите пароль для защиты листа, который в будущем позволит снять защиту и снова увидеть формулы. В разделе Allow all users of this worksheet to (Разрешить всем пользователям этого листа) установите флажки напротив тех задач, которые хотите разрешить пользователям. Нажмите OK.
6. Для продолжения введите свой пароль еще раз в соответствующем поле диалогового окна Confirm Password (Подтверждение пароля).
7. Можете заметить, что теперь при выборе ячейки, содержащей формулу, Строка формул остается пустой.
8. Чтобы вновь показать формулы, нажмите Format (Формат) и выберите пункт Unprotect Sheet (Снять защиту листа).
9. Введите пароль в диалоговом окне Unprotect Sheet (Снять защиту листа) и нажмите OK.

Формулы вновь будут видны при выделении ячеек.

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: http://www.howtogeek.com/175759/how-to-show-formulas-in-cells-and-hide-formulas-completely-in-excel-2013/
Перевел: Антон Андронов
Правила перепечатки
Еще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Adblock
detector

Excel 2016: Введение в формулы

Урок 13: Введение в формулы

/en/excel2016/page-layout-and-printing/content/

Введение

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

Необязательно: Загрузите нашу рабочую тетрадь.

Посмотрите видео ниже, чтобы узнать больше о создании формул в Excel.

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

Excel использует стандартные операторы для формул, такие как знак плюс для сложения ( + ), знак минус для вычитания ( — для мультириска ), aste 0 * ), косая черта для деления ( / ) и ) для показателей.

Все формулы в Excel должны начинаться со знака равенства ( = ). Это связано с тем, что ячейка содержит или равна формуле и вычисляемому ею значению.

Понимание ссылок на ячейки

Хотя вы можете создавать простые формулы в Excel, используя числа (например, =2+2 или =5*5 ), большую часть времени вы будете использовать адресов ячеек для создания формула. Это называется созданием ссылки на ячейку 9.0010 . Использование ссылок на ячейки гарантирует, что ваши формулы всегда будут точными, поскольку вы можете изменить значение ячеек, на которые ссылаются, без необходимости переписывать формулу.

В приведенной ниже формуле ячейка A3 суммирует значения ячеек A1 и A2, создавая ссылки на ячейки:

Когда вы нажимаете Enter, формула вычисляет и отображает ответ в ячейке A3:

Если значения в указанных ячейках изменяются , формула автоматически пересчитывает:

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

Чтобы создать формулу:

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку D12 .
2. Введите знак равенства (=) . Обратите внимание, как он отображается в ячейке и в формуле bar .
3. Введите ячейку адрес ячейки, на которую вы хотите сослаться первой в формуле: ячейка D10 в нашем примере. Вокруг указанной ячейки появится синяя рамка .
4. Введите математический оператор , который вы хотите использовать. В нашем примере мы напечатаем знак добавления ( + ).
5. Введите адрес ячейки ячейки, на которую вы хотите сослаться второй в формуле: ячейка D11 в нашем примере. Вокруг указанной ячейки появится красная рамка .
6. Нажмите Введите на клавиатуре. Формула будет вычислено , и в ячейке отобразится значение . Если вы выберете ячейку еще раз, обратите внимание, что в ячейке отображается результат, а в строке формул отображается формула.

Если результат формулы слишком велик для отображения в ячейке, он может отображаться в виде знаков фунта стерлингов (#######) вместо значения. Это означает, что ширина столбца недостаточно широка для отображения содержимого ячейки. Просто увеличьте ширину столбца , чтобы показать содержимое ячейки.

Изменение значений с помощью ссылок на ячейки

Истинное преимущество ссылок на ячейки заключается в том, что они позволяют вам обновлять данные на листе без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки D10 с 1200 до 1800 долларов. Формула в D12 автоматически пересчитает и отобразит новое значение в ячейке D12.

Excel не всегда будет сообщать вам , если ваша формула содержит ошибку, поэтому вы должны проверить все свои формулы. Чтобы узнать, как это сделать, вы можете прочитать урок «Перепроверьте свои формулы» из нашего учебника по формулам Excel.

Чтобы создать формулу методом «укажи и щелкни»:

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

1. Выберите ячейку , которая будет содержать формулу. В нашем примере мы выберем ячейку Д4 .
2. Введите знак равенства (=) .
3. Выберите ячейку , на которую вы хотите сослаться первой в формуле: ячейка B4 в нашем примере. В формуле появится адрес ячейки .
4. Введите математический оператор , который вы хотите использовать. В нашем примере мы введем знак умножения (*) .
5. Выберите ячейку , которую вы хотите указать второй в формуле: ячейка C4 в нашем примере. В формуле появится адрес ячейки .
6. Нажмите Введите на клавиатуре. Формула будет вычислено , а значение будет отображаться в ячейке.

Копирование формул с помощью маркера заполнения

Формулы также можно копировать в соседние ячейки с помощью маркера заполнения , что может сэкономить много времени и усилий, если вам нужно выполнить тот же расчет несколько раз на листе. Маркер заполнения — это маленький квадрат в правом нижнем углу выбранной ячейки (ячеек).

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

Чтобы отредактировать формулу:

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

1. Выберите ячейку , содержащую формулу, которую вы хотите изменить. В нашем примере мы выберем ячейку D12 .
2. Щелкните строку формул , чтобы изменить формулу. Вы также можете дважды щелкнуть ячейку, чтобы просмотреть и изменить формулу непосредственно в ячейке.
3. Рамка появится вокруг любых ячеек, на которые есть ссылки. В нашем примере мы изменим первую часть формулы, указав ссылку на ячейку D10 вместо ячейки 9.0009 Д9 .
4. Когда вы закончите, нажмите Введите на клавиатуре или выберите команду Введите в строке формул.
5. Формула будет обновлена ​​ , а в ячейке будет отображаться новое значение .

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

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

Вызов!

1. Откройте нашу рабочую тетрадь.
2. Щелкните вкладку Challenge в левом нижнем углу рабочей книги.
3. Создайте формулу в ячейке D4 , которая умножает количество в B4 на цену за единицу в ячейке С4 .
4. Используйте маркер заполнения , чтобы скопировать формулу из ячейки D4 в ячейки D5:D7 .
5. Измените цену за единицу жареных бананов в ячейке C6 на 2,25 доллара США. Обратите внимание, что итоговая сумма строки также автоматически изменяется.
6. Измените формулу для суммы в ячейке D8 , чтобы она также добавила ячейку D7 .
7. Когда вы закончите, ваша книга должна выглядеть так:

Продолжать

Предыдущий: Макет страницы и печать

Далее:Создание более сложных формул

/en/excel2016/creating-more-complex-formulas/content/

Как создать формулу в Excel: учебник для начинающих (2023)

Перейти к содержимому Как создать формулу в Excel: учебник для начинающих (2023)

Excel is все о текущих расчетах! Таким образом, создать формулу и работать с ней в Excel очень просто.

Формула Excel представляет собой комбинацию операторы и операнды . Например, 2 + 2 = 4 — это формула, где 2 — это операнды, плюс (+) — это оператор, а 4 — это ответ на формулу.

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

Итак, давайте приступим прямо к делу.

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

Содержание

Как создавать формулы в Excel

Создавать формулы Excel очень просто.

Например, сколько будет 10 разделить на 2? Вы можете посчитать это в Excel?

1. Начните с активации ячейки.

2. Поставьте знак равенства.

Очень важно начинать любую формулу со знака равенства. Если вы не начнете со знака равенства, Excel распознает его не как формулу, а как текстовую строку.

Каспер Лангманн , специалист Microsoft Office

3. Введите простую математическую операцию 10 разделить на 2.

= 10 / 2

Вы можете создать аналогичную выше формулу с небольшими изменениями.

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

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

= А2/В2

Вышеприведенная формула переводится как «A2 разделить на A3».

Где A2 имеет числовое значение 10, а A3 имеет числовое значение 2.

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

Создание формулы с использованием ссылок на ячейки и значений

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

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

= A2 / 2

Приведенная выше формула переводится как «A2 разделить на 2».

Где A2 имеет числовое значение 10, а 2 — это значение.

1. Результаты выглядят следующим образом.

Профессиональный совет!

Использование ссылок на ячейки лучше, чем использование абсолютных значений. Это связано с тем, что если через какое-то время вы измените значение ячейки, формула автоматически обновится.

Как складывать, вычитать, умножать и делить?

Существует четыре основных математических операции: сложение, вычитание, умножение и деление.

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

Как составить формулу СУММ (сложение)

Сложение в Excel может принимать различные формы.

Excel имеет встроенную функцию для выполнения сложения, т. е. функцию СУММ. Вот как вы можете привести его в действие.

1. Напишите функцию СУММ, начинающуюся со знака равенства, следующим образом.

= СУММ (5, 5)

Каждый аргумент функции СУММ, разделенный запятой, представляет собой добавляемое значение.

Excel добавляет 5 к 5, чтобы получить результаты ниже.

Достаточно просто? Вы также можете использовать функцию СУММ для ссылок на ячейки. 🤩

2. Запишите функцию СУММ следующим образом.

= СУММ (A2, A3)

Профессиональный совет!

Чтобы вставить СУММ с помощью кнопки «Вставить функцию», выберите этот маршрут.

Перейти на вкладку Formulas > Библиотека функций > Вставить функциональную кнопку > Введите имя функции .

В диалоговом окне «Вставить функцию» введите SUM и нажмите «Поиск». Выберите нужную функцию и нажмите «ОК», чтобы вставить ее.

Excel складывает значения ячеек A2 и A3.

Что делает функцию СУММ большим плюсом, так это ее способность суммировать диапазон ячеек.

Например, см. данные ниже.

3. Чтобы сложить это в Excel с помощью функции СУММ, напишите функцию СУММ, как показано ниже.

= СУММ (A2:A10)

Обратите внимание, как мы определили диапазон ячеек от ячейки A2 до ячейки A10 как A2:A10 .

4. Excel суммирует все значения ячеек в ячейках от A2 до A10.

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

5. Напишите функцию СУММ с несколькими диапазонами следующим образом.

= СУММ (A2:A5, B2:B8, C1:C10)

Вот результаты.

Как вычитать в Excel

Вычитание в Excel заключается в создании формулы с оператором минус (-).

Например:

1. Чтобы вычесть 5 из 10, начните со знака равенства и напишите следующую формулу.

= 10 – 5

Простая формула вычитания со знаком минус!

Нажмите Enter и вот.

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

= A2 – A3

Эта формула переводится как A2 минус A3. Где A2 имеет числовое значение 10, а A3 имеет числовое значение 5.

3. Кроме того, вы можете использовать функцию СУММ для выполнения вычитания. Однако для этого нужно добавить знак минус к вычитаемому значению.

= СУММ (A2, -A3)

Вот результаты.

Как умножать в Excel

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

Во-первых, оператор умножения в Excel — это звездочка (*).

Теперь вы помните, сколько будет 9 умножить на 8? Нет?

1. Напишите формулу умножения в Excel.

= 9 * 8

Эксель, кратный 9 и 8.

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

= A2 * A3

Приведенная выше формула переводится как A2, умноженный на A3.

Excel также предлагает встроенную функцию умножения в Excel. Функция ПРОДУКТА!

3. Напишите функцию ПРОИЗВЕД следующим образом.

= ПРОИЗВЕД (9, 8)

Мы добавили оба значения для умножения в качестве аргументов функции ПРОИЗВЕД.

Вот результаты.

Функция ПРОИЗВЕД также может найти произведение нескольких значений (или диапазона ячеек) одновременно.

Например, см. данные ниже.

4. Чтобы умножить все эти значения, напишите функцию ПРОИЗВЕД следующим образом:

= ПРОИЗВЕД (A2:A10)

Excel умножает все значения в указанном диапазоне.

Как делить в Excel

А вот и последняя операция этого руководства — деление. 🤞

Создать формулу деления в Excel тоже очень просто.

Что такое оператор деления? Косая черта (/).

Кроме того, делимое (или числитель) стоит перед косой чертой. А делитель (или знаменатель) стоит после косой черты.

Каспер Лангманн , специалист Microsoft Office

1. Напишите формулу деления, как показано ниже.

= 30 / 10

Excel разделяет оба операнда, чтобы получить следующие результаты.

2. То же самое можно сделать, используя ссылки на ячейки.

= A2 / A3

Профессиональный совет!

При выполнении функции деления в Excel вы можете увидеть #DIV/0! Ошибка. Эта ошибка возвращается Excel, когда вы пытаетесь разделить число ноль.

Основное правило 6 класса! Ни одно число не делится на ноль. Excel помнит это, если не мы. 😆

Порядок действий

Вот уравнение, которое вам нужно решить.

= 2+ 4 * 6 / 3 – 2

Какой бардак! Какую операцию вы выполняете первой?

Чтобы решить эту загадку, существует приказ о выполнении математических операций — Pemdas

P = Клаженка

E = Экспоненты

MD = умножение и дивизион (слева направо) MD = Размен и разделение (слева направо) AS = сложение и вычитание

Решите приведенное выше уравнение в том же порядке, и вы получите ответ 8.

Пусть Excel сделает то же самое, чтобы увидеть результаты.

Excel сначала выполняет деление (6 / 3 = 2), затем умножение (4 * 2 = 8), сложение в конце (2 + 8 = 10) и вычитание в последнюю очередь (10 – 2 = 8), в результате чего получается 8.

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

= 2+ 4 * 6 / (3 – 2)

Что приводит к изменению результатов при добавлении только круглых скобок?

Теперь Excel сначала выполняет операцию, заключенную в круглые скобки, т. е. (3-2).

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

Профессиональный совет!

Попробуйте посчитать в уме, чтобы еще раз проверить, правильно ли Excel вычислил 26.

Первая скобка = 2+ 4*6/ (3 – 2)

Умножение Вторая = 2+ 4*6/1

Деление Третье = 2 + 24/1

Сложение Последнее =

4 +

24

Вот ответ = 26

Как создавать формулы со ссылками

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

Например, давайте создадим формулу умножения в Excel.

Отлично! Что, если бы у вас были 2 и 4 в качестве числовых значений в ячейках?

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

Вы можете сделать то же самое для всех операторов! Это просто.

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

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

Формулы или функции?

Что такое формула Excel и что такое функция Excel? И чем эти два отличаются?

Есть два способа добавить 2 и 2 в Excel.

1. = 2 + 2
2. СУММ (2,2)

Ответ на них обоих будет одинаковым. Однако первая — это формула, созданная в Excel. А вторая — это встроенная функция Excel — функция СУММ.

Функции больше похожи на предопределенные формулы в Excel.

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

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

Профессиональный совет!

Иногда функцию можно даже вложить в формулу.

Например, сколько будет 2 + 2 – 3?

Вы можете записать это как = (СУММ (2,2)) – 3

СУММ (2,2) — это функция, и вычитание из нее 3 делает ее самостоятельно созданной формулой.

Вот и все – Что теперь?

До сих пор мы создавали формулы, используя различные операторы, значения и ссылки на ячейки. И научился использовать функцию СУММ для сложения и вычитания.

Мало того, мы еще изучили порядок операций в Excel.