Все формулы в excel начинаются со знака равенства

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

Основы Формул

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

1. Каждая формула в Excel начинается с “=”

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

После знака равенства вы можете размещать в ячейке невероятно разнообразные вещи. Попробуйте ввести =4+4 в качестве вашей первой формулы и нажмите Enter, чтобы отобразить результат. Excel выведет 8, но формула останется за кулисами электронной таблицы.

2. Формулы показываются на панели формул Excel.

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

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

3. Как собрать формулу

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

Excel — это сетка ячеек, а столбцы идут слева направо, каждая назначена на букву, а строки пронумерованы. Каждая ячейка является пересечением строки и столбца. Например, ячейка, где пересекаются столбцы A и строка 3, называется A3.

Предположим, что у меня две ячейки с простыми числами, например 1 и 2, и они находятся в ячейках A2 и A3. Когда я набираю формулу, я могу начать формулу с «=», как всегда. Затем я могу ввести:

=A2+A3

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

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

Базовая статистика

Используйте вкладку “Basic Statistics” в книге для практики.

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

Среднее

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

=СРЗНАЧ(1;3;5;7;10)

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

=СРЗНАЧ(A2:A5)

Медиана

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

=МЕДИАНА(1;3;5;7;10)

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

=МЕДИАНА(A2:A5)

Минимум

Если у вас есть набор данных и вы хотите держать на виду наименьшее значение, полезно использовать формулу МИН в Excel. Вы можете использовать формулу МИН со списком чисел, разделенных точкой с запятыми, чтобы найти самое маленькое значение в наборе. Это очень полезно при работе с большими наборами данных.

=МИН(1;3;5;7;9)

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

=МИН(A1:E1)

Максимум

Формула МАКС в Excel — полная противоположность МИН

=МАКС(1;3;5;7;9)

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

=МАКС(A1:E1)

Циклические вычисления

Если зависимые ячейки Excel образуют цикл, то говорят, что имеют место циклические ссылки (circular references). В обычном режиме Excel обнаруживает цикл и выдает сообщение о возникшей ситуации, требуя устранить циклические ссылки. Следуя обычной семантике, он не может провести вычисления, так как циклические ссылки порождают бесконечные вычисления. Есть два выхода из этой ситуации, – устранить циклические ссылки или изменить настройку в машине вычислений так, чтобы такие вычисления стали возможными. В последнем случае, естественно, требуется, чтобы число повторений цикла было конечным. Excel допускает переход к новой семантике, обеспечивающей проведение циклических вычислений. Вручную, для этого достаточно на вкладке Вычисления (меню Сервис, пункт Параметры) включить флажок Итерации и при необходимости изменить число повторений цикла в окошке “Максимум итераций”. Можно также задать точность вычислений в окошке “Максимальное изменение”, что также приводит к ограничению числа повторений цикла. По умолчанию максимальное число итераций и точность вычислений соответственно имеют значения 100 и 0,0001. Понятно, что включить циклические вычисления и задать значения параметров, определяющих окончание цикла, можно и программно.

Укажем, особенности семантики циклических вычислений:

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

В каких же ситуациях требуется прибегать к циклическим вычислениям? Это, возможно, следует делать, когда речь идет о реализации итерационного процесса, вычислениях по рекуррентным соотношениям. У нас уже были примеры реализации итерационных процессов, например, вычисление суммы ряда, задающего экспоненту, в которых не применялись циклические ссылки. Платой за это было использование дополнительных ячеек таблицы Excel. Правда, появлялись и новые возможности, – возможность построить график, проанализировать процесс сходимости и т.д. Тем не менее, программисту, привыкшему к традиционным языкам, и привыкшему “с детства” экономить на переменных, может показаться странным предложенное решение задачи о нахождении корня уравнения, где на экран выводятся результаты всех приближений. В Excel экономия ячеек не главная задача. Тем не менее, при реализации итерационных процессов можно, конечно, и в Excel иметь одну единственную ячейку X, значение которой изменяется, начиная от начального приближения до искомого результата. Это в большей степени соответствует понятию переменной в языках программирования.

Циклические вычисления и нахождение корней уравнения

Покажем, как можно использовать циклические вычисления на примере задачи нахождения корня уравнения методом Ньютона. Для простоты я начну с квадратного уравнения, а позже рассмотрю и более “серьезные” уравнения. Итак, рассмотрим квадратное уравнение: X² -5X+6 =0. Найти корень этого (и любого другого уравнения) можно, используя всего одну единственную ячейку Excel. Для этого достаточно включить режим циклических вычислений и ввести в произвольную ячейку с именем, скажем X, рекуррентную формулу, задающую вычисления по Ньютону:

где F и F1 задают соответственно выражения, вычисляющие функцию и производную. Для нашего квадратного уравнения после ввода формулы в ней появится значение 2, соответствующее одному из корней уравнения. А как получить второй корень? Обычно, это можно сделать путем изменения начального приближения. В нашем случае начальное приближение не задавалось, итерационный процесс вычислений начинался со значения, хранимого в ячейке X по умолчанию и равного нулю. Как же задать начальное приближение в циклических вычислениях? Возникшая проблема не связана с данной конкретной задачей. Она возникает всегда в циклических вычислениях, – до начала цикла надо задать начальные установки. В рекуррентных соотношениях всегда есть некоторый начальный отрезок. Решать задачу задания начальных установок в каждом случае можно по-разному. Я продемонстрирую один прием, основанный на использовании функции ЕСЛИ. Вот как выглядит “настоящее” решение этой задачи, использующее 4 ячейки, две из которых нужны по существу дела, а две используются для повышения наглядности процесса вычислений:

• В ячейку с именем Xinit я ввел начальное приближение.
• В ячейку Xcur, в которой и будет идти циклический счет, ввел формулу:

  = ЕСЛИ(Xcur =0; Xinit; Xcur - (6- Xcur *(5- Xcur))/(2* Xcur -5))

• В две другие вспомогательные ячейки я поместил текст этой формулы и формулу, задающую вычисление функции в точке Xcur, позволяющую следить за качеством решения.
• Заметьте, что на первом шаге вычислений, функция IF (ЕСЛИ) поместит в ячейку Xcur начальное значение, а затем уже начнет счет по формуле на последующих шагах.
• Чтобы сменить начальное приближение, недостаточно изменить содержимое ячейки Xinit и запустить процесс вычислений. В этом случае вычисления будут продолжены, начиная с последнего вычисленного значения. Чтобы обнулить значение, хранящееся в ячейке Xcur, нужно заново записать туда формулу. Для этого достаточно выбрать ячейку и выделить текст формулы непосредственно в окне ее редактирования. Щелчок по Enter начнет вычисления с новым начальным приближением.

Функции ЧЁТН и НЕЧЁТ

Для выполнения операций округления можно использовать функции ЧЁТН (EVEN) и НЕЧЁТ (ODD). Функция ЧЁТН округляет число вверх до ближайшего четного целого числа. Функция НЕЧЁТ округляет число вверх до ближайшего нечетного целого числа. Отрицательные числа округляются не вверх, а вниз. Функции имеют следующий синтаксис:

=ЧЁТН(число)
=НЕЧЁТ(число)

Функции ОКРВНИЗ, ОКРВВЕРХ

Функции ОКРВНИЗ (FLOOR) и ОКРВВЕРХ (CEILING) тоже можно использовать для выполнения операций округления. Функция ОКРВНИЗ округляет число вниз до ближайшего кратного для заданного множителя, а функция ОКРВВЕРХ округляет число вверх до ближайшего кратного для заданного множителя. Эти функции имеют следующий синтаксис:

=ОКРВНИЗ(число;множитель)
=ОКРВВЕРХ(число;множитель)

Значения число и множитель должны быть числовыми и иметь один и тот же знак. Если они имеют различные знаки, то будет выдана ошибка.

Функции ЦЕЛОЕ и ОТБР

Функция ЦЕЛОЕ (INT) округляет число вниз до ближайшего целого и имеет следующий синтаксис:

=ЦЕЛОЕ(число)

Аргумент – число – это число, для которого надо найти следующее наименьшее целое число.

Рассмотрим формулу:

=ЦЕЛОЕ(10,0001)

Эта формула возвратит значение 10, как и следующая:

=ЦЕЛОЕ(10,999)

Функция ОТБР (TRUNC) отбрасывает все цифры справа от десятичной запятой независимо от знака числа. Необязательный аргумент количество_цифр задает позицию, после которой производится усечение. Функция имеет следующий синтаксис:

=ОТБР(число;количество_цифр)

Если второй аргумент опущен, он принимается равным нулю. Следующая формула возвращает значение 25:

=ОТБР(25,490)

Функции ОКРУГЛ, ЦЕЛОЕ и ОТБР удаляют ненужные десятичные знаки, но работают они различно. Функция ОКРУГЛ округляет вверх или вниз до заданного числа десятичных знаков. Функция ЦЕЛОЕ округляет вниз до ближайшего целого числа, а функция ОТБР отбрасывает десятичные разряды без округления. Основное различие между функциями ЦЕЛОЕ и ОТБР проявляется в обращении с отрицательными значениями. Если вы используете значение -10,900009 в функции ЦЕЛОЕ, результат оказывается равен -11, но при использовании этого же значения в функции ОТБР результат будет равен -10.

Функция ПРОИЗВЕД

Функция ПРОИЗВЕД (PRODUCT) перемножает все числа, задаваемые ее аргументами, и имеет следующий синтаксис:

=ПРОИЗВЕД(число1;число2…)

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

Функция ОСТАТ

Функция ОСТАТ (MOD) возвращает остаток от деления и имеет следующий синтаксис:

=ОСТАТ(число;делитель)

Значение функции ОСТАТ – это остаток, получаемый при делении аргумента число на делитель. Например, следующая функция возвратит значение 1, то есть остаток, получаемый при делении 19 на 14:

=ОСТАТ(19;14)

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

=ОСТАТ(25;40)

Если число точно делится на делитель, функция возвращает 0. Если делитель равен 0, функция ОСТАТ возвращает ошибочное значение.

Функция КОРЕНЬ

Функция КОРЕНЬ (SQRT) возвращает положительный квадратный корень из числа и имеет следующий синтаксис:

=КОРЕНЬ(число)

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

КОРЕНЬ(16)

Если число отрицательное, КОРЕНЬ возвращает ошибочное значение.

Функция ЧИСЛОКОМБ

Функция ЧИСЛОКОМБ (COMBIN) определяет количество возможных комбинаций или групп для заданного числа элементов. Эта функция имеет следующий синтаксис:

=ЧИСЛОКОМБ(число;число_выбранных)

