Как в эксель правильно расставить скобки
Как узнать, является ли данная формула формулой массива? Что вообще она означает?
На этапе создания формула (или также функция) сама по себе не является ни формулой массива, ни обычной формулой. Это вы определяете, как Excel должен истолковать формулу, которую вы вводите. То, что формула является формулой массива – это не столько особенность самой формулы, а скорее способом, которым программа Excel введенную формулу "обрабатывает". Подтверждение формулы с помощью сочетания клавиш "Ctrl + Shitf + Enter" – это является для Excelя командой на выполнение (обработку данных) как массив вычислений. Тогда он используется в качестве аргумента функции и возвращает в качестве результата вычислений таблицу (массив данных).
Примеры формул массива и отличие от обычных формул в Excel
Некоторые функции Excelя по умолчанию в качестве аргумента принимают диапазон ячеек (массив) и в результате возвращают одно значение. Отличными примерами являются функции СУММ, СЧЕТЕСЛИ, СРЗНАЧ и т.д. Для этих функций не имеет никакого значения, вводите ли вы их как функции массива или нет. Они и так обрабатывают таблицы, и найдут выход (сработают правильно) из любой ситуации. Вот такие маленькие Excel-евские приспособленцы.
К счастью, существуют другие функции, которые работают совершенно иначе, т.е. в зависимости от вашего решения относительно их принадлежности к "функциям массива" (иногда они вообще не хотят работать). Прекрасным примером является функция ЕСЛИ.
Когда формула является формулой массива, а когда обычной?
Для начала определимся как выглядит обычный массив значений в Excel. Это значения, которые находятся внутри фигурных скобок и разделены между собой точкой с запятой. Например:
– это синтаксис массива значений в Excel. Он может быть использован в аргументах функций.
Диапазон ячеек A1:A4 – так же является массивом значений в Excel. Естественно так же используется в аргументах функций. Например сравним результаты вычислений двух формул: =СУММ(A1:A4) и =СУММ() – они идентичны:
Визуально формула массива находится так же внутри фигурных скобок, но они не должны быть введены вручную, а только лишь при помощи комбинации клавиш CTRL+SHIFT+Enter. Если ввести вручную фигурные скобки, то формула не будет выполнятся в массиве – это будет синтаксическая ошибка в Excel.
Формула массива (введенная с помощью сочитания CTRL+SHIFT+Enter) будет использоваться везде, где вы хотите, чтобы функция, которая обычно работает с отдельными значениями (ячейками), внезапно повела себя иначе и приняла в качестве аргумента и вернула в качестве результата массив значений (таблицу). Вернемся к уже упомянутой функции ЕСЛИ. В качестве аргумента она принимает логическое значение ИСТИНА или ЛОЖЬ. В классической форме:
Если значение в ячейке A1 больше нуля, в качестве аргумента функция получит значение ИСТИНА и в качестве результата вернет текстовую строку «больше». Однако, если бы вы хотели проверить несколько ячеек сразу и передать результат такой проверки другой функции, вы должны были бы использовать вышеуказанную формулу как формулу массива. Для этого при вводе нажмем сочитание клавиш CTRL+SHIFT+Enter, а не как обычно (просто Enter):
В качестве аргумента функция принимает целый диапазон $A$1:$A$4. В результате проверки каждой ячейки диапазона в памяти компьютера создается таблица значений в массиве. Схематически таблицу можно отобразить так:
А так выглядят эти значения в массиве:
Например, чтобы прочитать этот массив и получить второе значение (сделать выборку значений) воспользуемся функцией:
Тоже самое что и:
Затем создается другая таблица, значения которой зависят непосредственно от значений в первой таблице. Если элемент в первом массиве имеет значение ИСТИНА, во втором массиве он примет значение «больше». Если он имеет значение ЛОЖЬ, элемент во второй таблице примет значение «меньше». После этой операции первая таблица удаляется из памяти компьютера, и в конечном счете, функция возвращает массив . Схематически вторую таблицу можно отобразить так:
Так же ее можно прочитать функцией:
В примере с функцией ЕСЛИ была введена формула массива только в одну ячейку, поэтому в результате получили только одно значение, соответствующее первому значению в таблице. Однако достаточно ввести формулу массива в диапазон ячеек, чтобы увидеть все значения массива результатов. Для этого выделяем диапазон из нескольких ячеек, нажимаем клавишу F2 (или заново вводим формулу вручную) и жмем CTRL+SHIFT+Enter.
В примере (рисунок ниже) видно, что таблица результатов содержит ровно четыре элемента, о которых я упоминал выше.
Примеры как использовать формулу массива в Excel
Это все хорошо, но возникают некоторые вопросы: «Зачем же нужна формула массива?» или «Как или где использовать формулу в массиве?», «Чем она лучше обычной формулы?».
Разумеется, массив, возвращаемый функцией ЕСЛИ, может передаваться далее на «обработку» в качестве аргумента для другой функции.
Пример. Представим, что вы хотели бы найти сумму ячеек B7:B10, но только тех, которые имеют значение больше нуля. Конечно же, вы можете использовать функцию СУММЕСЛИ, однако в нашем примере мы хотим сделать это только с помощью формулы массива. Суммируя значения ячеек нашего диапазона, необходимо будет как-то избавиться от значения "-32". Функции СУММ необходимо передать массив, в котором содержатся только значения больше нуля. Везде там, где значение меньше нуля, мы заменяем его на ноль, что, конечно же, не повлияет на результат. Как вы уже знаете, временную таблицу с соответствующими значениями вы можете получить, используя функцию ЕСЛИ. В конечном итоге соответствующая формула будет выглядеть так:
0;$A$1:$A$4;0))' >
Вводим формулу и не забываем для подтверждения ввода нажать комбинацию клавиш CTRL+SHIFT+Enter. В результате проверки каждой ячейки диапазона $A$1:$A$4 (является ли значение больше нуля) в памяти компьютера создается массив . Затем создается очередная таблица. Если элемент в первом массиве имеет значение ИСТИНА, то во второй таблице будет отображаться значение из соответствующей ячейки. Если он имеет значение ЛОЖЬ, то элемент во второй таблице примет значение 0. После этой операции первая таблица удаляется из памяти компьютера, и в конечном итоге функция ЕСЛИ возвращает массив . Затем эта таблица передается в качестве аргумента функции =СУММ(), которая, согласно своему предназначению, возвращает сумму всех элементов в таблице. В нашем примере сумма равна 45. В завершении, посмотрите, что произойдет, если вы скажете Excelю обработать приведенную выше формулу не как формулу массива.
Как отличать формулу массива от обычной формулы
При нажатии клавиш CTRL+SHIFT+Enter для подтверждения ввода в строке формул будут отображены фигурные скобки по краям. Значит данная формула выполняется в массиве. Но что если еще на этапе создания неизвестно какой тип формул следует применять?
Правильное «распознавание», когда следует нажимать CTRL+SHIFT+Enter, а когда просто Enter полностью зависит от понимания того, как работают массивы в формулах. Когда вы это поймете, сможете сказать, что конкретную формулу следует вводить (подтверждать) сочетанием клавиш – CTRL+SHIFT+Enter.
Конечно же, не подтвержденная, а просто как формула также может возвращать КАКОЙ-ТО результат (в чем вы могли только что убедиться сами). Однако, если вы сможете прочитать формулу и понять механизм, то вы заметите, что такой результат является ОШИБОЧНЫМ. И поэтому для правильной работы формулы вам необходимо ее подтвердить "Ctr+Shift+Enter". Как и все, понимание и использование формул массива требует практики. Тем не менее, стоит некоторое время посвятить тому, чтобы во всем разобраться. Потому что формулы массива позволяют решить многие проблемы, которые на первый взгляд могут казаться неразрешимыми.
Примеры вычислений и анализа формул массива
Каким образом можно просматривать и проверять значения промежуточных результатов расчета, например, содержание массивов, созданных в памяти компьютера и используемых для выполнения последующих действий? Ничего сложного! Пример 1:
Перейдите на ячейку с формулой, а затем в строке формул выделите в первом аргументе функции ссылку на диапазон ячеек:
Нажмите клавишу F9 (или "Пересчет" в правом верхнем углу меню "Формулы"), и вы получите (в строке формулы) значения аргументов, которые используются для вычислений, как показано ниже:
- запись с использованием двоеточий означает, что мы имеем дело с элементами вертикального (столбикового) массива, элементы горизонтального (строкового) разделены стандартным символом - ";" (точкой с запятой).
Пример 2: Снова перейдите на ячейку с формулой массива, но на этот раз выделите первый аргумент функции целиком вместе со знаком сравнения «>» и значением критерия – «0»).
То есть, созданный в памяти компьютера массив:
Пример 3: Выделите ячейку формулой массива где в функцию СУММ вложена функция ЕСЛИ. Затем в строке формул выберите весь аргумент функции СУММ (вместе с функцией ЕСЛИ):
Нажмите клавишу F9 и вы получите массив итоговых результатов вычисления, которые используются для суммирования, как показано ниже:
То есть, созданный в памяти компьютера массив:
Пример 4: Просто перейдите на ячейку с формулой B1 и выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу»
После чего нажмите на кнопку «Вычислить»:
В результате ссылка на диапазон ячеек в аргументе вложенной функции ЕСЛИ разложилась на массив значений. Снова нажмите на кнопку «Вычислить»:
Мы получили массив значений теперь уже для функции СУММ. Такой же, как и в примере 3.
Часто неопытные пользователи Excel возмущаются, что формула не работает. В конце концов оказалось, как легко догадаться, формулу в массиве вводили как обычную (просто Enter). Речь идет не о недоразумении, которое произошло, а о том факте, что у этих пользователей возникает вопрос: как избегать таких ошибок? Поэтому важно сразу во всем разобраться, чтобы в дальнейшем больше не задавать таких вопросов.
Операторы определяют тип вычислений, которые должны выполняться с элементами формулы, такими как с добавление, вычитание, умножение или деление. В этой статье вы узнаете порядок, в котором операторы по умолчанию действуют с элементами вычислений. Вы также узнаете, как изменить этот порядок с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Для выполнения основных математических операций, таких как с добавление, вычитание или умножение (или объединение чисел) и получения числных результатов, используйте арифметические операторы из этой таблицы.
Арифметический оператор
Возведение в степень
С помощью операторов из приведенной ниже таблицы можно сравнить два значения. При сравнении двух значений с помощью этих операторов результатом является логическое значение ИСТИНА или ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
<= (знак «меньше или равно»)
Меньше или равно
С помощью амперсанд (&) можно объединить одну или несколько текстовых строк, чтобы получить один фрагмент текста.
Текстовый оператор
Объединяет или объединяет два значения, чтобы получить одно непрерывное текстовое значение.
Объединяйте диапазоны ячеек для вычислений с помощью этих операторов.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
Оператор объединения, объединяющий несколько ссылок в одну ссылку.
Оператор пересечения, который создает ссылку на ячейки, общие с двумя ссылками.
Используется в качестве части имени ошибки.
Используется для отображения недостаточного пространства для отрисовки. В большинстве случаев столбец можно расширить до тех пор, пока содержимое не отобразится надлежащим образом.
Оператор пролитого диапазона, который используется для ссылки на весь диапазон в формуле динамического массива.
Оператор ссылки, который используется для неявного пересечения в формуле.
Порядок выполнения действий в формулах Excel
В некоторых случаях порядок выполнения вычислений может повлиять на возвращаемую формулу, поэтому важно понимать порядок и порядок получения результатов.
Формулы вычисляют значения в определенном порядке. Формула в Excel всегда начинается со знака равно (=). Знак равно означает Excel что последующие символы образуют формулу. После этого знака можно вычислить ряд элементов (операндов), разделенных операторами вычислений. Excel вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Если в одной формуле используется несколько операторов, Microsoft Excel выполняет операции в порядке, указанном в приведенной ниже таблице. Если формула содержит операторы с одинаковым приоритетом — например, операторы деления и умножения, — они выполняются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула вычислит значение 11, так как Excel умножение перед с добавлением. Формула сначала умножает 2 на 3, а затем добавляет 5 к результату.
Если же изменить синтаксис с помощью скобок, Excel 5 и 2, а затем умножить результат на 3, чтобы получить 21.
В приведеном ниже примере скобки, которые заключены в первую часть формулы, принудительно Excel сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Просмотрите это видео о заказе операторов в Excel, чтобы узнать больше.
Преобразование Excel значений в формулах
При вводе формулы Excel типы значений для каждого оператора. Если ввести значение другого вида, Excel его можно преобразовать.
При использовании знака "плюс" (+) Excel числа в формуле. Хотя кавычками означает, что "1" и "2" являются текстовыми значениями, Excel преобразует текстовые значения в числа.
Если формула ожидает числа, Excel преобразует текст, если он имеет формат, который обычно принимается для числа.
Excel интерпретирует текст как дату в формате дд.мм.дд.yyy, преобразует даты в числовом формате, а затем вычисляет разницу между ними.
Excel не удается преобразовать текст в число, так как текст "8+1" невозможно преобразовать в число. Вместо "8+1" можно использовать "9" или "8"+"1" для преобразования текста в число и возврата результата 3.
Если текст является ожидаемым, Excel преобразует числа и логические значения, такие как ИСТИНА и ЛОЖЬ, в текст.
Дополнительные сведения
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Начинающие пользователи Office часто спрашивают, как составить формулу в Excel, и какие возможности доступны при работе с программой. Ниже приведем подробную инструкцию для чайников, позволяющую быстро разобраться с вопросом и научиться выполнять вычисления любой сложности.
Математические операторы
Одно из главных преимуществ Excel — проведение расчетов с помощью математических формул, делающих таблицы более удобными и гибкими.
Программа способна выполнять не только простые задачи, но и делать весьма трудные расчеты. Для начала рассмотрим стандартные действия:
- плюс (+) — сложение;
- минус (-) — вычитание;
- косая полоска (/) — деление;
- звезда (*) — умножение;
- символ крыши «домика» (^) — степень.
Для составления любой формулы Excel вначале необходимо поставить знак равно (=).
Для удобства в программе предусмотрена опция, позволяющая делать математические действия в привязке к номерам ячеек.
Суть в том, что пользователь использует конкретные адреса, после чего Excel выполняет расчеты.
Применение метода дает плюсы в виде уменьшения числа помарок и облегчения внесения изменений.
С учетом сказанного можно подвести итог, как правильно составить формулу. Для этого поставьте в нужную ячейку знак равно, а после этого укажите необходимые действия, к примеру, В1+В2 или А1*А2.
Thank you!
We will contact you soon.
Простые формулы
Как правило, пользователи Excel обходятся простыми математическими действиями со знаками вычитания, сложения и другими. С рассмотрения таких расчетов мы начнем инструкцию.
Правила создания формул
Любой пользователь Excel должен уметь составлять формулы разной сложности. Это позволяет задействовать весь потенциал программы и сделать простыми расчеты с большим числом действий.
Приведем пример простой формулы по сложению двух цифр. Алгоритм такой:
- Выделите ячейку, в которой должен быть итоговый результат (С3).
- Поставьте знак равно. Учтите, что он появляется не только в ячейке, но и в верхней строке.
- Укажите адрес секции, которая должна быть первой, к примеру, С1.
- Поставьте знак, который интересует в конкретной формуле, например, плюс (+).
- Введите адрес графы для второго числа, к примеру, С2.
- Проверьте, что у вас получилась надпись = С1+С2, жмите на Ввод.
Выше мы привели простое пояснение для начинающих, позволяющее понять общий принцип расчетов в Excel.
Удобство в том, что пользователь Excel может вносить правки в данные таблице, не корректируя при этом формулу.Если поменять в С1 или С2 значение, в С3 итоговый параметр также поменяется. Недостаток в том, что Эксель не проверяет формулы на ошибки, поэтому этот момент необходимо контролировать самому.
Выделение с помощью мышки
Для упрощения работы можно минимизировать применение клавиатуры и использовать для ввода формулы кнопку мышки. Сделайте следующие шаги:
- Выделите секцию в таблице, где будет находиться формула (В3).
- Поставьте знак равно (=).
- Жмите на ячейку, которая должна стоять первой в расчетах. Для текущего примера В1. Цифра будет обведена, а номер секции появится в итоговой графе.
- Укажите действие, которое необходимо сделать. Как вариант, жмите на умножение (*).
- Выделите ту ячейку, цифра в которой должна стоять второй в формуле (В2). Обратите внимание на то, что необходимый участок будет обведен пунктирной линией.
- Жмите на Ввод, чтобы система выполнила расчет
В дальнейшем можно копировать формулы в другие ячейки, чтобы не переносить их несколько раз.
Редактирование
Бывают ситуации, когда необходимо изменить уже сделанную формулу в Excel. Причина может быть любой — пересмотр алгоритма расчетов, ошибка или другие проблемы.
Для внесения правок сделайте следующее:
- Укажите секцию, где необходимо внести изменения. В нашем случае это С3.
- Перейдите в строку формулы вверху, чтобы внести коррективы. Как вариант, можно нажать прямо на ячейку, чтобы глянуть и внести изменения в формулу.
- Внесите новые данные и обратите внимание, какие ячейки при этом подсвечиваются программой. На этом этапе будьте внимательны, чтобы избежать ошибок.
- Жмите Ввод и подтвердите внесенные изменения. К примеру, если раньше формула имела вид С1+С2, ее можно поменять на С1-С2 или любой другой вариант. Главное, чтобы в указанных ячейках стояли цифры.
При желании проделанные действия можно отметить. Для этого жмите на Esc клавиатуры или выберите знак Отмена в строчке с формулой.
Для просмотра заданных арифметических действий можно сделать проще — жмите на комбинацию Ctrl+’.
Сложные формулы
Бывают ситуации, когда простым действием не обойдешься и необходимо задать сложную формулу.
Здесь необходимо учесть общие правила для математических действий. Они просты.
Умножение и деление выполняется в первую очередь. При этом операции в скобках имеют приоритет перед остальными действиями.Приведем вариант формулы для ячейки С4. В ней можно указать следующие данные (В2+В3)*0,3.
Это означает, что формула сначала суммирует два показателя, а полученное значение рассчитывается с процентами (30% записывается в виде 0,3).
Во время расчетов Excel придерживается заданного порядка. Сначала выполняются действия в скобках, а уже потом делается умножение.
Записывая сложную формулу, важно учитывать правила расчетов, ведь в ином случае результат будет ошибочным. Применение скобок — лучший способ задать правильный порядок вычислений.
Помните, что Excel делает расчет с учетом введенных данных и не предупреждает об ошибках. Проверяйте правильность ввода самостоятельно. Главное — корректно расставить скобки и задать приоритеты вычислений.
Функции программы
Одна из главных особенностей Excel — наличие специальных функций. По сути, это формула, делающая определенные расчеты с учетом заданных параметров. Они созданы для ускорения и упрощения вычислений разного уровня сложности.
Синтаксис
Для правильной работы Excel функция должна записываться в конкретной последовательности.
Сначала ставится знак равно (=). После него идет функция СУММ, а за ней диапазон ячеек (В1:В4).
В программе имеются опции, в которых вообще не указываются аргументы. Если написать СЕГОДНЯ (), приложение вернет день с учетом времени в ОС компьютера.
Основные функции
Чтобы выполнять действия с несколькими условиями и проводить более серьезные расчеты, разберитесь с базовыми функциями.
Кратко рассмотрим их названия и особенности:
- СУММ. С помощью опции можно вычислить сумму двух и больше чисел. К примеру, если записать в качестве адреса (А1:А6), программа просуммирует все цифры в секциях, начиная с А1 по А6. Если указать опцию в формате (А1; А6), расчет будет выполнен только применительно к двум указанным секциям.
- СЧЕТ. Задача формулы в том, чтобы рассчитать число ячеек с числовыми обозначениями в одном ряду. К примеру, для получения информации о числе ячеек с цифрами между В1 и В20, пропишите такую формулу Excel — = СЧЕТ (В1:В20).
- СЧЕТ3. В отличие от прошлой опции, здесь учитываются все секции с внесенными данными (не только с цифрами). Плюс в том, что СЧЕТ3 можно использовать для разных типов информации, в том числе указанной в буквенном отображении.
- ДЛСТР. Задача опции состоит в расчете числа знаков в секции. Но учтите, что система считает все действия, в том числе сделанные пробелы.
- СЖПРОБЕЛЫ. Цель опции в удалении лишних пробелов. Это полезно, когда информация переносится с других источников, где уже имеется много ненужных пробелов.
- ВПР. Используется, если нужно найти элементы в таблице или диапазоне по строкам.
- ЕСЛИ. Опция применяется, если расчет осуществляется с условием «ЕСЛИ» и большим объемом данных с различными сценариями. Применение функции позволяет сравнить значения. Если результат правдивый, программа выполняет какое-то еще действие.
- МАКС и МИН — определяют наибольший и наименьший параметр из перечня.
В Эксель применяются и другие функции, но они менее востребованы.
Правила использования
Для лучшего понимания рассмотрим, как правильно добавлять функцию в Excel. Используем параметр СРЗНАЧ.
Алгоритм действий такой:
- Жмите на ячейку, где необходимо установить формулу — В11.
- Пропишите знак равно =, а после укажите название нужной опции СРЗНАЧ.
- Укажите диапазон секций в круглых скобках (В3:В10).
- Кликните на Ввод.
После указания этих параметров программа суммирует данные в ячейках с В3 по В10, а после этого вычисляет их среднее значение.
Применение Автосуммы
Для удобства почти любую опцию можно вставить с помощью Автосуммы. Сделайте следующее:
- Выберите и жмите на секцию, в которую необходимо вбить формулу (С 11).
- В группе Редактирования в разделе Главная отыщите и нажмите на стрелку возле надписи Автосумма.
- Выберите нужную опцию в появившемся меню, например, Сумма.
- Программа автоматически выбирает диапазон ячеек для суммирования, но эти данные можно задать вручную путем внесения правок в формулу.
Как и в рассмотренных выше случаях, результат необходимо проверять во избежание ошибок.
Комбинированные формулы
Дополнительное удобство Excel состоит в возможности комбинирования нескольких формул для проведения более сложных расчетов.
Рассмотрим ситуацию, когда необходимо просуммировать три числа и умножить их на коэффициент 1,5 или 1,6 в зависимости от того, какое получилось число (больше или меньше 100).
В таком случае запись имеет следующий вид: =ЕСЛИ(СУММ(А2:С2)<100;СУММ(А2:С2)*1,5;СУММ(А2:С2)*1,6).
В приведенной выше формуле используется две опции — ЕСЛИ и СУММА. В первом случае учитывается три результата — условие, правильно или неправильно.
Здесь действуют такие условия:
- Эксель суммирует числа в ячейках с А2 по С2.
- Если полученное число меньше 100, тогда параметр умножается на 1,5.
- Если итоговая цифра превышает 100, в таком случае результат умножается на 1,6.
Комбинированные формулы Эксель пользуются спросом, когда необходимо сделать разные расчеты и использовать более сложные формулы.
Виды ссылок и их особенности
В программе Excel предусмотрены относительные и абсолютные ссылки, упрощающие жизнь пользователю и позволяющие выполнять сложные расчеты. Ниже рассмотрим, в чем особенности каждой ссылки.
Относительная
Если не вносить никаких изменений, по умолчанию все ссылки являются относительными.
К примеру, при копировании формулы =В2+С2 из второй строки в третью, запись поменяется на =В3+С3. Это полезно, когда необходимо скопировать вычисление одновременно для разных столбцов.
Алгоритм действий при использовании:
- Выберите и нажмите на ячейку (секцию), где планируется запись формулы (В3).
- Пропишите данные для расчета интересующего параметра — =С3*D3.
- Жмите на ввод. Программа делает расчеты, а итог показывается в выбранной секции.
- Найдите маркер автоматического заполнения в В3.
- Нажмите на него и, удерживая левую кнопку мышки, перемести маркер по нужным ячейкам, например, с В3 до В10.
- После отпускания кнопок формула копируется во все указанные секции. Одновременно с этим выполняются расчеты.
Еще раз убедитесь, что вычисления сделаны корректно.
Абсолютные ссылки
Инструмент применяется, когда необходимо работать с множеством ячеек. Суть в том, что при копировании расчета ссылка на указанную секцию сохраняется.
Для обозначения абсолютной ссылки в Excel применяется символ доллара ($). В зависимости от варианта расстановки меняется и указание пользователя.
Рассмотрим несколько вариантов:
- $А$3 — строка/столбец не меняется;
- А$3 — срока остается неизменной;
- $А3 — столбец не меняется.
Как правило, применяется первый вид отображения, а остальные почти не используются.
Для примера сделаем расчет для ставки налога, введенного в секцию D1. Так как параметр во всех случаях идентичен, внедрим формулу $D$1 в расчет.
- Укажите ячейку, где будет находиться формула, к примеру, C3.
- Пропишите вычисление в виде (А3*В3)* $D$1.
- Нажмите на кнопку Ввод.
- Найдите маркер автоматического заполнения в С3 (точка справа внизу).
- Нажмите на него, кликните на левую кнопку мышки и потяните маркер вниз, захватив диапазоны с С4:С14.
- Отпустите кнопку мышки, после чего формула копируется в ячейки со ссылкой. Во всех случаях делаются автоматические расчеты.
При двойном нажатии на ячейки можно проверить правильность введенных формул. Для всех секций абсолютная ячейка должна быть идентичной. При проверке убедитесь в наличии символа $. Если его упустить, это приведет к неправильным результатам.
Теперь в вашем распоряжении подробная инструкция, как составить формулу в Excel, какие существуют функции, и как их правильно применять для получения корректного результата. Следование приведенным рекомендациям позволяет быстро разобраться в расчетах и расширить возможности использования программы.
Чтобы объединить значения (текстовые, цифровые) из разных ячеек в одну, используется функция СЦЕПИТЬ. Ее аналог – & (амперсанд). Знак справляется с простейшими задачами. Но для объединения множества строк не подходит.
Синтаксис функции СЦЕПИТЬ
Функция входит в группу «текстовые». Синтаксис: (текст 1; текст 2;…). Можно включить до 255 текстовых строк. Первый аргумент – «текст 1» – является обязательным. Любые аргументы можно указать в виде текста, цифр или ссылок на ячейки.
Общие правила использования функции СЦЕПИТЬ:
Как пользоваться функцией СЦЕПИТЬ в Excel
Начнем с простейших примеров.
Данные для объединения:
Ставим курсор в ячейку Е2 и вызываем мастер функций (кнопка fx). В категории «Текстовые» находим функцию СЦЕПИТЬ. Аргументы – ссылки на ячейки А2, В2, С2 и пробелы:
Результат объединения значений в ячейках:
Такой же результат получим с помощью амперсанда:
Один из аргументов – текст. Формула: =СЦЕПИТЬ("слесарь"; " ";A2;" ";B2;" ";C2). Результат:
И с более сложными задачами справится функция СЦЕПИТЬ в Excel. Примеры:
- Соединим текст с датой. Данные находятся в отдельных ячейках. Для столбца «Дата» установлен формат «Дата». Если использовать только функцию СЦЕПИТЬ, дата отобразится в числовом формате. Поэтому добавим функцию ТЕКСТ для форматирования чисел. Формула: Второй аргумент функции ТЕКСТ – формат представления даты. Результат:
- Соединим сразу много ячеек в строке. Если писать формулу с функцией СЦЕПИТЬ, получится долго и малоэффективно. Используем маленькую хитрость. Вот наша строка со значениями в отдельных ячейках. В ячейку Н1 ставим знак «равно». Выделяем диапазон со значениями А1:F1. Вводим амперсанд & и пробел “ “. Жмем F9. В строке формул появится формула массива. Ставим курсор в строку формул после знака «равно». Пишем название функции. Фигурные скобки заменяем на круглые и жмем ВВОД. Получаем нужный результат.
- Соединим значения через косую черточку («-», «&» и т.п.). Формула будет выглядеть так: = СЦЕПИТЬ (ссылка на ячейку 1; «/»; ссылка на ячейку 2).
- Соединим несколько значений в столбце. Функция подходит для значений в строках. Описанный выше способ с формулой массива тоже не сработает.
Преобразуем вертикальный диапазон в горизонтальный с помощью функции ТРАНСП. А затем воспользуемся формулой массива для быстрого соединения значений. В ячейку В1 ставим знак «равно». Пишем ТРАНСП. Указываем диапазон А1:А6. Вводим & и пробел (« »). Жмем F9.
Теперь преобразуем формулу массива, добавив функцию и заменив скобки.
Обратная функция СЦЕПИТЬ в Excel
Стандартной функции, которая разделяла бы значения одной ячейки, нет. Для выполнения ряда подобных задач можно использовать другие функции:
- ЛЕВСИМВ (отображает заданную часть символов с начала строки);
- ПРАВСИМВ (отображает заданную часть символов с конца строки);
- ПСТР (отображает указанную часть символов, начиная с заданной позиции) и другие.
- Извлечем первое слово из строки, применив функцию ЛЕВСИМВ. Аргументы – «текст» (ссылка на строку с искомым значением), «количество знаков» (число символов, которые нужно извлечь).
- Извлечем последнее слово из строки с помощью функции ПРАВСИМВ. Синтаксис похожий (как в предыдущем примере). Только количество знаков считается с конца.
- Извлечем из записи в строке фамилию (второе слово) с помощью функции ПСТР. Аргументы – «текст» (ссылка на строку), «начальная позиция» (номер символа, с которого программа начнет считать число знаков); «количество знаков». В примере: Е2 – «текст», с 9 знака функция вернет 8 символов.
Для выполнения более сложных задач используются более сложные формулы, составляются пользовательские функции.
Читайте также: