Работа с эксель формулы: 15 формул в Excel, которые помогут в работе

Содержание

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

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

Например, на рисунке ниже ячейка А3 содержит формулу, которая складывает значения ячеек А2 и A1.

Ещё один пример. Ячейка A3 содержит функцию SUM (СУММ), которая вычисляет сумму диапазона A1:A2.

=SUM(A1:A2)
=СУММ(A1:A2)

Ввод формулы

Чтобы ввести формулу, следуйте инструкции ниже:

Выделите ячейку.
Чтобы Excel знал, что вы хотите ввести формулу, используйте знак равенства (=).
К примеру, на рисунке ниже введена формула, суммирующая ячейки А1 и А2.

Совет: Вместо того, чтобы вручную набирать А1 и А2, просто кликните по ячейкам

A1 и A2.

Измените значение ячейки A1 на 3.
Excel автоматически пересчитывает значение ячейки A3. Это одна из наиболее мощных возможностей Excel.

Редактирование формул

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

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

Нажмите Enter.

Приоритет операций

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

Сперва Excel умножает (A1*A2), затем добавляет значение ячейки

A3 к этому результату.

Другой пример:

Сначала Excel вычисляет значение в круглых скобках (A2+A3), потом умножает полученный результат на величину ячейки A1.

Копировать/вставить формулу

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

Введите формулу, показанную ниже, в ячейку A4.
Выделите ячейку А4, кликните по ней правой кнопкой мыши и выберите команду Copy (Копировать) или нажмите сочетание клавиш Ctrl+C.
Далее выделите ячейку B4, кликните по ней правой кнопкой мыши и выберите команду Insert (Вставить) в разделе Paste Options (Параметры вставки) или нажмите сочетание клавиш Ctrl+V.
Ещё вы можете скопировать формулу из ячейки A4 в B4 протягиванием. Выделите ячейку А4, зажмите её нижний правый угол и протяните до ячейки В4. Это намного проще и дает тот же результат!
Результат: Формула в ячейке B4 ссылается на значения в столбце B.

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

Все функции имеют одинаковую структуру. Например:

SUM(A1:A4)
СУММ(A1:A4)

Название этой функции – SUM (СУММ). Выражение между скобками (аргументы) означает, что мы задали диапазон A1:A4 в качестве входных данных. Эта функция складывает значения в ячейках

A1, A2, A3 и A4. Запомнить, какие функции и аргументы использовать для каждой конкретной задачи не просто. К счастью, в Excel есть команда Insert Function (Вставить функцию).

Чтобы вставить функцию, сделайте следующее:

Выделите ячейку.
Нажмите кнопку Insert Function (Вставить функцию).
Появится одноименное диалоговое окно.
Отыщите нужную функцию или выберите её из категории. Например, вы можете выбрать функцию COUNTIF (СЧЕТЕСЛИ) из категории Statistical (Статистические).
Нажмите ОК. Появится диалоговое окно Function Arguments (Аргументы функции).
Кликните по кнопке справа от поля Range (Диапазон) и выберите диапазон A1:C2.
Кликните в поле Criteria (Критерий) и введите “>5”.
Нажмите OK.
Результат: Excel подсчитывает число ячеек, значение которых больше 5.
=COUNTIF(A1:C2;">5")
=СЧЁТЕСЛИ(A1:C2;">5")