Аргумент число – это общее количество элементов, а число_выбранных – это количество элементов в каждой комбинации. Например, для определения количества команд с 5 игроками, которые могут быть образованы из 10 игроков, используется формула:

=ЧИСЛОКОМБ(10;5)

Результат будет равен 252. Т.е., может быть образовано 252 команды.

Функция ЕЧИСЛО

Функция ЕЧИСЛО (ISNUMBER) определяет, является ли значение числом, и имеет следующий синтаксис:

=ЕЧИСЛО(значение)

Пусть вы хотите узнать, является ли значение в ячейке А1 числом. Следующая формула возвращает значение ИСТИНА, если ячейка А1 содержит число или формулу, возвращающую число; в противном случае она возвращает ЛОЖЬ:

=ЕЧИСЛО(А1)

Формула ЧАСТНОЕ()

Тоже одна из простых операций в математике. В Экселе выполняется тоже несложно: у функции ЧАСТНОЕ() есть два аргумента: делимое и делитель.

В выделенной ячейке выводится частное:

Формула СУММЕСЛИ()

Оператор СУММЕСЛИ() находит сумму чисел. Главное отличие этой функции от СУММ() в том, что здесь в качестве аргумента можно задавать условие (только одно), которое будет показывать, какие значения будут использованы в расчетах, а какие — нет.

В качестве условий могут выступать неравенства со знаками больше, меньше или не равно («>», «<», «< >»). Число, которое не соответствует введенному условию, не будет включен в суммирование.

На рисунке изображено суммирование всех чисел, которые больше 0.

Оранжевым выделены те числа, которые будут включены в расчет функцией СУММЕСЛИ().

Остальные числа просто будут игнорироваться:

 

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

Для этого воспользуемся следующей формулой:

 

Формулы ОКРУГЛ(), ОКРУГЛВВЕРХ(), ОКРУГЛВНИЗ()

Функция ОКРУГЛ() предназначена для округления значения до заданного количества знаков после запятой. В качестве первого аргумента выступают, как обычно, числа или диапазон ячеек, второго – разряд, до которого нужно округлить число.

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

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

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

Если необходимо округлить в сторону меньшего или большего по модулю числа используют функции ОКРУГЛВНИЗ(), ОКРУГЛВВЕРХ(), соответственно:

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

На самом деле, это не так.

Дело в том, что увеличение или уменьшение разрядности влияет только на «внешний вид» ячейки, то есть на то, как мы число видим.

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

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

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

Существуют:

• простые;
• ссылки на другой лист;
• абсолютные;
• относительные ссылки.

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

• пересечение столбца и строки (А4);
• массив ячеек по столбцу А со строки 5 до 20 (А5:А20);
• диапазон клеток по строке 5 со столбца В до R (В5:R5);
• все ячейки строки (10:10);
• все клетки в диапазоне с 10 по 15 строку (10:15);
• по аналогии обозначаются и столбцы: В:В, В:К;
• все ячейки диапазона с А5 до С4 (А5:С4).

Следующий формат адресов: ссылки на другой лист. Оформляется это следующим образом: Лист2!А4:С6. Подобный адрес вставляется в любую функцию.

ABS

С помощью математической формулы ABS производится расчет числа по модулю. У этого оператора один аргумент – «Число», то есть, ссылка на ячейку, содержащую числовые данные. Диапазон в роли аргумента выступать не может. Синтаксис имеет следующий вид:

=ABS(число)

СТЕПЕНЬ

Из названия понятно, что задачей оператора СТЕПЕНЬ является возведение числа в заданную степень. У данной функции два аргумента: «Число» и «Степень». Первый из них может быть указан в виде ссылки на ячейку, содержащую числовую величину. Второй аргумент указывается степень возведения. Из всего вышесказанного следует, что синтаксис этого оператора имеет следующий вид:

=СТЕПЕНЬ(число;степень)

СЛУЧМЕЖДУ

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

=СЛУЧМЕЖДУ(Нижн_граница;Верхн_граница)

РИМСКОЕ

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

=РИМСКОЕ(Число;Форма)

LOG

С помощью этого оператора определяется логарифм числа по заданному основанию. Синтаксис функции представлен в виде:

=LOG(Число;Основание)

Необходимо заполнить два аргумента: Число и Основание логарифма (если его не указать, программа примет значение по умолчанию, равное 10).

Также для десятичного логарифма предусмотрена отдельная функция – LOG10.

Заключение

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

Источники

• https://business.tutsplus.com/ru/tutorials/how-to-use-excel-math-formulas–cms-27554
• https://www.intuit.ru/studies/courses/114/114/lecture/3315
• http://on-line-teaching.com/excel/lsn021.html
• https://blog.sf.education/matematicheskie-funkczii-v-excel/
• https://FB.ru/article/445487/matematicheskie-funktsii-v-excel-osobennosti-i-primeryi
• https://lumpics.ru/mathematical-functions-in-excel/
• https://MicroExcel.ru/matematicheskie-funktsii/

Полные сведения о формулах в Excel

​Смотрите также​​ Ссылка на ячейку​ несколько строк или​=А7/А8​Или так:​Пример 1. Дана таблица​ Чтобы быстро применить формулу​ переменные вместо констант,​ нажать на клавишу​ используются, расположены на​ на закладке «Главная».​подстановочные знаки в​ другое место книги,​ анализа данных из​ на относительной позиции​ ячейки из других​ наглядным примерам вы​Примечание:​ со значением общей​ столбцов.​^ (циркумфлекс)​

​Пример 3. Используя функцию​ с кодами символов:​ к ячейкам ниже​ можно использовать значения​

​ «Num Lk» или​​ клавиатуре. Смотрите в​Как выделить ячейки в​Excel​ Microsoft Excel скорректирует​ одной и той​ ячейки, содержащей формулу,​ книг. Ссылки на​ сможете вычислять сумму,​ Мы стараемся как можно​ стоимости должна быть​Вручную заполним первые графы​

Создание формулы, ссылающейся на значения в других ячейках

1. ​Степень​

2. ​ ТИП, отобразить тип​

   ​ от 65 –​​ в столбце, дважды​ ячеек, например,​

3. ​ «Num Lock» (вверху​ статье «Где на​Excel с похожими словами.​

   

4. ​– это символы,​ сумму с учетом​ же ячейки или​

5. ​ и ячейки, на​ ячейки других книг​ количество, среднее значение​

   

6. ​ оперативнее обеспечивать вас​ абсолютной, чтобы при​ учебной таблицы. У​

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

1. ​=6^2​ данных, которые введены​ до 74:​​ щелкните маркер заполнения​​= A1 + A2​

   

2. ​ в правой части​ клавиатуре кнопка» здесь.​Использовать такие знаки​ которые обозначают определенные​

   

Ввод формулы, содержащей встроенную функцию

1. ​ изменения диапазона листов.​

2. ​ диапазона ячеек на​ которую указывает ссылка.​ называются связями или​ и подставлять данные​ актуальными справочными материалами​

3. ​ копировании она оставалась​ нас – такой​

4. ​= (знак равенства)​ в таблицу вида:​Необходимо с помощью функции​

   

5. ​в первой ячейке,​. Если вы работаете​

Скачивание книги «Учебник по формулам»

​ клавиатуры).​Но специальные символы,​ можно и в​ действия.​Удаление конечного листа​ нескольких листах одной​ При изменении позиции​ внешними ссылками.​ не хуже профессионалов.​ на вашем языке.​ неизменной.​ вариант:​Равно​Функция ТИП возвращает код​ СИМВОЛ отобразить символы,​ содержащей формулу.​ с длинные столбцы​

Подробные сведения о формулах

​Теперь, чтобы ввести​ не часто используемые,​ условном форматировании. Выделим​Например, когда нужно​

Части формулы Excel

​   . Если удалить лист 2​ книги. Трехмерная ссылка​ ячейки, содержащей формулу,​​Стиль ссылок A1​​Чтобы узнать больше об​​ Эта страница переведена​​Чтобы получить проценты в​​Вспомним из математики: чтобы​​Меньше​​ типов данных, которые​​ которые соответствуют данным​

​На листе, содержащем диапазон​​ данных или данных,​

​ любой символ кодом​​ расположены в специальной​​ диапазон. На закладке​ найти в столбце​ или 6, Microsoft​

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

​ автоматически, поэтому ее​​ Excel, не обязательно​​ найти стоимость нескольких​>​ могут быть введены​ кодам.​

​ чисел, щелкните пустую​​ который хранится в​​ в ячейку, нажимаем​ таблице.​ «Главная» нажимаем на​ слово в разных​ Excel скорректирует сумму​

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

​ ячейку или диапазон,​ При копировании или​ стиль ссылок A1,​ просмотрите соответствующие разделы​ текст может содержать​ умножать частное на​ единиц товара, нужно​Больше​ в ячейку Excel:​Для этого введем в​ ячейку, в которой​ разных частях листа​ клавишу «Alt», удерживаем​Таблица символов​ кнопку функции «Условное​ падежах (молоко, молоком,​ с учетом изменения​ перед которой указываются​ заполнении формулы вдоль​ в котором столбцы​ ниже.​ неточности и грамматические​ 100. Выделяем ячейку​ цену за 1​Меньше или равно​Типы данных​

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

​ ячейку В2 формулу​ должны выводиться результаты​ или на другом​ её нажатой и​Excel​ форматирование». Выбираем функцию​ молоку, т.д.), то​ диапазона листов.​ имена листов. В​ строк и вдоль​ обозначаются буквами (от​Формула также может содержать​ ошибки. Для нас​ с результатом и​ единицу умножить на​>=​Код​ следующего вида:​ формулы.​ листе, вы можете​ вводим цифры кода​расположена на закладке​ «Правила выделенных ячеек»​

• ​ в пустой ячейке​

  ​Стиль ссылок R1C1​ Microsoft Excel используются​ столбцов ссылка автоматически​ A до XFD,​ один или несколько​ важно, чтобы эта​ нажимаем «Процентный формат».​ количество. Для вычисления​Больше или равно​Числовой​Аргумент функции: Число –​Введите знак равенства (=)​ использовать диапазон —​ символа. Отпускаем клавишу​ «Вставка» в разделе​ -> «Текст содержит».​ пишем такую формулу.​Можно использовать такой стиль​ все листы, указанные​ корректируется. По умолчанию​

  ​ не более 16 384 столбцов),​	​ таких элементов, как​
  ​ статья была вам​ Или нажимаем комбинацию​ стоимости введем формулу​	​<>​
  ​1​ код символа.​	​ и функцию, например​
  ​ к примеру,​ «Alt». Символ появился​	​ «Текст» кнопка «Символ».​
  ​ Заполняем так.​ Мы написали формулу​	​ ссылок, при котором​
  ​ между начальным и​ в новых формулах​ а строки —​	​функции​
  ​ полезна. Просим вас​ горячих клавиш: CTRL+SHIFT+5​	​ в ячейку D2:​
  ​Не равно​Текстовый​В результате вычислений получим:​	​=МИН​
  ​=SUM(A1:A100)/SUM(B1:B100)​ в ячейке.​	​Для примера, как​

• ​Нажимаем «ОК». Получилось так.​ в ячейке В5.​ нумеруются и строки,​ конечным именами в​ используются относительные ссылки.​

  ​ номерами (от 1​,​ уделить пару секунд​Копируем формулу на весь​ = цена за​Символ «*» используется обязательно​

  

  ​2​Как использовать функцию СИМВОЛ​

  ​. Функция МИН находит​, представляющий деления суммы​Коды символов​

  ​ можно использовать символы,​Выделились все слова, в​=СЧЁТЕСЛИ(A1:A10;»молок*») Как написать​ и столбцы. Стиль​

  ​ ссылке. Например, формула​​ Например, при копировании​ до 1 048 576). Эти​ссылки​ и сообщить, помогла​ столбец: меняется только​ единицу * количество.​ при умножении. Опускать​

• ​Логический​ в формулах на​

  1. ​ наименьшее число в​​ первого сотен числа​Excel.​ смотрите, как вставить​ которых есть слово​ формулу с функцией​ ссылок R1C1 удобен​ =СУММ(Лист2:Лист13!B5) суммирует все​ или заполнении относительной​ буквы и номера​,​ ли она вам,​ первое значение в​ Константы формулы –​ его, как принято​4​ практике? Например, нам​ диапазоне ячеек.​ в столбце A​Здесь приведены коды​ и использовать символ​ «шуруп».​ «СЧЕТЕСЛИ», читайте в​ для вычисления положения​

     ​ значения, содержащиеся в​ ссылки из ячейки​​ называются заголовками строк​

     

  2. ​операторы​​ с помощью кнопок​ формуле (относительная ссылка).​ ссылки на ячейки​ во время письменных​Значение ошибки​ нужно отобразить текстовую​Введите открывающую круглую скобку,​ на сумму эти​ часто используемых символов.​ «Стрелка», в статье​О других способах​ статье «Функция «СЧЁТЕСЛИ»​ столбцов и строк​ ячейке B5 на​ B2 в ячейку​ и столбцов. Чтобы​и​ внизу страницы. Для​ Второе (абсолютная ссылка)​ с соответствующими значениями.​ арифметических вычислений, недопустимо.​16​ строку в одинарных​ выберите диапазон ячеек,​ числа в столбце​

     ​В Excel можно​ «Символ в Excel​​ поиска в таблице​

     

  3. ​ в Excеl». Нашлось​​ в макросах. При​ всех листах в​ B3 она автоматически​ добавить ссылку на​константы​ удобства также приводим​ остается прежним. Проверим​Нажимаем ВВОД – программа​ То есть запись​Массив​ кавычках. Для Excel​ которые требуется включить​ B. Когда ссылается​ установить в ячейке​ для визуализации данных».​ Excel, читайте в​ таких 4 слова.​ использовании стиля R1C1​ диапазоне от Лист2​ изменяется с =A1​ ячейку, введите букву​.​ ссылку на оригинал​ правильность вычислений –​ отображает значение умножения.​ (2+3)5 Excel не​64​ одинарная кавычка как​

     ​ в формулу, и​ формула в другие​​ ссылку в виде​

     

• ​Коды символов Excel.​

  ​ статье «Поиск в​В формуле написали слово​​ в Microsoft Excel​ до Лист13 включительно.​ на =A2.​ столбца, а затем —​Части формулы​ (на английском языке).​ найдем итог. 100%.​ Те же манипуляции​ поймет.​Введем формулу для вычисления​ первый символ –​ введите закрывающую круглую​ ячейки, при каждом​ символа на конкретную​Каждый символ имеет​ Excel» тут.​ «молок» и поставили​ положение ячейки обозначается​При помощи трехмерных ссылок​Скопированная формула с относительной​ номер строки. Например,​   ​

  • ​Начните создавать формулы и​ Все правильно.​ необходимо произвести для​Программу Excel можно использовать​ в ячейку В2:​ это спец символ,​ скобку.​ изменении данных во​ строку в другой​ свой код. Его​Про других символы,​ звездочку (*) –​ буквой R, за​

  • ​ можно создавать ссылки​ ссылкой​

  • ​ ссылка B2 указывает​1.​ использовать встроенные функции,​При создании формул используются​ всех ячеек. Как​ как калькулятор. То​

  ​Аргумент функции: Значение –​ который преобразует любое​Нажмите клавишу RETURN.​​ всех ячейках Excel​ таблице на другом​ можно посмотреть в​ что они означают,​ это значит, что​ которой следует номер​ на ячейки на​   ​ на ячейку, расположенную​Функции​ чтобы выполнять расчеты​ следующие форматы абсолютных​ в Excel задать​ есть вводить в​

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

  • ​ строки, и буквой​​ других листах, определять​Абсолютные ссылки​ на пересечении столбца B​. Функция ПИ() возвращает​ и решать задачи.​ ссылок:​

  • ​ формулу для столбца:​​ формулу числа и​В результате получим:​ текстовый тип данных.​ МИН возвращает​Можно также создать формулу​ таблица с общими​ в таблице символов​ в статье «Символы​ все ячейки со​ C, за которой​ имена и создавать​

  • ​   . Абсолютная ссылка на ячейку​​ и строки 2.​ значение числа пи:​Важно:​$В$2 – при копировании​ копируем формулу из​ операторы математических вычислений​

  • ​Таким образом с помощью​​ Поэтому в самой​11​ с помощью функции​ данными. Нам нужно​ на нужный символ​

• ​ в формулах Excel».​

  ​ словами, которые начинаются​ следует номер столбца.​ формулы с использованием​ в формуле, например​Ячейка или диапазон​ 3,142…​ Вычисляемые результаты формул и​ остаются постоянными столбец​ первой ячейки в​ и сразу получать​ функции ТИП всегда​ ячейке одинарная кавычка​ — наименьшее число в​ предопределенных формулу, которая​ узнать конкретную информацию​ и в строке​

  ​Какими способами можно​	​ на «молок», а​
  ​Ссылка​	​ следующих функций: СУММ,​ $A$1, всегда ссылается​Использование​2.​
  ​ некоторые функции листа​	​ и строка;​ другие строки. Относительные​ результат.​ можно проверить что​ как первый символ​
  ​ ячейках от A1​	​ упрощает ввод вычислений.​ по какому-то пункту​ «Код знака» виден​
  ​ сравнить данные с​	​ дальше имеют разные​Значение​ СРЗНАЧ, СРЗНАЧА, СЧЁТ,​
  ​ на ячейку, расположенную​	​Ячейка на пересечении столбца​Ссылки​

  ​ Excel могут несколько​B$2 – при копировании​ ссылки – в​Но чаще вводятся адреса​ на самом деле​ – не отображается:​​ до C4.​​Знак равенства​ (контактные данные по​ код этого символа.​ помощью диаграммы, смотрите​ окончания.​R[-2]C​ СЧЁТЗ, МАКС, МАКСА,​

  ​ в определенном месте.​ A и строки​. A2 возвращает значение​ отличаться на компьютерах​​ неизменна строка;​​ помощь.​​ ячеек. То есть​​ содержит ячейка Excel.​​Для решения данной задачи​​При вводе формулы в​​начать всех формул. ​​ человеку, т.д.). Нажимаем​ Ставим в строке​ в статье «Диаграмма​​Символ «звездочка» в​​относительная ссылка на ячейку,​

  ​ МИН, МИНА, ПРОИЗВЕД,​

support.office.com

Подстановочные знаки в Excel.

​ При изменении позиции​​ 10​ ячейки A2.​​ под управлением Windows​$B2 – столбец не​Находим в правом нижнем​ пользователь вводит ссылку​​ Обратите внимание что​ ​ используем такую формулу​​ ячейке формула также​Константы​ на ссылку и​
​ «из» — «Юникод»,​ в Excel план-факт».​Excel​ расположенную на две​ СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА,​ ячейки, содержащей формулу,​A10​3.​ с архитектурой x86​
​ изменяется.​ углу первой ячейки​ на ячейку, со​ дата определяется функцией​ с функцией =СИМВОЛ(39)​ отображается в строке​
​, например числовое или​ Excel переходит в​ если не срабатывает,​Кроме цифр и​(​ строки выше в​ СТАНДОТКЛОНПА, ДИСПР, ДИСП.В,​ абсолютная ссылка не​Диапазон ячеек: столбец А,​Константы​
​ или x86-64 и​ ​Чтобы сэкономить время при​​ столбца маркер автозаполнения.​​ значением которой будет​​ как число. Для​Также данную функцию полезно​ формул.​ текстовое значение, можно​ другую таблицу на​
​ то — «Кирилица​ букв, можно применять​*​ том же столбце​ ДИСПА и ДИСППА.​ изменяется. При копировании​
​ строки 10-20.​. Числа или текстовые​ компьютерах под управлением​ введении однотипных формул​ Нажимаем на эту​ оперировать формула.​​ Excel любая дата​ применять, когда нужно​​Кнопки в строке формул​ ​ ввести непосредственно в​​ другом листе на​​ (дес.). Обязательно проверяйте,​​ в Excel определенные​) означает любой текст​R[2]C[2]​Трехмерные ссылки нельзя использовать​ или заполнении формулы​A10:A20​ значения, введенные непосредственно​ Windows RT с​ в ячейки таблицы,​ точку левой кнопкой​При изменении значений в​ — это числовое​
​ формулой сделать перенос​ могут помочь вам​ формулу. ​ строку именно этого​ что написано и​​ символы. Есть​ после слова, букв,​Относительная ссылка на ячейку,​ в формулах массива.​ по строкам и​
​Диапазон ячеек: строка 15,​ в формулу, например​ архитектурой ARM. Подробнее​ ​ применяются маркеры автозаполнения.​
​ мыши, держим ее​ ячейках формула автоматически​ значение, которое соответствует​ строки в ячейке​ в создании формул.​Операторы​ человека, т.д. Для​
​ ставьте нужное в​ ​в​
​ которое мы написали​ расположенную на две​Трехмерные ссылки нельзя использовать​ столбцам абсолютная ссылка​ столбцы B-E​ 2.​ об этих различиях.​ Если нужно закрепить​ и «тащим» вниз​ пересчитывает результат.​​ количеству дней, прошедших​
​ Excel. И для​Чтобы проверить формулу, нажмите​определяют тип вычисления,​
​ ссылки можно использовать​ строках «Шрифт» и​Excel таблица символов​ в формуле перед​ строки ниже и​
​ вместе с оператор​ не корректируется. По​B15:E15​4.​Выделите ячейку.​
​ ссылку, делаем ее​ по столбцу.​Ссылки можно комбинировать в​ от 01.01.1900 г​ других подобного рода​