Примечание: Вместо того, чтобы использовать инструмент «Вставить функцию», просто наберите =СЧЕТЕСЛИ(A1:C2,”>5″). Когда напечатаете ” =СЧЁТЕСЛИ( “, вместо ввода “A1:C2” вручную выделите мышью этот диапазон.

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

Как пользоваться формулами в Excel

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

Для чего нужны?

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

Создание формулы

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

Нажать левой кнопкой мыши дважды по ячейке.
Ввести «=».
Ввести «2+2».
Нажать на клавишу «Enter».

После этого программа автоматически посчитает пример, результат приведён на рисунке ниже.

Подобным образом можно использовать и иные элементарные вычисления (сложение, вычитание, деление (использовать / для выполнения операции), умножение (использовать * для выполнения операции)).

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

Варианты работы с формулами

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

Ввести данные в произвольные ячейки. Например, три числа: 15, 28, 35.
В свободной ячейке указать =, адреса ячеек и требуемую операцию между ними. Например, сложение.
Нажать на клавишу «Enter».

Результат приведён на рисунке ниже.

Копирование и вставка

Любые формулы можно копировать и вставлять в разные ячейки. Для этого потребуется:

Ячейку с формулой выделить при помощи мыши.
Нажать сочетание клавиш Ctrl+C.
Выбрать свободную ячейку.
Нажать сочетание клавиш Ctrl+V.

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

Пример этого приведён на рисунке ниже.

Авто заполнение

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

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

После этого авто заполнение сработает на ближайшие ячейки.

Абсолютные и относительные ссылки

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

Выбрать ячейку с формулой, в которой нужно зафиксировать значение.
Добавить к ячейке $.
Нажать на клавишу «Enter».

После этого значение ячейки не будет изменяться.

Особые формулы

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

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

Создать столбцы, в которые будет помещено новое значение.
В формулах выбрать «ВПР».

Указать диапазон ячеек, по которым будет выполняться расчёт. В поле «интервальный просмотр» указать значение «ЛОЖЬ», только тогда значения будут точные, а не приблизительные.

Нажать на «ОК». После этого столбец будет заполнен, при необходимости его следует растянуть.

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

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

После этого значение выбранных ячеек будет просуммировано.

10 формул для работы в офисе

Наверх

Рейтинги
Обзоры
- Смартфоны и планшеты
- Компьютеры и ноутбуки
- Комплектующие
- Периферия
- Фото и видео
- Аксессуары
- ТВ и аудио
- Техника для дома
- Программы и приложения
Новости
Советы
- Покупка
- Эксплуатация
- Ремонт
Подборки
- Смартфоны и планшеты
- Компьютеры
- Аксессуары
- ТВ и аудио
- Фото и видео
- Программы и приложения
- Техника для дома
Гейминг

10 формул в Excel, которые облегчат вам жизнь

Чтобы применить любую из перечисленных функций, поставьте знак равенства в ячейке, в которой вы хотите видеть результат. Затем введите название формулы (например, МИН или МАКС), откройте круглые скобки и добавьте необходимые аргументы. Excel подскажет синтаксис, чтобы вы не допустили ошибку.

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

Есть и альтернативный способ указать аргументы. Если после названия функции добавить пустые скобки и нажать на кнопку «Вставить функцию» (fx), появится окно ввода с дополнительными подсказками. Можете использовать его, если вам так удобнее.

1. МАКС

Синтаксис: =МАКС(число1; [число2]; …).

Формула «МАКС» отображает наибольшее из чисел в выбранных ячейках. Аргументами функции могут выступать как отдельные ячейки, так и диапазоны. Обязательно вводить только первый аргумент.

2. МИН

Синтаксис: =МИН(число1; [число2]; …).

Функция «МИН» противоположна предыдущей: отображает наименьшее число в выбранных ячейках. В остальном принцип действия такой же.

Сейчас читают 🔥

3. СРЗНАЧ

Синтаксис: =СРЗНАЧ(число1; [число2]; …).

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

4. СУММ

Синтаксис: =СУММ(число1; [число2]; …).

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

5. ЕСЛИ

Синтаксис: =ЕСЛИ(лог_выражение; значение_если_истина; [значение_если_ложь]).

Формула «ЕСЛИ» проверяет, выполняется ли заданное условие, и в зависимости от результата отображает одно из двух указанных пользователем значений. С её помощью удобно сравнивать данные.

В качестве первого аргумента функции можно использовать любое логическое выражение. Вторым вносят значение, которое таблица отобразит, если это выражение окажется истинным. И третий (необязательный) аргумент — значение, которое появляется при ложном результате. Если его не указать, отобразится слово «ложь».

6. СУММЕСЛИ

Синтаксис: =СУММЕСЛИ(диапазон; условие; [диапазон_суммирования]).

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

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

7. СЧЁТ

Синтаксис: =СЧЁТ(значение1; [значение2]; …).

Эта функция подсчитывает количество выбранных ячеек, которые содержат числа. Аргументами могут выступать отдельные клетки и диапазоны. Для работы функции необходим как минимум один аргумент. Будьте внимательны: «СЧЁТ» учитывает ячейки с датами.

8. ДНИ

Синтаксис: =ДНИ(конечная дата; начальная дата).

Всё просто: функция «ДНИ» отображает количество дней между двумя датами. В аргументы сначала добавляют конечную, а затем начальную дату — если их перепутать, результат получится отрицательным.

9. КОРРЕЛ

Синтаксис: =КОРРЕЛ(диапазон1; диапазон2).

«КОРРЕЛ» определяет коэффициент корреляции между двумя диапазонами ячеек. Иными словами, функция подсчитывает статистическую взаимосвязь между разными данными: курсами доллара и рубля, расходами и прибылью и так далее. Чем больше изменения в одном диапазоне совпадают с изменениями в другом, тем корреляция выше. Максимальное возможное значение — +1, минимальное — −1.

10. СЦЕП

Синтаксис: =СЦЕП(текст1; [текст2]; …).

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

Работа в Эксель с формулами

Создание столбца с результатами вычислений — это самая обычная операция в Экселе.

Есть несколько приемов работы с формулами в Excel. На примере простого товарного чека рассмотрим четыре способа заполнения формулами диапазона.

Нам нужно подсчитать общую сумму по каждому товару и для этого перемножаем цену товара на его количество.

Способ #1. Автозаполнение

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

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

Способ #2. Копирование и вставка

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

Способ #3. Двойной клик

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

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

Способ #4. Умная таблица

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

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

как исправить формулы, которые не обновляются или не вычисляют

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

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

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

Формулы Excel не работают

Признаки : Формула Excel работает неправильно, возвращается ошибка или неправильный результат.

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

1. Сопоставьте все открывающие и закрывающие круглые скобки в формуле

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

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

2. Введите все необходимые аргументы в функцию Excel.

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

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

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

3. Не вкладывайте более 64 функций в формулу

При вложении двух или более функций Excel друг в друга, например создавая вложенную формулу ЕСЛИ, помните о следующих ограничениях:

В Excel 2016, Excel 2013, Excel 2010 и Excel 2007 можно использовать до 64 вложенных функций.
В Excel 2003 и ниже можно использовать до 7 вложенных функций.

4. Не заключайте числа в двойные кавычки

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

Это означает, что если вы введете такую формулу, как = IF (A1> 0, «1») , Excel будет рассматривать число 1 как текст, и поэтому вы не сможете использовать возвращенные единицы в других вычислениях. . Чтобы исправить это, просто удалите двойные кавычки вокруг цифры «1»: = IF (A1> 0, 1) .

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

5. Введите числа без форматирования

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

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

Например, вместо того, чтобы вводить 50 000 долларов в формулу, введите просто 50000 и используйте диалоговое окно Форматирование ячеек (Ctrl + 1) для форматирования вывода по своему вкусу.

6. Убедитесь, что числа не отформатированы как текстовые значения

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

Визуальные индикаторы текстовых чисел следующие:

Числа, отформатированные как текст, по умолчанию выравниваются по левому краю, тогда как обычные числа в ячейках выравниваются по правому краю.
Поле Формат чисел на вкладке Домашняя страница в группе Числа отображает формат Текст .
Когда на листе выбрано несколько ячеек с текстовыми числами, в строке состояния отображается только Count , тогда как обычно для чисел отображается Average , Count и SUM .
В строке формул может быть виден начальный апостроф или в верхнем левом углу ячеек могут отображаться зеленые треугольники.

На снимке экрана ниже показано, что даже простая формула СУММ в Excel может не работать из-за чисел, отформатированных как текст:

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

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

На первый взгляд, следующая формула работает нормально:
= ЕСЛИ (A1 = «Хорошо», «1», «0»)

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

Если маленькие зеленые треугольники не появляются в ячейках по какой-либо другой причине, посмотрите на поле Number Format на вкладке Home в группе Number . Если отображается Текст , попробуйте очистить все форматирование для проблемных ячеек и установите для них формат Число или Общий . Если это не сработает, возможно, вам придется создать новый столбец, вручную ввести данные (например, скопировать свои текстовые числа в Блокнот, а затем обратно в новый столбец) и удалить сломанный столбец.

Другое возможное решение — умножить значения в проблемном столбце на 1, используя простую формулу, например = A1 * 1 . Затем скопируйте ячейки формулы и вставьте их как значения в тот же или любой другой столбец с помощью Специальная вставка > значений .

7. Разделите аргументы функции соответствующим символом

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

Запятая — это разделитель списков по умолчанию в Северной Америке и некоторых других странах. В европейских странах в качестве десятичного символа используется запятая, а в качестве разделителя списка обычно устанавливается , точка с запятой .

Например, в Северной Америке вы должны написать = IF (A1> 0, «OK», «Not OK») , а европейские пользователи Excel должны ввести ту же формулу, что и = IF (A1> 0; «OK ";" Не в порядке ") .

Итак, если ваши формулы Excel не работают из-за: « Мы обнаружили проблему с этой формулой… «Ошибка , перейдите к своим региональным настройкам ( Панель управления > Регион и язык > Дополнительные настройки ) и проверьте, какой символ установлен там как Разделитель списка . А затем используйте именно этот символ для разделения аргументов в формулах Excel.

8. Заключите имена книг и рабочих листов в одинарные кавычки.

При ссылке на другие листы или книги, в именах которых есть пробелов, или неалфавитных символов , заключите имена в «одинарные кавычки».Например,

Ссылка на другой лист:
= СУММ («Январские продажи»! B2: B10)

Ссылка на другую книгу:
= SUM ('[2015 Sales.xlsx] Jan sales'! B2: B10)

Для получения дополнительной информации см. Как сослаться на другой лист или книгу в Excel.

9. Добавьте полный путь к закрытой книге

Если вы пишете формулу, которая ссылается на закрытую книгу Excel, ваша внешняя ссылка должна включать имя книги и полный путь к книге.Например:

= СУММ ('D: \ Reports \ [Sales.xlsx] Jan'! B2: B10)

Для получения дополнительной информации см. Создание ссылки на другую книгу.

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

Формулы Excel не обновляются

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

Когда формулы Excel не обновляются автоматически, скорее всего, это связано с тем, что параметр Calculation был изменен на Manual вместо Automatic . Чтобы исправить это, просто снова установите для параметра Calculation значение Automatic .

На ленте Excel перейдите на вкладку Формулы > группу Расчет , нажмите кнопку Параметры расчета и выберите Автоматически :

Вы также можете изменить этот параметр в Параметры Excel :

В Excel 2003 щелкните Инструменты > Параметры > Расчет > Расчет > Автоматический .
В Excel 2007 нажмите кнопку Office > Параметры Excel > Формулы > Расчет книги > Автоматически .
В Excel 2010 , Excel 2013 и Excel 2016 перейдите в Файл > Параметры > Формулы > Параметры расчета раздел и выберите Автоматический в разделе Расчет книги .

Как заставить формулы Excel пересчитывать

Если по какой-либо причине вам необходимо установить для параметра Calculation значение Manual , вы можете принудительно пересчитать формулы, нажав кнопку Calculate на ленте или используя один из следующих ярлыков:

Для пересчета всей книги :

Нажмите F9 или
Нажмите кнопку Рассчитать сейчас на вкладке Формулы > Группа расчетов .

Для пересчета активного листа :

Нажмите Shift + F9 или
Щелкните Расчет листа на вкладке Формулы > Группа расчетов .

Чтобы пересчитать всех листов во всех открытых книгах , нажмите Ctrl + Alt + F9.

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

Формулы Excel без вычисления

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

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

1. Включен режим отображения формул

Самая распространенная причина, по которой формулы Excel не вычисляются, заключается в том, что вы случайно активировали режим «Показать формулы» на листе.

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

Нажатие сочетания клавиш Ctrl + `или
Нажмите кнопку Показать формулы на вкладке Формулы > Аудит формул .

2. Формула вводится как текст

Еще одна частая причина, по которой формула Excel не вычисляет, — это то, что формула отформатирована как текст. Чтобы проверить это, выберите ячейку формулы и посмотрите на поле Числовой формат в группе Число на вкладке Домашняя страница :

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

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

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

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

Вас также может заинтересовать

формул Excel: простые формулы

закрыть поиск поиск меню

Темы

печать

Английский expand_more expand_less
Español
Português

Простые формулы

Вернуться к учебнику закрыть

Я бы хотел…
- Начать работу с компьютерами
- Изучите Microsoft Office
- Устроиться на работу
- Улучшить свои рабочие навыки
- Создавайте красивые документы
- Больше…
Microsoft
- Офис 2019 | 2016 | 2013
- слово
- Excel
- Силовая установка
- Доступ
- Формулы Excel
- Больше…
Основные навыки
- Компьютеры
- Смартфоны и планшеты
- Учебник по вводу текста
- Windows
- Больше…
Интернет-навыки
- Интернет
- Безопасность в Интернете
- Социальные медиа
- Эл. адрес
- Поиск лучше
- Больше…
Google
- Gmail
- Гугл документы
- Google Таблицы
- Больше…
Работа и карьера
- Планирование карьеры
- Написание резюме
- Сопроводительные письма
- Поиск работы и работа в сети
- Деловое общение
- Больше…
Навыки на сегодня
- Адаптация к изменениям
- 3D печать
- Носимые
- Внештатную работу
- Личные финансы
- Совместная экономика
- Принятие решений
- Больше…
Творчество и дизайн
- Графический дизайн
- Креативность
- Фотография
- Редактирование изображений
- Фотошоп
- Больше…
Основные навыки
- Математика
- Чтение
- Грамматика
- Изучение языка
- Больше…

Для преподавателей
Переводы
Выбор персонала
Все темы

Excel 2010: создание сложных формул

закрыть поиск поиск меню

Темы

печать

Английский expand_more expand_less
Español
Português

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

Вернуться к учебнику закрыть

Я бы хотел…
- Начать работу с компьютерами
- Изучите Microsoft Office
- Устроиться на работу
- Улучшить свои рабочие навыки
- Создавайте красивые документы
- Больше…
Microsoft
- Офис 2019 | 2016 | 2013
- слово
- Excel
- Силовая установка
- Доступ
- Формулы Excel
- Больше…
Основные навыки
- Компьютеры
- Смартфоны и планшеты
- Учебник по вводу текста
- Windows
- Больше…
Интернет-навыки
- Интернет
- Безопасность в Интернете
- Социальные медиа
- Эл. адрес
- Поиск лучше
- Больше…
Google
- Gmail
- Гугл документы
- Google Таблицы
- Больше…
Работа и карьера
- Планирование карьеры
- Написание резюме
- Сопроводительные письма
- Поиск работы и работа в сети
- Деловое общение
- Больше…
Навыки на сегодня
- Адаптация к изменениям
- 3D печать
- Носимые
- Внештатную работу
- Личные финансы
- Совместная экономика
- Принятие решений
- Больше…
Творчество и дизайн
- Графический дизайн
- Креативность
- Фотография
- Редактирование изображений
- Фотошоп
- Больше…
Основные навыки
- Математика
- Чтение
- Грамматика
- Изучение языка
- Больше…

Для преподавателей
Translati

Десять формул Microsoft Excel для всех видов работы

Эти 10 лучших формул Microsoft Excel помогут вам справиться со всеми видами работы и помогут вам лучше работать в качестве менеджера или аналитика

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

1. ВПР ()

VLOOKUP — это умная функция, которая ищет заданное значение в таблице MS Excel и без особых усилий возвращает соответствующую запись. Если вам нужно больше узнать о названии продукта с идентификатором pO76908 или об ученике по имени «Генри», VLOOKUP найдет его для вас.

Синтаксис для ВПР

= ВПР («Po76908», произведение, 5, истина)

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

2.ЛИСТ ()

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

В случае, если ссылка опущена, ЛИСТ () возвращается номер листа.
Если действительное имя листа не указано в качестве ссылки, # ССЫЛКА! возвращается ошибка.
В случае отсутствия листа по ссылочному имени, даже если это допустимая конструкция имени, возвращается ошибка # Н / Д.

Рекомендуемое чтение — восемь полезных сочетаний клавиш Microsoft Excel, которые вы должны знать

3. РАБДЕНЬ () / ЧИСТРАБДНИ ()

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

РАБДЕНЬ () : он находит дату окончания после того, как в качестве входных данных принимает число: рабочих дней и дату начала.

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

4. COUNTA ()

Чаще всего аналитики теряют сознание в отношении количества ячеек, имеющих значения, и тех, которые пусты. Функция COUTA определяет общее количество непустых ячеек в выбранном диапазоне; другими словами, он сообщает, сколько ячеек содержит алфавиты, символы, специальные символы или другие данные.

Синтаксис для COUNTA

= СЧЁТ (A1: A10)

Предлагаемое чтение — Более разумные способы работы в Excel

5. СУММЕСЛИМН ()

Были ли у вас вопросы типа «Сколько двигателей модели B было продано в регионе A потребителю типа C в год Y»? Не волнуйтесь. Просто изучите формулу СУММЕСЛИМН и сложите любой заданный набор значений, не используя бесчисленные фильтры или выборки.

Синтаксис СУММЕСЛИМН

= СУММЕСЛИМН (что нужно добавить, столбец условия1, условие1, столбец условия2, условие 2….)

Пример:

= СУММЕСЛИМН (продажи, площади, «D», продукты, «C», тип клиента, год, «Y»)

6. LEN ()

Хотите узнать общее количество символов, заполняющих ячейку? Только будьте немного осторожны; эта функция подсчитывает количество пробелов между символами!
Синтаксис для LEN

= LEN (A1)

6.ЕСЛИ ()

Все вы, аналитики, использующие MS Excel, должны знать, что функция ЕСЛИ вернет определенное значение, если условие / логический оператор имеет значение ИСТИНА, а другое — если оно ЛОЖЬ.

IF (логический_тест, значение_если_ истинно, [значение_если_ ложь])

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

Предлагаемое чтение — Наиболее часто используемые сочетания клавиш в Microsoft Excel

7. СРЕДНЕЕ ()

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

= СРЕДНИЙ (число1, [число2,…. Число n])

СКИДКА 40% на наш самый продаваемый курс в MS Excel. ПОЛУЧИ ЭТО СЕЙЧАС!

8. ISFORMULA ()

Функция ISFORMULA — это еще один инструмент MS Excel, который помогает менеджерам ссылаться на любую определенную ячейку для возврата значения ИСТИНА / ЛОЖЬ в случае, если в ячейке присутствует формула или нет. Здесь вам нужно быть особенно осторожным, поскольку ваше представление о формуле может не совпадать с представлением Excel!

9.СЦЕПИТЬ ()

Эта функция Microsoft Excel помогает объединить данные из двух разных ячеек в одну. Скажем, вы хотите добавить имя («Отметить», как в ячейке A1) и второе имя («Энтони», как в ячейке B1) конкретного сотрудника в ячейке B11, используйте:

= СЦЕПИТЬ (A1; ««; B1)

Результатом в ячейке B11 будет «Марк Энтони». Вуаля!

10. ЕСЛИ ОШИБКА

Вы нормально справляетесь со своим заблудшим персоналом и неисправными автоматами по продаже кофе; но вы поднимаете руки, когда дело доходит до выяснения таких ошибок, как VLOOKUP # N / As.На помощь приходит вечнозеленая функция:

ЕСЛИ ОШИБКА (формула, действие, которое следует предпринять в случае ошибки)

Например, ЕСЛИОШИБКА (ВПР (….), «Значение найдено!»)

Итак, все ли вы готовы использовать эти формулы Microsoft Excel, чтобы сделать вашу работу более управляемой и простой, чем раньше? Просто идите за ними — вы будете рады, что сделали!

Щелкните здесь, чтобы перейти к курсу MS Excel

Совет недели по Excel № 53 — Причины, по которым формулы не вычисляются

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

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

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

Этот блог также доступен на YouTube.

Описание: Ячейка содержит то, что выглядит как формула, но формула не вычисляет. Вместо этого текст формулы отображается непосредственно в ячейке. См. Пример ниже.

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

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

Метод 1: Перейти в затронутую ячейку (щелчок мышью или F2), а затем немедленно выйти снова. Это быстрее для одной формулы, но если формул много, это займет много времени, и вам следует использовать метод 2.

Метод 2: Используйте команду «Найти и заменить» (Ctrl + F). Выберите замену = на =, и это заставит Excel обновить формулы и начать вычисления в обычном режиме.

Описание: Подборка формул написана правильно, но результаты не соответствуют правильным вводным данным или не имеют смысла.

Причина: Книга переведена в режим «Ручной расчет». Это будет означать, что Excel не обновляет все формулы автоматически при каждом изменении книги и требует принудительного обновления вручную. Это также означает, что если вы копируете и вставляете формулу (например, формулу сложения, показанную справа), результат копируется вместо правильного ответа до тех пор, пока не будет использоваться запрос ручного пересчета.

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

Метод 1: Вы можете принудительно выполнить пересчет вручную, нажав кнопку «Рассчитать» в нижнем левом углу Excel или нажав F9.

Метод 2: Вы можете снова переключить рабочую книгу на автоматический расчет, выбрав Формулы => Параметры расчета.

Эти два являются причиной 99% проблем этого типа.

Работайте умнее и быстрее с формулами, диаграммами и утилитами ASAP в Excel

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

В этом двухчасовом виртуальном классе Дайан Олд продемонстрирует экономящие время и часто неизвестные советы в Excel.Узнайте, как считывать данные из таблиц надбавки заслуг, налоговых таблиц, таблиц комиссионных и т. Д. Без написания сложных вложенных формул ЕСЛИ. Вы обнаружите, что графики еще никогда не были такими простыми, с такими сочетаниями клавиш, как нажатия клавиш и шаблоны. Вы также научитесь использовать ASAP Utilities для быстрого и эффективного применения одной команды к нескольким рабочим листам.

После этого занятия вы будете знать, как:

Создание более эффективных формул поиска вместо сложных вложенных формул
Создание комбинированных диаграмм для эффективного сравнения рынков
Форматируйте, разделяйте и объединяйте файлы распределения за считанные минуты с помощью ASAP Utilities

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

Что вы узнаете:

Раздел 1 — Создание более эффективных формул LOOKUP

Используйте VLOOKUP без False в конце, чтобы читать между значениями, чтобы найти увеличение заслуг, налоговые таблицы, ставки комиссионных или диапазоны любого типа
Используйте INDIRECT и именованные диапазоны для поиска различных таблиц или объедините INDIRECT с формулой ARRAY для получения разных таблиц

Раздел 2 — Создание диаграмм за меньшее время

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

Раздел 3 — Используйте ASAP Utilities для:

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

Все подсказки будут последовательно продемонстрированы на одном примере.