excel-office.ru

Символ в Excel.

​. Если ошибок​ которое выполняет формулу.​ любые символы. Подробнее​ «Набор» в таблице​​. Эти символы не​ ​ звездочкой (например, «молок*»).​​ на два столбца​ пересечения (один пробел),​ умолчанию в новых​Все ячейки в строке​
​Операторы​ ​Введите знак равенства «=».​​ абсолютной. Для изменения​Отпускаем кнопку мыши –​ рамках одной формулы​ до исходной даты.​ задач.​ нет, в ячейке​ Например ^ («крышка»)​ о такой функции​ символа.​ просто написаны как​Можно сделать по-другому.​ правее​ а также в​ формулах используются относительные​ 5​. Оператор ^ (крышка)​
​Примечание:​ значений при копировании​ формула скопируется в​ с простыми числами.​ Поэтому каждую дату​Значение 39 в аргументе​ будет выведен результат​ оператор возведения числа​ Excel читайте в​Набор символов бывает​ буквы, а выполняют​ В ячейке В2​R2C2​ формулах с неявное​ ссылки, а для​5:5​ применяется для возведения​ Формулы в Excel начинаются​ относительной ссылки.​
​ выбранные ячейки с​ ​Оператор умножил значение ячейки​​ в Excel следует​ функции как вы​ формулы. Если же​ в степень и​ статье «Гиперссылка в​ «​ определенную функцию.​
​ написать «молок*». А​Абсолютная ссылка на ячейку,​ пересечение.​ использования абсолютных ссылок​Все ячейки в строках​
​ числа в степень,​ со знака равенства.​Простейшие формулы заполнения таблиц​ относительными ссылками. То​
​ В2 на 0,5.​ ​ воспринимать как числовой​​ уже догадались это​ ошибки есть, появится​ * (звездочка) оператор​
​ Excel на другой​Надстрочный/подстрочный​Что в​ в ячейке В5​ расположенную во второй​Что происходит при перемещении,​ надо активировать соответствующий​
​ с 5 по​
​ а * (звездочка) —​Выберите ячейку или введите​ в Excel:​ есть в каждой​ Чтобы ввести в​ тип данных отображаемый​ код символа одинарной​ значок​ вычисляет произведение чисел. ​ лист».​». Это значит, что​Excel означает символ​ написать такую формулу.​ строке второго столбца​ копировании, вставке или​ параметр. Например, при​ 10​ для умножения.​ ее адрес в​​Перед наименованиями товаров вставим​ ячейке будет своя​​ формулу ссылку на​​ в формате ячейки​ кавычки.​. Наведите на​Функции​​О других сочетаниях​ символ пишется верху​
​. Например, если начинаем​ =СЧЁТЕСЛИ(A1:A10;B2) Получится так.​R[-1]​ удалении листов​
​ копировании или заполнении​5:10​Константа представляет собой готовое​ выделенной.​ еще один столбец.​ формула со своими​ ячейку, достаточно щелкнуть​ – «Дата».​​ него указатель, чтобы​готовый формулы, которые​
​ клавиш читайте в​ цифры, слова (например,​ вводить в ячейку​Такой формулой можно выбрать​
​Относительная ссылка на строку,​   . Нижеследующие примеры поясняют, какие​
​ абсолютной ссылки из​Все ячейки в столбце​
​ (не вычисляемое) значение,​
​Введите оператор. Например, для​ Выделяем любую ячейку​ аргументами.​ по этой ячейке.​Формула предписывает программе Excel​Пример 2. В таблице​ просмотреть описание проблемы,​ можно использовать отдельно,​ статье «Горячие клавиши​ градус)​ данные со знака​ весь товар из​ расположенную выше текущей​ изменения происходят в​
​ ячейки B2 в​ H​ которое всегда остается​ вычитания введите знак​ в первой графе,​Ссылки в ячейке соотнесены​В нашем примере:​
​ порядок действий с​ дано 3 числа.​ или щелкните стрелку​ или как часть​ Excel» тут.​. Или внизу​ «равно» (=), то​ таблицы с одним​ ячейки​
​ трехмерных ссылках при​ ​ ячейку B3 она​​H:H​ неизменным. Например, дата​
​ «минус».​ щелкаем правой кнопкой​ со строкой.​Поставили курсор в ячейку​ числами, значениями в​ Вычислить, какой знак​ вниз, чтобы получить​ формулу более. Каждая​Как установить проверку​ цифры, числа -​ этот знак говорит​ названием и разными​R​ перемещении, копировании, вставке​ остается прежней в​Все ячейки в столбцах​ 09.10.2008, число 210​Выберите следующую ячейку или​ мыши. Нажимаем «Вставить».​Формула с абсолютной ссылкой​ В3 и ввели​ ячейке или группе​ имеет каждое число:​ дополнительную помощь в​ функция имеет синтаксис​ вводимых данных в​
​Найти в таблице​ Excel, что вводится​ кодами (шуруп А1,​Абсолютная ссылка на текущую​
​ и удалении листов,​ обеих ячейках: =$A$1.​ с H по​ и текст «Прибыль​ введите ее адрес​

excel-office.ru

Ввод формулы

​ Или жмем сначала​​ ссылается на одну​ =.​ ячеек. Без формул​ положительный (+), отрицательный​ устранении неполадки.​ определенных аргументов. ​ Excel, смотрите в​ символ можно, введя​ формула, по которой​ шуруп 123, т.д.).​ строку​ на которые такие​Скопированная формула с абсолютной​ J​ за квартал» являются​ в выделенной.​ комбинацию клавиш: CTRL+ПРОБЕЛ,​ и ту же​Щелкнули по ячейке В2​ электронные таблицы не​ (-) или 0.​

​Чтобы вернуться к предыдущей​Значения ячеек​ статье «Проверка данных​ код в строку​ нужно посчитать. То​Ещё один подстановочный​При записи макроса в​ ссылки указывают. В​ ссылкой​​H:J​​ константами. Выражение или​Нажмите клавишу ВВОД. В​ чтобы выделить весь​

​ ячейку. То есть​ – Excel «обозначил»​ нужны в принципе.​Введем данные в таблицу​​ формуле, нажмите​​позволяют обращаться к​ в Excel».​ «Код знака».​ же самое со​ знак – это​ Microsoft Excel для​ примерах используется формула​   ​Диапазон ячеек: столбцы А-E,​​ его значение константами​​ ячейке с формулой​ столбец листа. А​ при автозаполнении или​ ее (имя ячейки​Конструкция формулы включает в​ вида:​.​ ячейке Excel, вместо​Примечание:​Ещё вариант сделать​ знаками «Сложение» и​

​символ «Знак вопроса» в​ некоторых команд используется​ =СУММ(Лист2:Лист6!A2:A5) для суммирования​Смешанные ссылки​

​ строки 10-20​​ не являются. Если​

​ отобразится результат вычисления.​​ потом комбинация: CTRL+SHIFT+»=»,​ копировании константа остается​ появилось в формуле,​ себя: константы, операторы,​

​Введем в ячейку В2​​Чтобы выбрать функцию, используйте​ внутри ячейки конкретное​Мы стараемся как​ цифру, букву надстрочной​ «Вычитание». Если вводим​Excel​ стиль ссылок R1C1.​

​ значений в ячейках​​   . Смешанная ссылка содержит либо​A10:E20​ формула в ячейке​При вводе в ячейку​ чтобы вставить столбец.​ неизменной (или постоянной).​

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

Ввод формулы, ссылающейся на значения в других ячейках

1. ​ Например, если записывается​ с A2 по​ абсолютный столбец и​Создание ссылки на ячейку​

2. ​ содержит константы, а​​ формула также отображается​​Назовем новую графу «№​

3. ​Чтобы указать Excel на​ «мелькающий» прямоугольник).​ диапазонов, круглые скобки​

   

4. ​Аргумент функции: Число –​При выборе функции открывается​ ячейки можно изменить​ вас актуальными справочными​ маленькую букву «о».​ то Excel понимает,​?​ команда щелчка элемента​ A5 на листах​ относительную строку, либо​ или диапазон ячеек​

   

5. ​ не ссылки на​ в​ п/п». Вводим в​ абсолютную ссылку, пользователю​

   

6. ​Ввели знак *, значение​

   ​ содержащие аргументы и​ любое действительное числовое​

   

   ​ построитель формул с​​ без функции, которая​ материалами на вашем​ Выделяем её. Нажимаем​ что нужно соединить​​). В формуле он​Автосумма​

Ввод формулы, содержащей функцию

1. ​ со второго по​ абсолютную строку и​ с другого листа​ другие ячейки (например,​строке формул​

2. ​ первую ячейку «1»,​ необходимо поставить знак​​ 0,5 с клавиатуры​​ другие формулы. На​ значение.​ дополнительной информацией о​

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

   

4. ​ шестой.​

   ​ относительный столбец. Абсолютная​ в той же​​ имеет вид =30+70+110),​​.​ во вторую –​ доллара ($). Проще​

Советы

​ и нажали ВВОД.​ примере разберем практическое​Скопировав эту формулу вниз,​ функции.​

​ внося изменений. ​ переведена автоматически, поэтому​ из контекстного меню​

• ​ текста.​​ знак. Например, нам​ суммирующей диапазон ячеек,​Вставка или копирование.​ ссылка столбцов приобретает​ книге​ значение в такой​​Чтобы просмотреть формулу, выделите​ «2». Выделяем первые​ всего это сделать​Если в одной формуле​ применение формул для​ получим:​В Excel часто необходимо​

• ​На листе, содержащем столбцы​ ее текст может​​ функцию «Формат ячейки»,​

• 

• ​Есть видимые символы,​ нужно найти все​

  

  ​ в Microsoft Excel​    Если вставить листы между​ вид $A1, $B1​В приведенном ниже примере​

  

support.office.com

Функции СИМВОЛ ЗНАК ТИП в Excel и примеры работы их формул

​ ячейке изменяется только​ ячейку, и она​ две ячейки –​ с помощью клавиши​ применяется несколько операторов,​

​ начинающих пользователей.​Сначала посчитаем количество отрицательных​

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

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

​ «цепляем» левой кнопкой​ F4.​ то программа обработает​

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

Примеры использования функций СИМВОЛ, ТИП и ЗНАК в формулах Excel

​ с буквы «с»​ будет использован стиль​ 6, Microsoft Excel​ ссылка строки приобретает​

​ среднее значение в​ Обычно лучше помещать​ формул.​ мыши маркер автозаполнения​

​Создадим строку «Итого». Найдем​ их в следующей​ ячейки, необходимо активизировать​

​ в столбцах «Прибыль»​ с символами, знаком​

​ выводиться результаты формулы.​

​ нас важно, чтобы​ «ОК».​ невидимые символы, их​ и заканчиваются на​ ссылок R1C1, а​ прибавит к сумме​ вид A$1, B$1​ диапазоне B1:B10 на​ такие константы в​Выделите пустую ячейку.​ – тянем вниз.​ общую стоимость всех​ последовательности:​ ее (поставить курсор)​ и «ЗНАК»:​ числа и типом.​

​Введите знак равенства (​ эта статья была​Или выделяем цифру.​

​ не видно в​ буквы «ла» (сЕла,​ не A1.​ содержимое ячеек с​ и т.д. При​ листе «Маркетинг» в​ отдельные ячейки, где​

​Введите знак равенства «=»,​По такому же принципу​ товаров. Выделяем числовые​%, ^;​ и ввести равно​

​А теперь суммируем только​

Как посчитать количество положительных и отрицательных чисел в Excel

​Для этого используются следующие​=​ вам полезна. Просим​ В формате ячеек​ ячейках, но они​ сИла). В пустой​

​Чтобы включить или отключить​ A2 по A5​

​ изменении позиции ячейки,​ той же книге.​

​ их можно будет​ а затем — функцию.​ можно заполнить, например,​

​ значения столбца «Стоимость»​*, /;​

​ (=). Так же​ положительные или только​ функции:​).​

​ вас уделить пару​ ставим галочку у​ есть и выполняют​

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

​ легко изменить при​ Например, чтобы получить​ даты. Если промежутки​ плюс еще одну​+, -.​

​ можно вводить знак​ отрицательные числа:​СИМВОЛ;​Щелкните первую ячейку, которую​ секунд и сообщить,​ функции «подстрочный».​ свою функцию. Например,​

​ такую формулу. =СЧЁТЕСЛИ(A1:A10;»с?ла»)​ R1C1, установите или​Удаление​ ссылка изменяется, а​

​ «Маркетинг».​

Проверка какие типы вводимых данных ячейки в таблице Excel

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

​Поменять последовательность можно посредством​ равенства в строку​Как сделать отрицательное число​ТИП;​

​ D2:D9​ круглых скобок: Excel​

​ формул. После введения​ положительным, а положительное​

​ЗНАК.​

​ вычисление.​ вам, с помощью​ ячейку Excel.​ «разрыв строки» в​Или напишем искомое слово​Стиль ссылок R1C1​ между листами 2​ изменяется. При копировании​ ячеек от B1​ на эти ячейки.​Введите открывающую круглую скобку​ год. Введем в​Воспользуемся функцией автозаполнения. Кнопка​ в первую очередь​ формулы нажать Enter.​ отрицательным? Очень просто​Функция СИМВОЛ дает возможность​Введите оператор. Оператор представляет​ кнопок внизу страницы.​Устанавливаем курсор в​

exceltable.com

Работа в Excel с формулами и таблицами для чайников

​ ячейке, другие символы.​ в ячейке В2​в разделе​ и 6, Microsoft​ или заполнении формулы​ до B10​Ссылка указывает на ячейку​

​ «(«.​ первую ячейку «окт.15»,​ находится на вкладке​ вычисляет значение выражения​ В ячейке появится​ достаточно умножить на​ получить знак с​ математическую операцию, выполняемую​ Для удобства также​

Формулы в Excel для чайников

​ нужную ячейку.​ Они могут помешать​ «с?ла», а в​Работа с формулами​ Excel не будет​ вдоль строк и​3. Восклицательный знак (!)​ или диапазон ячеек​Выделите диапазон ячеек, а​ во вторую –​ «Главная» в группе​

​ в скобках.​ результат вычислений.​

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

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

​ а абсолютная ссылка​ на диапазон ячеек.​ находятся необходимые формуле​Нажмите клавишу ВВОД, чтобы​ «протянем» за маркер​ «Сумма» (или комбинации​

​ на ячейки: относительные​Оператор​ оператора вычитания –​

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

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

​Примечание:​

1. ​ значения или данные.​ получить результат.​ вниз.​
2. ​ клавиш ALT+«=») слаживаются​ и абсолютные. При​Операция​ минус, перед ссылкой​ с других компьютеров,​ / (косая черта),​
3. ​ для вычисления значений​ расположена НЕ над​ в статье «Как​

​ в статье «Примеры​. Чтобы открыть это​ листом 2 и​ при копировании или​ Если название упоминаемого листа​

• ​ С помощью ссылок​
• ​Мы подготовили для вас​
• ​Найдем среднюю цену товаров.​

​ выделенные числа и​ копировании формулы эти​Пример​ на ячейку:​ в символы данного​

​ чтобы разделить числа.​

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

​ на листе. Все​ буквами вверху клавиатуры,​ удалить лишние пробелы​ функции «СУММЕСЛИМН» в​ окно, перейдите на​ листом 6, переместить​ заполнении смешанной ссылки​

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

1. ​ формулы начинаются со​ а — или​ в Excel» тут.​ Excel».​
2. ​ вкладку​ таким образом, чтобы​ из ячейки A2​ цифры, его нужно​ одной формуле данные,​ с формулами, которая​ ценами + еще​ пустой ячейке.​ по-разному: относительные изменяются,​Сложение​ число с любым​Функция ТИП определяет типы​ формула должна выглядеть​
3. ​ знака равенства (=).​ справа от букв,​Символ​Как найти все слова​Файл​ они оказались перед​ в ячейку B3​ заключить в апострофы​ находящиеся в разных​ доступна для скачивания.​ одну ячейку. Открываем​Сделаем еще один столбец,​

​ абсолютные остаются постоянными.​=В4+7​ знаком сделать положительным?​ данных ячейки, возвращая​ так:​ С помощью константа​ или на ноутбуке​Excel​

​ с разными окончаниями​.​ листом 2 или​ она изменяется с​ (‘), например так:​ частях листа, а​ Если вы впервые​ меню кнопки «Сумма»​

​ где рассчитаем долю​Все ссылки на ячейки​

​- (минус)​ Тогда следует использовать​ соответствующее число.​Щелкните следующую ячейку, которую​ и вычислений оператор​ на клавишах букв.​можно вставить в​

​ в​К началу страницы​ после листа 6,​ =A$1 на =B$1.​ ‘123’!A1 или =’Прибыль​ также использовать значение​ пользуетесь Excel или​

1. ​ — выбираем формулу​ каждого товара в​ программа считает относительными,​Вычитание​ функцию ABS. Данная​Функция ЗНАК возвращает знак​ нужно включить в​
2. ​ можно создать простую​ Например, 1 –​ формулу, и он​Excel.​
3. ​Можно в формулу​ Microsoft Excel вычтет​Скопированная формула со смешанной​ за январь’!A1.​ одной ячейки в​ даже имеете некоторый​

​ для автоматического расчета​ общей стоимости. Для​ если пользователем не​=А9-100​ функция возвращает любое​

1. ​ числа и возвращает​ вычисление. Теперь формула​ формулу. Например, формула​ на клавише с​ будет выполнять определенную​Эти подстановочные знаки​Excel вставить символ, подстановочные​ из суммы содержимое​ ссылкой​Различия между абсолютными, относительными​
2. ​ нескольких формулах. Кроме​ опыт работы с​ среднего значения.​ этого нужно:​ задано другое условие.​* (звездочка)​ число по модулю:​ значение 1, если​
3. ​ должна выглядеть так:​= 5 + 2 * 3​ буквой О, 2​ функцию. Читайте о​ можно вставлять не​ знаки​ ячеек с перемещенных​   ​ и смешанными ссылками​

​ того, можно задавать​ этой программой, данный​Чтобы проверить правильность вставленной​

• ​Разделить стоимость одного товара​ С помощью относительных​Умножение​
• ​Теперь не сложно догадаться​ оно положительное, 0,​
• ​Нажмите клавишу RETURN.​, Перемножение двух чисел​

Как составить таблицу в Excel с формулами

​ – на клавише​ таких символах в​ только в формулы,​. Эти символы можно​ листов.​Стиль трехмерных ссылок​Относительные ссылки​ ссылки на ячейки​ учебник поможет вам​

​ формулы, дважды щелкните​ на стоимость всех​

1. ​ ссылок можно размножить​=А3*2​ как сделать любое​ если равно 0,​В ячейке отобразится результат​ и прибавляющей к​ с буквой Л.​ статье «Подстановочные знаки​ но и в​ применить для поиска,​Перемещение конечного листа​Удобный способ для ссылки​
2. ​   . Относительная ссылка в формуле,​ разных листов одной​ ознакомиться с самыми​ по ячейке с​ товаров и результат​ одну и ту​/ (наклонная черта)​ число с отрицательным​ и -1, когда​
3. ​ вычисления.​ результату число.​Чтобы включить эту​ в Excel».​ строку поиска в​ в условном форматировании,​   . Если переместить лист 2​ на несколько листов​ например A1, основана​ книги либо на​ распространенными формулами. Благодаря​ результатом.​
4. ​ умножить на 100.​ же формулу на​Деление​ знаком минус:​ – отрицательное.​Совет:​При необходимости ссылаться на​ числовую клавиатуру, нужно​

​Символы, которые часто​ диалоговом окне «Найти»​ др. Существую​ или 6 в​

exceltable.com

​   . Трехмерные ссылки используются для​

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

1. Не добавляйте последние скобки в функции

Давайте начнем с чего-то по-настоящему простого! Когда вы пишите формулу, содержащую только одну функцию (СУММ, СРЗНАЧ и т.д.), вам не обязательно вводить заключительные закрывающие скобки. Например, вы можете просто ввести:

= СУММ(A1:A10

и нажмите Enter. Excel добавит закрывающие скобки за вас. Это хоть и мелочь, но удобная.

Примечание: это не сработает, если ваша формула содержит более одного набора скобок.

2. При перемещении формулы не допускайте изменения ссылок

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

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

Способ 1: Если вы просто перемещаете формулу в соседнее место, попробуйте перетаскивание. Перетаскивание сохранит все адреса в целости и сохранности.

Способ 2: Если вы перемещаете формулу в более отдаленное место, используйте вырезание и вставку. Когда вы вырезаете формулу, ее ссылки не изменяются.

Горячие клавиши:

Windows: Ctrl + X, Ctrl + V

Mac: Cmd + X, Cmd + V

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

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

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

Способ 2: Чтобы скопировать группу формул в новое место, не затрагивая ссылки, вы можете использовать поиск и замену.

1. Выберите формулы, которые вы хотите скопировать
2. Затем найдите и замените знак равенства (=) в формулах символом хеша (#). Это преобразует формулы в текст.
3. Теперь скопируйте и вставьте формулы в новое место.
4. После этого проделайте обратную операцию. Найдите хэш (#) и замените на знак равенства (=). Это восстановит формулы в рабочее состояние.

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

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

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

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

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

5. Используйте таблицу для автоматического ввода формул

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

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

Когда вы обновляете формулу в таблице, Excel снова обновляет все аналогичные формулы в том же столбце.

Примечание: формулы в таблице будут автоматически использовать структурированные ссылки (т.е. в приведенном выше примере = [@ Кол-во] * [@ Цена]

6. Используйте автозаполнение при вводе функций

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

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

7. Используйте Ctrl + клик для ввода аргументов

Не любите вводить точку с запятой между аргументами? Excel может сделать это за вас. Когда вы вводите аргументы в функцию, просто удерживайте нажатой клавишу «Ctrl» (Mac: Command) при нажатии на каждую ссылку, и Excel автоматически введет для вас разделители.

Например, вы можете ввести формулу: = СУММ(A1; B10; C5:C10), введя «= СУММ(», затем щелкнув по каждой ссылке, удерживая нажатой клавишу «Ctrl». Это работает с любой функцией, в которой можно использовать ссылки в качестве аргументов.

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

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

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

9. Вставьте заполнители аргументов функции с помощью горячих клавиш

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

Когда вы вводите функцию, после того, как Excel распознал имя функции, введите Ctrl + Shift + A (обе платформы).

Например, если вы наберете «=ДАТА (» и затем используете Ctrl + Shift + A, Excel выдаст вам «= DATE (год, месяц, день)». Затем вы можете дважды щелкнуть каждый аргумент (или воспользоваться подсказкой функции окно для выбора каждого аргумента) и измените его на значение, которое вы хотите.

10. Уберите мешающую подсказку с обзора

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

11. Включить отображение всех формул одновременно

Каждый раз, когда вы редактируете ячейку, содержащую формулу, Excel автоматически отображает формулу вместо ее результата. Но иногда нужно увидеть все формулы на рабочем листе одновременно. Для этого просто используйте сочетание клавиш для отображения формул: Ctrl + ~ (это тильда). С помощью этого трюка вы можете быстро включать и выключать отображение всех формул на листе. Это хороший способ проверить формулы на согласованность, увидев все формулы одновременно.

12. Выберите все формулы на рабочем листе одновременно

Еще один способ увидеть все формулы на рабочем листе — выбрать их. Вы можете сделать это, используя более мощные (и скрытые) функции в Excel: это окно Переход (Ctrl + G). С помощью этой команды вы можете выбрать все виды аргументов в Excel, включая пустые ячейки, ячейки с числами и многое другое. Один из вариантов — это ячейки, содержащие формулы.

Чтобы выбрать все ячейки, содержащие формулы на листе, просто нажмите Ctrl + G, чтобы открыть диалоговое окно «Переход», затем нажмите кнопку «Выделить» и выберите «Формулы». Когда вы нажмете ОК, будут выбраны все ячейки, которые содержат формулы.

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

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

Распространенной проблемой в Excel является необходимость остановить обновление рассчитанных значений. Например, вы хотите упростить рабочую таблицу, удалив вспомогательные столбцы, которые вы использовали для генерации определенных значений. Но если вы удалите эти столбцы с формулами, все еще ссылающимися на них, вы получите массу ошибок #ЗНАЧ. Решение состоит в том, чтобы сначала преобразовать формулы в значения, а затем удалить дополнительные столбцы. Самый простой способ сделать это — использовать Специальную вставку. Сначала выберите формулы, которые вы хотите преобразовать, и скопируйте в буфер обмена. Затем, когда формулы еще выбраны, откройте диалоговое окно «Специальная вставка» (Win: Ctrl + Alt + V, Mac: Ctrl + Cmd + V) и используйте параметр «Значения». Это заменит все формулы, которые вы выбрали, на значения, которые они рассчитали.

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

Другой распространенной проблемой в Excel является необходимость изменения большого количества значений одновременно. Например, возможно, у вас есть список из 500 цен на товары, и вам нужно увеличить все цены на 5%. Или, может быть, у вас есть список из 100 дат, которые нужно перенести на одну неделю? В таких случаях вы можете добавить столбец «Помощник» в таблицу, выполнить необходимые вычисления, преобразовать результаты в значения, а затем скопировать их в исходный столбец. Но если вам нужен только простой расчет, Специальная вставка поможет намного проще и быстрее, потому что вы можете изменить значение напрямую без каких-либо дополнительных формул.

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

• добавьте число 7 к любой ячейке на рабочем листе
• скопируйте его в буфер обмена
• выберите все даты, которые вы хотите изменить
• используйте Специальная вставка> Операция> Сложить
• нажмите ОК

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

Чтобы увеличить цены на 5%, используйте тот же подход. Введите «1.05» в ячейку и скопируйте в буфер обмена. Затем выберите цены, которые вы хотите изменить, и используйте Специальная вставка> Операция> Умножить, чтобы преобразовать все цены сразу. Как только вы освоите этот совет, вы найдете много способов его применения.

Примечание: этот способ работает только со значениями. Не пытайтесь делать это с формулами!

15. Используйте именованные диапазоны, чтобы сделать формулы более читабельными

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

= B3 * $A$1
= B4 * $A$1
= B5 * $A$1

Но если вы назовете ячейку A1 «почасовая_ставка», ваши формулы будут выглядеть так:

= B3 * почасовая_ставка
= B4 * почасовая_ставка
= B5 * почасовая_ставка

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

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

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

16. Применять имена к существующим формулам автоматически

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

Когда откроется окно «Применить имена», выберите имена, которые вы хотите применить, и нажмите «ОК». Excel заменит любые соответствующие ссылки на выбранные вами имена.

17. Сохраните формулу, которая еще не дописана

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

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

18. Будьте в курсе функций, которые предлагает Excel

Функции существуют для решения конкретных проблем. Вы можете думать о функции как о готовой формуле с определенным названием, целью и возвращаемым значением. Например, функция ПРОПНАЧ (PROPER) имеет только одну цель: она преобразует первую букву каждого слова в прописную. Дайте ей текст типа «ПОЕЗД чита — ЧЕЛябинск», и она вернет вам «Поезд Чита — Челябинск». Функции невероятно удобны, когда они решают вашу проблему, поэтому имеет смысл ознакомиться с функциями, доступными в Excel.

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

19. Используйте клавишу F4 для переключения относительных и абсолютных ссылок

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

Однако преобразование между относительными и абсолютными ссылками может создавать неудобства — ввод всех этих знаков доллара ($) утомителен и подвержен ошибкам. К счастью, есть отличная горячая клавиша, которая позволяет быстро переключаться между 4 вариантами, доступными для каждой ссылки: (Windows: F4, Mac: Command + T).

Просто поместите курсор в ссылку, используя клавишу. Каждый раз, когда вы нажимаете ее, Excel будет «переходить» к следующему варианту в следующем порядке: полностью относительный (A1)> полностью абсолютный ($A$1)> абсолютный ряд (A$1)> абсолютный столбец ($A1).

20. Помните, что формулы и функции возвращают значение. Всегда.

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

Примечание: Слово «возвращение» происходит из мира программирования, и иногда оно вводит в заблуждение людей, плохо знакомых с этой концепцией. В контексте формул Excel он используется следующим образом: «Функция ДЛСТР возвращает число» или «Формула в ячейке A1 возвращает ошибку». Всякий раз, когда вы слышите слово «возвращает» с формулой, просто думайте «результат».

21. Используйте F9 для оценки частей формулы

Клавиша F9 (Fn + F9 на Mac) может решать части формулы в режиме реального времени. Это фантастический инструмент для отладки больших формул, когда вам необходимо убедиться, что вы получите ожидаемый результат определенной части формулы.

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

Примечание: В Windows вы можете отменить F9, но на Mac — нет. Чтобы выйти из формулы без внесения изменений, просто используйте Esc.

22. Используйте функцию «Вычислить формулу»

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

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

Примечание: Эта функция доступна только в версии для Windows. Версия для Mac использует другой подход, называемый Formula Builder, который отображает результаты при создании формулы. Функциональность отличается, но этот подход также очень полезен.

23. Построить сложные формулы в небольших шагах

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

Например, вы хотите написать формулу, которая извлекает имя из полного имени. Вы знаете, что могли бы использовать функцию ЛЕВСИМВ (LEFT), чтобы вытянуть текст слева, но вы не знаете, как рассчитать количество символов для извлечения. Начните с ЛЕВСИМВ (полное-имя; 5), чтобы формула заработала. Затем подумайте, как заменить число 5 на вычисленное значение. В этом случае вы можете определить количество извлекаемых символов, используя функцию НАЙТИ, чтобы определить положение первого пробела.

24. Используйте именованные диапазоны как переменные

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

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

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

Когда вы создаете рабочую таблицу, основанную на определенных допущениях, может возникнуть проблема с четким пониманием сути таблицы. Часто на рабочем листе имеется определенная область для входных данных и другая область для выходных данных, и нет места для одновременного отображения обоих. Один из способов убедиться в том, что ключевые допущения понятны, — это встроить их названия непосредственно в заголовки, которые появляются на рабочем листе, используя конкатенацию, обычно с помощью функции ТЕКСТ.

Например, обычно у вас может быть заголовок «Стоимость кофе», за которым следует рассчитанная стоимость. С конкатенацией вы можете сделать так, чтобы было написано: «Стоимость кофе (150 рублей за чашку)».

26. Добавьте разрывы строк во вложенные ЕСЛИ (IF), чтобы их было легче читать

Когда вы создаете вложенную формулу ЕСЛИ (IF), отслеживание истинных и ложных аргументов в скобках может привести к путанице. Когда вы будете закрывать скобки, легко ошибиться в логике. Однако существует простой способ сделать формулу с несколькими операторами ЕСЛИ (IF) более читабельной: просто добавьте разрывы строк в формулу после каждого ИСТИННОГО аргумента. Это сделает формулу более похожей на таблицу.

27. Вводите функции с автозаполнением

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

28. Используйте Автосумму для ввода формул СУММ (SUM)

Этот способ подойдет не для всех случаев, но при использовании точно доставит удовольствие. Автосумма работает как для строк, так и для столбцов. Просто выберите пустую ячейку справа или под ячейками, которые вы хотите суммировать, и введите Alt + = (Mac: Command + Shift + T). Excel определит диапазон, который вы пытаетесь суммировать, и вставит функцию СУММ (SUM) за один шаг. Если вы хотите быть более конкретным, чтобы Excel не догадывался, сначала выберите диапазон, который вы хотите суммировать, включая ячейку, в которой вы хотите использовать функцию СУММ (SUM).

Автосумма даже вставит несколько функций СУММ (SUM) одновременно. Чтобы сложить несколько столбцов, выберите диапазон пустых ячеек под столбцами. Чтобы сложить несколько строк, выберите диапазон пустых ячеек в столбце справа от строк.

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

29. Введите одну и ту же формулу одновременно в несколько ячеек.

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

Вы также можете использовать эту же технику для редактирования нескольких формул одновременно. Просто выберите все формулы , внесите необходимые изменения и нажмите Ctrl + Enter.

Все формулы в Excel начинаются со знака равенства «=». Они оперируют с числами, текстом, названиями ячеек, функциями. Главные отличия от обычных математических формул – формула в Excel задается одной строкой, вместо переменных используются названия ячеек. Для вычислений применяются следующие операции:

• сложение – «+»;
• вычитание – «-»;
• умножение – «*»;
• деление – «/»;
• возведение в степень – «^».

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

=2+6/3

=B1-B3

=A1+1000

=(1+3)/(2+2)

Пример простейших вычислений:

Применение функций

Для автоматизации расчетов применяются разнообразные функции. Их можно вставлять нажатием кнопки «Вставить функцию». Альтернативный вариант – нажатие комбинации Shift+F3 (для ноутбуков Shift+Fn+F3). Появляется диалоговое окно, в котором надо выбрать категорию. Далее определяется конкретная функция, задаются ее аргументы, нажимается «ОК».

Вот пример пошагового вычисления квадратного корня числа. Вызвали диалоговое окно, выбрали раздел «Математические», далее «КОРЕНЬ»:

Задали аргумент (в данном случае это B1):

Нажали «ОК»:

Математические вычисления

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

=СУММ(B1:B3)

Здесь сложили B1, B2 и B3.

Также применяется суммирование отдельных ячеек:

=СУММ(B1;B3)

А здесь сложили B1 и B3.

Аналог СУММ – ПРОИЗВЕД, позволяет перемножать значения. Пример перемножения диапазона:

=ПРОИЗВЕД(B1:B3)

В категории «Математические» также представлены средства для тригонометрических вычислений, для работы с целыми числами, для округления дробей.

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

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

=ИСТИНА()

=ЛОЖЬ()

Можно инвертировать содержимое:

=НЕ(B1)

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

ЕСЛИ(логическое_выражение;значение1;значение2)

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

• равно – «=»;
• больше – «>»;
• меньше – «<»;
• больше или равно – «>=»;
• меньше или равно – «<=»;
• не равно – «<>».

Примеры операций сравнения: 2<3, B1<>B4, F5>=10.

Пример использования ЕСЛИ:

=ЕСЛИ(2>=1;10;20)

Обработка текста

В Excel имеются средства и для несложных операций с текстовыми величинами. ДЛСТР возвращает длину текстового аргумента, например, =ДЛСТР(«Волга впадает в Каспийское море») даст результат 31.

НАЙТИ осуществляет поиск одного текста в другом и возвращает номер позиции первого вхождения. Если ввести =НАЙТИ(«ас»;»Василий»;1), то получим 2.

ПОДСТАВИТЬ заменяет в тексте один фрагмент другим. =ПОДСТАВИТЬ(«Все нормально!»;»е»;»ё») даст результат «Всё нормально!». Если в качестве третьего аргумента указать пустую строку, то фрагмент будет просто удален из всего текста.

Чтобы объединить несколько строк в одну, можно использовать СЦЕПИТЬ. =СЦЕПИТЬ(«Добрый «;»день») создаст небольшую фразу из двух слов.

Дата и время

В Excel много удобных средств для обработки времени и дат. В приведенном ниже примере в A1 поместили текущую дату с помощью формулы =СЕГОДНЯ(), потом разбили ее на составные части. Для этого применили конструкции =ДЕНЬ(A1), =МЕСЯЦ(A1), =ГОД(A1). Результат:

Чтобы получить текущее время, наберите =ТДАТА(), затем измените формат ячейки правой кнопкой мыши («Формат ячеек…» -> «Число» -> «Время»), выберите удобное представление. Из текущего времени также можно выделить составные части (используя СЕКУНДЫ, МИНУТЫ, ЧАСЫ).

Формулы в Excel – одно из самых главных достоинств этого редактора. Благодаря им ваши возможности при работе с таблицами увеличиваются в несколько раз и ограничиваются только имеющимися знаниями. Вы сможете сделать всё что угодно. При этом Эксель будет помогать на каждом шагу – практически в любом окне существуют специальные подсказки.

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

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

1. Сделайте активной любую клетку. Кликните на строку ввода формул. Поставьте знак равенства.

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

так и ссылки на ячейки.

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

Из чего состоит формула

В качестве примера приведём следующее выражение.

Оно состоит из:

• символ «=» – с него начинается любая формула;
• функция «СУММ»;
• аргумента функции «A1:C1» (в данном случае это массив ячеек с «A1» по «C1»);
• оператора «+» (сложение);
• ссылки на ячейку «C1»;
• оператора «^» (возведение в степень);
• константы «2».

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

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

• скобки;
• экспоненты;
• умножение и деление (в зависимости от последовательности);
• сложение и вычитание (также в зависимости от последовательности).

Арифметические

К ним относятся:

• сложение – «+» (плюс);

[kod]=2+2[/kod]

• отрицание или вычитание – «-» (минус);

[kod]=2-2[/kod]

[kod]=-2[/kod]

Если перед числом поставить «минус», то оно примет отрицательное значение, но по модулю останется точно таким же.

• умножение – «*»;

[kod]=2*2[/kod]

• деление «/»;

[kod]=2/2[/kod]

• процент «%»;

[kod]=20%[/kod]

• возведение в степень – «^».

[kod]=2^2[/kod]

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

Данные операторы применяются для сравнения значений. В результате операции возвращается ИСТИНА или ЛОЖЬ. К ним относятся:

• знак «равенства» – «=»;

[kod]=C1=D1[/kod]

• знак «больше» – «>»;

[kod]=C1>D1[/kod]

• знак «меньше» — «<»;

[kod]=C1<D1[/kod]

• знак «больше или равно» — «>=»;

[kod]=C1>=D1[/kod]

• знак «меньше или равно» — «<=»;

[kod]=C1<=D1[/kod]

• знак «не равно» — «<>».

[kod]=C1<>D1[/kod]

Оператор объединения текста

Для этой цели используется специальный символ «&» (амперсанд). При помощи его можно соединить различные фрагменты в одно целое – тот же принцип, что и с функцией «СЦЕПИТЬ». Приведем несколько примеров:

1. Если вы хотите объединить текст в ячейках, то нужно использовать следующий код.

[kod]=A1&A2&A3[/kod]

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

[kod]=A1&»,»&A2&»,»&A3[/kod]

1. Объединять можно не только ячейки, но и обычные символы.

[kod]=»Авто»&»мобиль»[/kod]

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

Обратите внимание, что кавычки используют именно такие, как на скриншоте.

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

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

• для того чтобы создать простую ссылку на нужный диапазон ячеек, достаточно указать первую и последнюю клетку этой области, а между ними символ «:»;
• для объединения ссылок используется знак «;»;
• если необходимо определить клетки, которые находятся на пересечении нескольких диапазонов, то между ссылками ставится «пробел». В данном случае выведется значение клетки «C7».

Поскольку только она попадает под определение «пересечения множеств». Именно такое название носит данный оператор (пробел).

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

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

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

Простые ссылки A1

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

В таких ссылках буквы означают столбец, а цифра – строку. Максимально можно задать:

• столбцов – от A до XFD (не больше 16384);
• строк – от 1 до 1048576.

Приведем несколько примеров:

• ячейка на пересечении строки 5 и столбца B – «B5»;
• диапазон ячеек в столбце B начиная с 5 по 25 строку – «B5:B25»;
• диапазон ячеек в строке 5 начиная со столбца B до F – «B5:F5»;
• все ячейки в строке 10 – «10:10»;
• все ячейки в строках с 10 по 15 – «10:15»;
• все клетки в столбце B – «B:B»;
• все клетки в столбцах с B по K – «B:K»;
• диапазон ячеек с B2 по F5 – «B2-F5».

Каждый раз при написании ссылки вы будете видеть вот такое выделение.

Ссылки на другой лист

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

[kod]=СУММ(Лист2!A5:C5)[/kod]

На втором листе указаны следующие данные.

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

[kod]=СУММ(‘Лист номер 2’!A5:C5)[/kod]

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

Редактор Эксель работает с тремя видами ссылок:

• абсолютные;
• относительные;
• смешанные.

Рассмотрим их более внимательно.

Относительные ссылки

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

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

1. Введём формулу для расчета суммы первой колонки.

[kod]=СУММ(B4:B9)[/kod]

1. Нажмите на горячие клавиши [knopka]Ctrl[/knopka]+[knopka]C[/knopka]. Для того чтобы перенести формулу на соседнюю клетку, необходимо перейти туда и нажать на [knopka]Ctrl[/knopka]+[knopka]V[/knopka].

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

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

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

Если вы хотите, чтобы при переносе формул все ссылки сохранялись (то есть чтобы они не менялись в автоматическом режиме), нужно использовать абсолютные адреса. Они указываются в виде «$B$2».

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

[kod]=СУММ($B$4:$B$9)[/kod]

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

Смешанные ссылки

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

• $D1, $F5, $G3 – для фиксации столбцов;
• D$1, F$5, G$3 – для фиксации строк.

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

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

1. В качестве примера используем следующее выражение.

[kod]=B$4[/kod]

1. Перенесем эту формулу в другую ячейку. Желательно не на следующую и на другой строке. Теперь вы видим, что новое выражение содержит ту же строчку (4), но другую букву, поскольку только она была относительной.

Трёхмерные ссылки

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

[kod]=СУММ(Лист1:Лист4!A5)[/kod]

В данном случае результат будет соответствовать сумме всех ячеек «A5» на всех листах, начиная с 1 по 4. При составлении таких выражений необходимо придерживаться следующих условий:

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

Если нарушить эти правила, то вы увидите какую-нибудь ошибку.

Ссылки формата R1C1

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

• R10C10 – абсолютная ссылка на клетку, которая расположена на десятой строке десятого столбца;
• R – абсолютная ссылка на текущую (в которой указывается формула) ссылку;
• R[-2] – относительная ссылка на строчку, которая расположена на две позиции выше этой;
• R[-3]C – относительная ссылка на клетку, которая расположена на три позиции выше в текущем столбце (где вы решили прописать формулу);
• R[5]C[5] – относительная ссылка на клетку, которая распложена на пять клеток правее и пять строк ниже текущей.

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

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

Имена вы можете использовать для умножения, деления, сложения, вычитания, расчета процентов, коэффициентов, отклонения, округления, НДС, ипотеки, кредита, сметы, табелей, различных бланков, скидки, зарплаты, стажа, аннуитетного платежа, работы с формулами «ВПР», «ВСД», «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» и так далее. То есть можете делать, что угодно.

Главным условием можно назвать только одно – вы должны заранее определить это имя. Иначе Эксель о нём ничего знать не будет. Делается это следующим образом.

1. Выделите какой-нибудь столбец.
2. Вызовите контекстное меню.
3. Выберите пункт «Присвоить имя».

1. Укажите желаемое имя этого объекта. При этом нужно придерживаться следующих правил.

1. Для сохранения нажмите на кнопку «OK».

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

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

А если попробовать вместо адреса «D4:D9» вставить наше имя, то вы увидите подсказку. Достаточно написать несколько знаков, и вы увидите, что подходит (из базы имён) больше всего.

В нашем случае всё просто – «столбец_3». А представьте, что у вас таких имён будет большое множество. Все наизусть вы запомнить не сможете.

Использование функций

В редакторе Excel вставить функцию можно несколькими способами:

• вручную;
• при помощи панели инструментов;
• при помощи окна «Вставка функции».

Рассмотрим каждый метод более внимательно.

Ручной ввод

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

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

Панель инструментов

В этом случае необходимо:

1. Перейти на вкладку «Формулы».
2. Кликнуть на какую-нибудь библиотеку.
3. Выбрать нужную функцию.

1. Сразу после этого появится окно «Аргументы и функции» с уже выбранной функцией. Вам остается только проставить аргументы и сохранить формулу при помощи кнопки «OK».

Мастер подстановки

Применить его можно следующим образом:

1. Сделайте активной любую ячейку.
2. Нажмите на иконку «Fx» или выполните сочетание клавиш [knopka]SHIFT[/knopka]+[knopka]F3[/knopka].

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

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

1. Выберите какую-нибудь функцию из предложенного списка.
2. Чтобы продолжить, нужно кликнуть на кнопку «OK».

1. Затем вас попросят указать «Аргументы и функции». Сделать это можно вручную либо просто выделить нужный диапазон ячеек.
2. Для того чтобы применить все настройки, нужно нажать на кнопку «OK».

1. В результате этого мы увидим цифру 6, хотя это было и так понятно, поскольку в окне «Аргументы и функции» выводится предварительный результат. Данные пересчитываются моментально при изменении любого из аргументов.

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

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

Затем придерживайтесь следующей инструкции:

1. Кликните на первую ячейку. Вызовите окно «Вставка функции». Выберите функцию «Если». Для вставки нажмите на «OK».

1. Затем нужно будет составить какое-нибудь логическое выражение. Его необходимо записать в первое поле. Например, можно сложить значения трех ячеек в одной строке и проверить, будет ли сумма больше 10. В случае «истины» указываем текст «Больше 10». Для ложного результата – «Меньше 10». Затем для возврата в рабочее пространство нажимаем на «OK».

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

[kod]=ЕСЛИ(СУММ(B3:D3)>10;»Больше 10″;»Меньше 10″)[/kod]

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

1. В итоге редактор пересчитывает наше выражение для каждой строки.

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

Как редактировать формулу

Сделать это можно несколькими способами: использовать строку формул или специальный мастер. В первом случае всё просто – кликаете в специальное поле и вручную вводите нужные изменения. Но писать там не совсем удобно.

Единственное, что вы можете сделать, это увеличить поле для ввода. Для этого достаточно кликнуть на указанную иконку или нажать на сочетание клавиш [knopka]Ctrl[/knopka]+[knopka]Shift[/knopka]+[knopka]U[/knopka].

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

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

1. Сделайте активной клетку с формулой. Нажмите на иконку «Fx».

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

1. Для сохранения внесенных изменений нужно использовать кнопку «OK».

Как убрать формулу

Для того чтобы удалить какое-нибудь выражение, достаточно сделать следующее:

1. Кликните на любую ячейку.

1. Нажмите на кнопку [knopka]Delete[/knopka] или [knopka]Backspace[/knopka]. В результате этого клетка окажется пустой.

Добиться точно такого же результата можно и при помощи инструмента «Очистить всё».

Возможные ошибки при составлении формул в редакторе Excel

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

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

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

• не указываются обязательные аргументы функций. Обратите внимание на то, что необязательные аргументы указываются в квадратных скобках. Всё что без них – необходимо для полноценной работы формулы;

• неправильно указываются диапазоны ячеек. Для этого необходимо использовать оператор «:» (двоеточие).

Коды ошибок при работе с формулами

При работе с формулой вы можете увидеть следующие варианты ошибок:

• #ЗНАЧ! – данная ошибка показывает, что вы используете неправильный тип данных. Например, вместо числового значения пытаетесь использовать текст. Разумеется, Эксель не сможет вычислить сумму между двумя фразами;
• #ИМЯ? – подобная ошибка означает, что вы допустили опечатку в написании названия функции. Или же пытаетесь ввести что-то несуществующее. Так делать нельзя. Кроме этого, проблема может быть и в другом. Если вы уверены в имени функции, то попробуйте посмотреть на формулу более внимательно. Возможно, вы забыли какую-нибудь скобку. Кроме этого, нужно учитывать, что текстовые фрагменты указываются в кавычках. Если ничего не помогает, попробуйте составить выражение заново;
• #ЧИСЛО! – отображение подобного сообщения означает, что у вас какая-то проблема с аргументами или с результатом выполнения формулы. Например, число получилось слишком огромным или наоборот – маленьким;
• #ДЕЛ/0!– данная ошибка означает, что вы пытаетесь написать выражение, в котором происходит деление на ноль. Excel не может отменить правила математики. Поэтому такие действия здесь также запрещены;
• #Н/Д! – редактор может показать это сообщение, если какое-нибудь значение недоступно. Например, если вы используете функции ПОИСК, ПОИСКА, ПОИСКПОЗ, и Excel не нашел искомый фрагмент. Или же данных вообще нет и формуле не с чем работать;
• Если вы пытаетесь что-то посчитать, и программа Excel пишет слово #ССЫЛКА!, значит, в аргументе функции используется неправильный диапазон ячеек;
• #ПУСТО! – эта ошибка появляется в том случае, если у вас используется несогласующаяся формула с пересекающимися диапазонами. Точнее – если в действительности подобные ячейки отсутствуют (которые оказываются на пересечении двух диапазонов). Довольно часто такая ошибка возникает случайно. Достаточно оставить один пробел в аргументе, и редактор воспримет его как специальный оператор (о нём мы рассказывали ранее).

При редактировании формулы (ячейки подсвечиваются) вы увидите, что они на самом деле не пересекаются.

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

Для того чтобы увидеть содержащееся там значение, достаточно изменить размер столбца.

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

1. Вызовите контекстное меню. Выберите пункт «Формат ячеек».

1. Укажите тип «Общий». Для продолжения используйте кнопку «OK».

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

Примеры использования формул

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

Арифметика

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

1. Создайте таблицу с какими-нибудь условными данными.

1. Для того чтобы высчитать сумму, введите следующую формулу. Если хотите прибавить только одно значение, можно использовать оператор сложения («+»).

[kod]=СУММ(B3:C3)[/kod]

1. Как ни странно, в редакторе Excel нельзя отнять при помощи функций. Для вычета используется обычный оператор «-». В этом случае код получится следующий.

[kod]=B3-C3[/kod]

1. Для того чтобы определить, сколько первое число составляет от второго в процентах, нужно использовать вот такую простую конструкцию. Если вы захотите вычесть несколько значений, то придется прописывать «минус» для каждой ячейки.

[kod]=B3/C3%[/kod]

Обратите внимание, что символ процента ставится в конце, а не в начале. Кроме этого, при работе с процентами не нужно дополнительно умножать на 100. Это происходит автоматически.

1. Для определения среднего значения используйте следующую формулу.

[kod]=СРЗНАЧ(B3:C3)[/kod]

1. В результате описанных выше выражений, вы увидите следующий итог.

Условия

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

1. Для этого увеличим нашу таблицу.

1. Например, сложим те ячейки, у которых значение больше трёх.

[kod]=СУММЕСЛИ(B3;»>3″;B3:C3)[/kod]

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

[kod]=СУММЕСЛИМН(B3:B9;B3:B9;»>2″;B3:B9;»<6″)[/kod]

[kod]=СУММЕСЛИМН(C3:C9;C3:C9;»>2″;C3:C9;»<6″)[/kod]

1. Также можно посчитать количество элементов, которые удовлетворяют какому-то условию. Например, пусть Эксель посчитает, сколько у нас чисел больше 3.

[kod]=СЧЁТЕСЛИ(B3:B9;»>3″)[/kod]

[kod]=СЧЁТЕСЛИ(C3:C9;»>3″)[/kod]

1. Результат всех формул получится следующим.

Математические функции и графики

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

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

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

1. Для того чтобы преобразовать значение «X», нужно указать следующие формулы.

[kod]=EXP(B4)[/kod]

[kod]=B4+5*B4^3/2[/kod]

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

1. Выделяем всю таблицу. Переходим на вкладку «Вставка». Кликаем на инструмент «Рекомендуемые диаграммы».

1. Выбираем тип «Линия». Для продолжения кликаем на «OK».

1. Результат получился довольно-таки красивый и аккуратный.

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

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

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2016 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Заключение

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

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

Кроме этого, важно помнить, что формулы должны начинаться с символа «=» (равно). Многие начинающие пользователи забывают про это.

Файл примеров

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

Видеоинструкция

Если наше описание вам не помогло, попробуйте посмотреть приложенное ниже видео, в котором рассказываются основные моменты более детально. Возможно, вы делаете всё правильно, но что-то упускаете из виду. С помощью этого ролика вы должны разобраться со всеми проблемами. Надеемся, что подобные уроки вам помогли. Заглядывайте к нам чаще.