Excel как использовать переменную в формуле
Если вы еще не Excel в Интернете, скоро вы увидите, что это не просто сетка для ввода чисел в столбцах или строках. Да, с помощью Excel в Интернете можно найти итоги для столбца или строки чисел, но вы также можете вычислять платежи по ипотеке, решать математические или инженерные задачи или находить лучшие сценарии в зависимости от переменных чисел, которые вы подключали.
Excel в Интернете делает это с помощью формул в ячейках. Формула выполняет вычисления или другие действия с данными на листе. Формула всегда начинается со знака равенства (=), за которым могут следовать числа, математические операторы (например, знак "плюс" или "минус") и функции, которые значительно расширяют возможности формулы.
Ниже приведен пример формулы, умножающей 2 на 3 и прибавляющей к результату 5, чтобы получить 11.
Следующая формула использует функцию ПЛТ для вычисления платежа по ипотеке (1 073,64 долларов США) с 5% ставкой (5% разделить на 12 месяцев равняется ежемесячному проценту) на период в 30 лет (360 месяцев) с займом на сумму 200 000 долларов:
Ниже приведены примеры формул, которые можно использовать на листах.
=A1+A2+A3 Вычисляет сумму значений в ячейках A1, A2 и A3.
=КОРЕНЬ(A1) Использует функцию КОРЕНЬ для возврата значения квадратного корня числа в ячейке A1.
=СЕГОДНЯ() Возвращает текущую дату.
=ПРОПИСН("привет") Преобразует текст "привет" в "ПРИВЕТ" с помощью функции ПРОПИСН.
=ЕСЛИ(A1>0) Анализирует ячейку A1 и проверяет, превышает ли значение в ней нуль.
Элементы формулы
Формула также может содержать один или несколько из таких элементов: функции, ссылки, операторы и константы.
1. Функции. Функция ПИ() возвращает значение числа Пи: 3,142.
2. Ссылки. A2 возвращает значение ячейки A2.
3. Константы. Числа или текстовые значения, введенные непосредственно в формулу, например 2.
4. Операторы. Оператор ^ ("крышка") применяется для возведения числа в степень, а оператор * ("звездочка") — для умножения.
Использование констант в формулах
Использование операторов в формулах
Операторы определяют операции, которые необходимо выполнить над элементами формулы. Вычисления выполняются в стандартном порядке (соответствующем основным правилам арифметики), однако его можно изменить с помощью скобок.
Типы операторов
Приложение Microsoft Excel поддерживает четыре типа операторов: арифметические, текстовые, операторы сравнения и операторы ссылок.
Арифметические операторы
Арифметические операторы служат для выполнения базовых арифметических операций, таких как сложение, вычитание, умножение, деление или объединение чисел. Результатом операций являются числа. Арифметические операторы приведены ниже.
Арифметический оператор
Возведение в степень
Операторы сравнения
Операторы сравнения используются для сравнения двух значений. Результатом сравнения является логическое значение: ИСТИНА либо ЛОЖЬ.
Оператор сравнения
>= (знак «больше или равно»)
Больше или равно
Меньше или равно
Текстовый оператор конкатенации
Амперсанд (&) используется для объединения (соединения) одной или нескольких текстовых строк в одну.
Текстовый оператор
Соединение или объединение последовательностей знаков в одну последовательность
Выражение «Северный»&«ветер» дает результат «Северный ветер».
Операторы ссылок
Для определения ссылок на диапазоны ячеек можно использовать операторы, указанные ниже.
Оператор ссылки
Оператор диапазона, который образует одну ссылку на все ячейки, находящиеся между первой и последней ячейками диапазона, включая эти ячейки.
; (точка с запятой)
Оператор объединения. Объединяет несколько ссылок в одну ссылку.
Оператор пересечения множеств, используется для ссылки на общие ячейки двух диапазонов.
Порядок выполнения Excel в Интернете формулах
В некоторых случаях порядок вычисления может повлиять на возвращаемое формулой значение, поэтому для получения нужных результатов важно понимать стандартный порядок вычислений и знать, как можно его изменить.
Порядок вычислений
Формулы вычисляют значения в определенном порядке. Формула всегда начинается со знака равно(=).Excel в Интернете интерпретирует знаки после знака равно как формулу. После знака равно вычисляются элементы (операнды), такие как константы или ссылки на ячейки. Они разделены операторами вычислений. Excel в Интернете вычисляет формулу слева направо в соответствии с определенным порядком для каждого оператора в формуле.
Приоритет операторов
Если в одной формуле несколько операторов, Excel в Интернете выполняет операции в том порядке, который показан в таблице ниже. Если формула содержит операторы с одинаковым приоритетом, например операторы деления и умножения, Excel в Интернете эти операторы оцениваются слева направо.
Возведение в степень
Умножение и деление
Сложение и вычитание
Объединение двух текстовых строк в одну
Использование круглых скобок
Чтобы изменить порядок вычисления формулы, заключите ее часть, которая должна быть выполнена первой, в скобки. Например, следующая формула дает результат 11, так как Excel в Интернете умножение выполняется перед с добавлением. В этой формуле число 2 умножается на 3, а затем к результату прибавляется число 5.
Если же изменить синтаксис с помощью скобок, Excel в Интернете сбавляет 5 и 2, а затем умножает результат на 3, чтобы получить 21.
В следующем примере скобки, в которые заключена первая часть формулы, принудительно Excel в Интернете сначала вычислить ячейки B4+25, а затем разделить результат на сумму значений в ячейках D5, E5 и F5.
Использование функций и вложенных функций в формулах
Функции — это заранее определенные формулы, которые выполняют вычисления по заданным величинам, называемым аргументами, и в указанном порядке. Эти функции позволяют выполнять как простые, так и сложные вычисления.
Синтаксис функций
Приведенный ниже пример функции ОКРУГЛ, округляющей число в ячейке A10, демонстрирует синтаксис функции.
1. Структура. Структура функции начинается со знака равно (=), за которым следуют имя функции, открывая скобка, аргументы функции, разделенные запятой, и закрывая скобка.
2. Имя функции. Чтобы отобразить список доступных функций, щелкните любую ячейку и нажмите клавиши SHIFT+F3.
4. Всплывающая подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, всплывающая подсказка появляется после ввода выражения =ОКРУГЛ(. Всплывающие подсказки отображаются только для встроенных функций.
Ввод функций
Диалоговое окно Вставить функцию упрощает ввод функций при создании формул, в которых они содержатся. При вводе функции в формулу в диалоговом окне Вставить функцию отображаются имя функции, все ее аргументы, описание функции и каждого из аргументов, текущий результат функции и всей формулы.
Чтобы упростить создание и редактирование формул и свести к минимуму количество опечаток и синтаксических ошибок, пользуйтесь автозавершением формул. После того как вы введите знак " ocpSection" role="region" aria-label="Вложенные функции">
Вложенные функции
В некоторых случаях может потребоваться использовать функцию в качестве одного из аргументов другой функции. Например, в приведенной ниже формуле для сравнения результата со значением 50 используется вложенная функция СРЗНАЧ.
1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.
Использование ссылок в формулах
Ссылка указывает на ячейку или диапазон ячеек на сайте и сообщает Excel в Интернете, где искать значения или данные, которые вы хотите использовать в формуле. С помощью ссылок в одной формуле можно использовать данные, которые находятся в разных частях листа, а также значение одной ячейки в нескольких формулах. Вы также можете задавать ссылки на ячейки разных листов одной книги либо на ячейки из других книг. Ссылки на ячейки других книг называются связями или внешними ссылками.
Стиль ссылок A1
Стиль ссылок по умолчанию По умолчанию в Excel в Интернете используется стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с числами (от 1 до 1 048 576). Эти буквы и номера называются заголовками строк и столбцов. Для ссылки на ячейку введите букву столбца, и затем — номер строки. Например, ссылка B2 указывает на ячейку, расположенную на пересечении столбца B и строки 2.
Ячейка или диапазон
Использование
Ячейка на пересечении столбца A и строки 10
Диапазон ячеек: столбец А, строки 10-20.
Диапазон ячеек: строка 15, столбцы B-E
Все ячейки в строке 5
Все ячейки в строках с 5 по 10
Все ячейки в столбце H
Все ячейки в столбцах с H по J
Диапазон ячеек: столбцы А-E, строки 10-20
1. Ссылка на лист «Маркетинг».
2. Ссылка на диапазон ячеек с B1 по B10 включительно.
3. Ссылка на лист, отделенная от ссылки на диапазон значений.
Различия между абсолютными, относительными и смешанными ссылками
Смешанные ссылки Смешанная ссылка имеет абсолютный столбец и относительную строку либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец принимает форму $A 1, $B 1 и так далее. Абсолютная ссылка на строку имеет форму A$1, B$1 и так далее. При изменении позиции ячейки, содержаной формулу, изменяется относительная ссылка, а абсолютная ссылка не изменяется. При копировании или заполнении формулы по строкам или вниз по столбцам относительная ссылка автоматически корректируется, а абсолютная ссылка не корректируется. Например, при копировании или заполнении смешанной ссылки из ячейки A2 в B3 она будет меняться с =A$1 на =B$1.
Стиль трехмерных ссылок
Удобный способ для ссылки на несколько листов Трехмерные ссылки используются для анализа данных из одной и той же ячейки или диапазона ячеек на нескольких листах одной книги. Трехмерная ссылка содержит ссылку на ячейку или диапазон, перед которой указываются имена листов. Excel в Интернете использует все таблицы, которые хранятся между начальным и конечним именами ссылки. Например, формула =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5 на всех листах в диапазоне от листа 2 до листа 13 включительно.
При помощи трехмерных ссылок можно создавать ссылки на ячейки на других листах, определять имена и создавать формулы с использованием следующих функций: СУММ, СРЗНАЧ, СРЗНАЧА, СЧЁТ, СЧЁТЗ, МАКС, МАКСА, МИН, МИНА, ПРОИЗВЕД, СТАНДОТКЛОН.Г, СТАНДОТКЛОН.В, СТАНДОТКЛОНА, СТАНДОТКЛОНПА, ДИСПР, ДИСП.В, ДИСПА и ДИСППА.
Трехмерные ссылки нельзя использовать в формулах массива.
Трехмерные ссылки нельзя использовать вместе с оператор пересечения (один пробел), а также в формулах с неявное пересечение.
Вставка или копирование Если вставить листы между листами 2 и 6, Excel в Интернете будет включать в расчет все значения из ячеек с A2 по A5 на добавленных листах.
Удалить Если удалить листы между листами 2 и 6, Excel в Интернете вы вычислите их значения.
Переместить Если переместить листы между листами 2 и 6 в место за пределами диапазона, на который имеется ссылка, Excel в Интернете удалит их значения из вычислений.
Перемещение конечного листа Если переместить лист 2 или 6 в другое место книги, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов.
Удаление конечного листа Если удалить лист 2 или 6, Excel в Интернете скорректирует сумму с учетом изменения диапазона листов между ними.
Стиль ссылок R1C1
Можно использовать такой стиль ссылок, при котором нумеруются и строки, и столбцы. Стиль ссылок R1C1 удобен для вычисления положения столбцов и строк в макросах. В стиле R1C1 Excel в Интернете указывает на расположение ячейки с помощью R, за которым следует номер строки, и C, за которым следует номер столбца.
относительная ссылка на ячейку, расположенную на две строки выше в том же столбце
Относительная ссылка на ячейку, расположенную на две строки ниже и на два столбца правее
Абсолютная ссылка на ячейку, расположенную во второй строке второго столбца
Относительная ссылка на строку, расположенную выше текущей ячейки
Абсолютная ссылка на текущую строку
При записи макроса Excel в Интернете некоторые команды с помощью стиля ссылок R1C1. Например, если записать команду (например, нажать кнопку "Автоумма"), чтобы вставить формулу, в которую добавляется диапазон ячеек, Excel в Интернете записи формулы со ссылками с помощью стиля R1C1, а не A1.
Использование имен в формулах
Можно создавать определенные имена для представления ячеек, диапазонов ячеек, формул, констант и Excel в Интернете таблиц. Имя — это значимое краткое обозначение, поясняющее предназначение ссылки на ячейку, константы, формулы или таблицы, так как понять их суть с первого взгляда бывает непросто. Ниже приведены примеры имен и показано, как их использование упрощает понимание формул.
Я ненавижу повторяющиеся функции, особенно в формулах Excel. Есть ли способ, которым я могу избежать чего-то вроде:
=IF (VLOOKUP(A1, B:B, 1, 0) > 10, VLOOKUP(A1, B:B, 1, 0) - 10, VLOOKUP(A1, B: B, 1, 0) )
[выше приведен простой пример проблемы, а не конкретная формула, с которой я работаю.]
вы можете определить имя для части VLOOKUP формулы.
Шаг (1) важен здесь: я думаю, во второй строке вы хотите использовать Excel VLOOKUP(A2,B:B, 1, 0) третья строка VLOOKUP(A3,B:B, 1, 0) , etc. Шаг (4) достигает этого, используя относительные ссылки ( A1 и B:B ), а не абсолютные ссылки ( $A и $B:$B ).
вы можете хранить промежуточные значения в ячейке или столбце (которые вы можете скрыть, если выберете)
не связанные с переменными, ваш пример также будет решен с помощью MOD :
- VLOOKUP функция в собственной ячейке: =VLOOKUP(A1, B:B, 1, 0) (В С1), то формула ссылается С1: =IF( C1 > 10, C1 - 10, C1 )
- создать UDF:
определение имени, содержащего поиск, является аккуратным решением, однако всегда кажется, что имя листа хранится со ссылкой на ячейку. Тем не менее, я думаю, если вы удалите имя листа в " кавычки, но оставьте "!"это может сработать.
нет способа определить переменную в строке формул в Excel. В качестве обходного пути вы можете поместить функцию в другую ячейку (необязательно, скрывая содержимое или помещая его в отдельный лист). В противном случае вы можете создать функция VBA.
да. Но не напрямую.
- вы можете опубликовать Vlookup () в одной ячейке и использовать его адрес там, где это необходимо. - Это, пожалуй, единственный прямой способ использования переменных в Excel.
- вы можете определить Vlookup (reference)-10 как функцию-оболочку из макросов VBE. Нажмите Alt+f12 и используйте эту функцию
Я знаю, что это немного не по теме, но следуя решению, представленному Йонасом Бемером, на самом деле я думаю, что MOD-лучшее решение для вашего примера.
Если вы намеревались ограничить результат одной цифрой, MOD-лучший подход для его достижения.
ie. Предположим, что VLOOKUP (A1, B:B, 1, 0) возвращает 23. Ваша формула IF просто сделает этот расчет: 23-10 и вернет 13 в результате.
с другой стороны, MOD (VLOOKUP(A1, B: B, 1, 0), 10) разделит 23 на 10 и покажет остаток: 3.
вернемся к основной теме, Когда мне нужно использовать формулу, которая повторяет какую-то часть, я обычно помещаю ее в другую ячейку, а затем скрываю ее, как уже предлагали некоторые люди.
Доброго дня, друзья. Давайте разберем сегодня основное, я бы сказал, кармическое предназначение Excel. А именно, как же начать (или, с чего начать) эффективно работать в Excel и что такое формула в Excel?
Никто не спорит, что работая в Excel, как нигде больше, можно размещать данные в таблицах и выводить их красиво на печать. Но выло бы большим, читай — смертным, грехом использовать его только для этого. Это все равно как купить калькулятор и использовать его как табло для цифр. Так вот, Excel — это, прежде всего, мощный вычислительный комплекс, позволяющий обрабатывать и анализировать внесенные в таблицы данные. Давайте рассмотрим, как, работая в Excel, заставить его выполнять вычисления.
Как вы уже, наверняка, знаете (или догадываетесь ? ), в ячейки Excel можно поместить такие данные: числа, текст, даты и формулы.
Как же Excel отличает формулы от других данных? Очень просто, по знаку равенства. Представьте, что ячейка, это некая переменная, отображающая результат вычисления. Следовательно, если приравнять ее к какому-то выражению (например, записать в ней =3+2), то результат в виде числа 5 отобразится в ячейке. А если знак равенства убрать, то в ячейке будет видно надпись 3+2. Попробуйте.
Адрес ячейки Excel
Как я уже отметил, ячейку удобно представлять как переменную (как X или Y в математике). Стало быть, записывая в ячейку данные, мы присваиваем их значение ячейке-переменной. Каждая ячейка имеет в таблице свой определенный адрес, который определяется как в шахматах по пересечению столбца и строки. Так, ячейка, расположенная на пересечении столбца В и пятой строки имеет адрес (он же ее имя) В5. Фактически это как бы переменная В5.
Это наиболее привычный нам формат адреса ячеек Excel. Но можно встретить и RC-формат, который устанавливается в настройках табличного редактора. RC расшифровывается как Row (строка) и Column (столбец). Ниже на рисунке показано, где в параметрах устанавливается (или отключается) переключение форматов ссылок. Таким образом, для той же ячейки В5 адрес в этом формате будет выглядеть как R5C2 (5-я строка и 2-й столбец). Так, с адресацией, надеюсь, разобрались.
Заставляем Excel считать
Как уже отмечалось, чтобы отобразить в ячейке расчетные данные, нужно записать в нее знак равенства и математическое выражение. Но, не будем же мы каждый раз переписывать наше выражение, изменяя в нем цифры. Гораздо удобней иметь готовую формулу, которая пересчитывает результат, как только в ней изменяются значения. То есть, чтобы записать формулу, суммирующую два числа, в ней можно указать адреса ячеек, в которые эти числа будут подставляться.
Например, поместим в ячейки A1 и В1 числа 5 и 12 соответственно, а в ячейку С1 запишем формулу суммирования =A1+В1. Как только формула будет введена, в ячейке С1 отобразится результат — число 17. Все просто! Если поменять в ячейках A1 и В1 числа на другие, то сумма в ячейке С1 будет автоматически пересчитана.
Нужно отметить, что в формулах Excel могут использоваться различные арифметические операторы: умножения (*), деления (/), сложения (+) и вычитания (-). Если отсутствуют скобки, то их приоритет стандартный, как в математике. Тут они перечислены в порядке убывания приоритета: умножение и деление имеют более высокий приоритет, чем сложение и вычитание. Операторы с одинаковым приоритетом выполняются слева направо. Но, чтобы работая в Excel не ошибиться, можно использовать скобки, тем более, что скобки облегчают чтение и анализ формул.
Относительные ссылки Excel
Выше были рассмотрены относительные ссылки, которые указывают на некоторые ячейки: А1, В1 и др. Их называют относительными, так как при копировании формулы в другую ячейку, содержащиеся в ней формулы изменяются на величину перемещения.
Например, если взять ячейку С1, содержащую формулу =A1+В1, и скопировать в ячейку С2, то формула в последней будет =A2+В2. Если копировать по строкам дальше, то формула будет соответственно изменяться, указывая на ячейки той строки, куда выполняется копирование. Это очень удобно для расчета данных таблицы построчно.
Допустим, есть небольшая табличка с какими-то данными. Достаточно создать формулу в итоговом столбце для 1-й строки и скопировать ее на другие строки протаскиванием. Результаты появятся в итоговом столбце для каждой строки. При этом в адресах ячеек изменятся номера строк на величину перемещения формулы.
Этот прием можно проводить и для копирования в другие столбцы, адреса ячеек в формуле изменят имена столбцов на величину смещения формулы относительно исходной (копируемой).
Абсолютные ссылки Excel
Абсолютные ссылки нужны, если мы хотим указать на ячейку, местоположение которой не должно изменяться при копировании формулы. Абсолютные ссылки ячеек записываются как $A$1, A$1 или $A1. При этом знак доллара ($) указывает на то, какая часть ссылки зафиксирована. Доллар перед именем столбца фиксирует столбец (при копировании по горизонтали имя столбца меняться не будет), а доллар перед номером строки фиксирует строку (при копировании по столбцу номер строки меняться не будет). Если же знак доллара указан и перед столбцом, и перед строкой, то ссылка останется неизменной при любом копировании.
Для быстрого изменения абсолютной ссылки на относительную используется клавиша F4. Для смена типа достаточно установить текстовый курсор на какую-нибудь ссылку в созданной формуле и нажать несколько раз клавишу F4. После каждого нажатия этой клавиши тип ссылки будет циклично меняться. Например, если установить курсор на ссылку A2 в формуле =A2+В2 и нажимать F4, то она поочередно примет вид: $A$2, A$2, $A2 и опять A2. Достаточно удобно и лучше, чем вручную забивать туда знак доллара.
Чтобы лучше разобраться в различиях ссылок, давайте рассмотрим такой пример: допустим, надо получить в столбце С сумму чисел, указанных в столбцах А и В, умноженную на некоторый коэффициент, размещенный в ячейке D1 и равный 0,5.
Для выполнения этого задания следует в ячейке с адресом С1 разместить формулу, которая будет вычислять данные. Это удобно выполнить, вводя знаки действия с клавиатуры, а адреса ячеек удобно вводить щелчком мыши на нужных ячейках (при этом их адреса будут заноситься в формулу).
После этого устанавливаем текстовый курсор на адрес ячейки D1 и нажимая на клавишу F4 делаем ссылку абсолютной — $D$1. После этого формула примет такой вид:
Теперь содержимое ячейки можно копировать на весь столбец таблицы с данными. При этом ссылки A1 и B1 будут изменяться на величину смещения, а ссылка $D$1 останется неизменной, благодаря чему значение коэффициента будет браться только из этой ячейки.
Обратите внимание, что в ячейке и в строке формул ссылки на ячейки подсвечены разными цветами. Этими же цветами отмечены ячейки, на которые они указывают.
Ссылки на другие листы книги Excel
Следует отметить, что можно также ссылаться и на другие листы текущей книги Excel (вашего файла) точно так же, как и на ячейки текущего листа. Честно говоря, можно ссылаться и на листы других книг (при этом ссылка будет называться внешней ссылкой), но об этом в другой статье.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А15 листа 2, надо проделать следующие действия:
- установить курсор на ячейку А1 и ввести знак равенства;
- выполнить щелчок на ярлыке листа «Лист 2» (выполнится переход на этот лист);
- щелкнуть на ячейке А15 и нажать клавишу Enter;
после этого опять будет выполнен переход на Лист 1 в ячейку А15, где появится формула Excel. =Лист2!А15.
При работе в Excel редактирование формулы выполняется так же, как и редактирование текстовой строки, записанной в ячейку. Т.е. надо курсор Excel (белый крестик) установить на ячейку с формулой и выполнить двойной щелчок мышью или нажать клавишу F2. При этом в формуле появится текстовый курсор и выполняется редактирование.
Деление является одним из четырех самых распространенных арифметических действий. Редко встречаются комплексные расчеты, которые могут обойтись без него. Программа Excel имеет широкий функционал по использованию данного арифметического действия. Давайте выясним, какими способами можно выполнить деление в Экселе.
Выполнение деления
В Microsoft Excel деление можно произвести как при помощи формул, так и используя функции. Делимым и делителем при этом выступают числа и адреса ячеек.
Способ 1: деление числа на число
Лист Эксель можно использовать как своеобразный калькулятор, просто деля одно число на другое. Знаком деления выступает слеш (обратная черта) — «/».
-
Становимся в любую свободную ячейку листа или в строку формул. Ставим знак «равно»(=). Набираем с клавиатуры делимое число. Ставим знак деления (/). Набираем с клавиатуры делитель. В некоторых случаях делителей бывает больше одного. Тогда, перед каждым делителем ставим слеш (/).
После этого Эксель рассчитает формулу и в указанную ячейку выведет результат вычислений.
Если вычисление производится с несколькими знаками, то очередность их выполнения производится программой согласно законам математики. То есть, прежде всего, выполняется деление и умножение, а уже потом — сложение и вычитание.
Урок: Работа с формулами в Excel
Способ 2: деление содержимого ячеек
Также в Excel можно делить данные, находящиеся в ячейках.
-
Выделяем в ячейку, в которую будет выводиться результат вычисления. Ставим в ней знак «=». Далее кликаем по месту, в котором расположено делимое. За этим её адрес появляется в строке формул после знака «равно». Далее с клавиатуры устанавливаем знак «/». Кликаем по ячейке, в которой размещен делитель. Если делителей несколько, так же как и в предыдущем способе, указываем их все, а перед их адресами ставим знак деления.
Можно также комбинировать, в качестве делимого или делителя используя одновременно и адреса ячеек и статические числа.
Способ 3: деление столбца на столбец
Для расчета в таблицах часто требуется значения одного столбца разделить на данные второй колонки. Конечно, можно делить значение каждой ячейки тем способом, который указан выше, но можно эту процедуру сделать гораздо быстрее.
-
Выделяем первую ячейку в столбце, где должен выводиться результат. Ставим знак «=». Кликаем по ячейке делимого. Набираем знак «/». Кликаем по ячейке делителя.
Как видим, после этого действия будет полностью выполнена процедура деления одного столбца на второй, а результат выведен в отдельной колонке. Дело в том, что посредством маркера заполнения производится копирование формулы в нижние ячейки. Но, с учетом того, что по умолчанию все ссылки относительные, а не абсолютные, то в формуле по мере перемещения вниз происходит изменение адресов ячеек относительно первоначальных координат. А именно это нам и нужно для конкретного случая.
Урок: Как сделать автозаполнение в Excel
Способ 4: деление столбца на константу
Бывают случаи, когда нужно разделить столбец на одно и то же постоянное число — константу, и вывести сумму деления в отдельную колонку.
-
Ставим знак «равно» в первой ячейке итоговой колонки. Кликаем по делимой ячейке данной строки. Ставим знак деления. Затем вручную с клавиатуры проставляем нужное число.
Как видим, на этот раз деление тоже выполнено корректно. В этом случае при копировании данных маркером заполнения ссылки опять оставались относительными. Адрес делимого для каждой строки автоматически изменялся. А вот делитель является в данном случае постоянным числом, а значит, свойство относительности на него не распространяется. Таким образом, мы разделили содержимое ячеек столбца на константу.
Способ 5: деление столбца на ячейку
Но, что делать, если нужно разделить столбец на содержимое одной ячейки. Ведь по принципу относительности ссылок координаты делимого и делителя будут смещаться. Нам же нужно сделать адрес ячейки с делителем фиксированным.
-
Устанавливаем курсор в самую верхнюю ячейку столбца для вывода результата. Ставим знак «=». Кликаем по месту размещения делимого, в которой находится переменное значение. Ставим слеш (/). Кликаем по ячейке, в которой размещен постоянный делитель.
После этого результат по всему столбцу готов. Как видим, в данном случае произошло деление колонки на ячейку с фиксированным адресом.
Урок: Абсолютные и относительные ссылки в Excel
Способ 6: функция ЧАСТНОЕ
Деление в Экселе можно также выполнить при помощи специальной функции, которая называется ЧАСТНОЕ. Особенность этой функции состоит в том, что она делит, но без остатка. То есть, при использовании данного способа деления итогом всегда будет целое число. При этом, округление производится не по общепринятым математическим правилам к ближайшему целому, а к меньшему по модулю. То есть, число 5,8 функция округлит не до 6, а до 5.
Посмотрим применение данной функции на примере.
-
Кликаем по ячейке, куда будет выводиться результат расчета. Жмем на кнопку «Вставить функцию» слева от строки формул.
После этих действий функция ЧАСТНОЕ производит обработку данных и выдает ответ в ячейку, которая была указана в первом шаге данного способа деления.
Эту функцию можно также ввести вручную без использования Мастера. Её синтаксис выглядит следующим образом:
Урок: Мастер функций в Excel
Как видим, основным способом деления в программе Microsoft Office является использование формул. Символом деления в них является слеш — «/». В то же время, для определенных целей можно использовать в процессе деления функцию ЧАСТНОЕ. Но, нужно учесть, что при расчете таким способом разность получается без остатка, целым числом. При этом округление производится не по общепринятым нормам, а к меньшему по модулю целому числу.
В этой статье описано, как использовать переменные Microsoft Excel под процедурах в Microsoft Visual Basic для приложений.
Дополнительные сведения
Переменные в под процедуре
Мощной функцией языков программирования является возможность хранить данные в переменной, чтобы содержимое переменной можно было использовать или менять позже. В этом документе обсуждается следующее использование переменных в Visual Basic:
Как объявляются переменные.
Процедуры и проекты, которые могут использовать переменную.
Время жизни переменной.
Объявление переменной в макросе
Самый простой способ объявить переменную в макросах — использовать отчет Dim. В следующей строке две переменные объявлялись как integers(integers).
Dim x As Integer, y As Integer
Если для переменных x и y заданы целые числа, вы указываете Visual Basic выделить достаточно памяти для переменной целого числа (по 2 на x и y) и что данные, хранимые в x или y, являются целым числом в период между -32768 и 32767.
ПРИМЕЧАНИЕ: Если вы объявляете несколько переменных с помощью одной выписки Dim, необходимо указать тип данных для каждой переменной.
Если не указать тип данных для каждой переменной, как в следующем коде Visual Basic, то переменная y будет настроена как переменная с типом данных типа integer. Переменная x будет иметь тип типа variant:
Dim x, y As Integer
Дополнительные сведения см. в приведенной ниже таблице Variant.
Чтобы выполнить проверку переменной, выполните следующие действия:
Сохраните и закроите все открытые книги, а затем откройте новую книгу.
Запустите редактор Visual Basic (нажмите ALT+F11).
В меню Вставка выберите пункт Модуль.
Введите следующий код:
Sub Variable_Test()
Dim x As Integer, y As Integer
x = 10
y = 100
MsgBox "значение x — " & x & _
Chr(13) & "значение y — " & y
End Sub
the value of y is 100
В макро Variable_Test изменить следующую строку:
Запустите Variable_Test макрос.
Вы получите ошибку во время запуска, так как "ошибка" не является integer, и вы пытаетесь назначить это строковую величину для переменной x.
Сводка по типам данных
Это распространенные типы данных переменных:
тип данных Variant
Если не указать тип данных при объявлении переменной или вообще не объявлять переменную, Visual Basic автоматически определяет тип данных переменной типа переменной. Ниже следующую информацию о преимуществах переменных, объявленных в качестве этого типа данных:
Переменные могут содержать строку, дату, время, boolean или числовую величину.
Переменные могут автоматически преобразовывать содержащиеся в них значения.
Недостатком является то, что для переменных требуется не менее 16байт памяти. 16 bytes of memory can be significant in large procedures or in complex modules.
Чтобы узнать, как это работает в макросе Variable_Test, выполните следующие действия:
Измените код макроса Variable_Test на:
Запустите Variable_Test макрос.
ПРИМЕЧАНИЕ: Вы также можете не использовать следующую строку, и макрос будет по-прежнему работать, так как переменные x и y будут рассматриваться как типы данных Variant:
Область действия переменной
Когда вы объявляете переменную, она может быть видна другими макросами в том же модуле, в других модулях или в других проектах. Такое доступность переменной в модулях называется областью действия. Область действия может быть трех типов: на уровне процедуры, на уровне частного модуля и на уровне общего модуля. Область действия зависит от того, как и где объявлять переменные или переменные.
Область на уровне процедуры
Переменная с областью на уровне процедуры не видна за пределами процедуры, в которой она объявлена. Если установить значение переменной, которая имеет область действия на уровне процедуры, ее содержимое не будет видно другими макросами.
Чтобы убедиться, что переменная с областью на уровне процедуры не видна за пределами процедуры, в которой она объявлена, выполните указанные ниже действия.
Вставка нового модуля в проект.
Введите в этот модуль оба макроса:
Sub Macro1() Dim x As Integer x = 10 MsgBox "x, as seen by Macro1 is " & x 'next line runs Macro2 Macro2 End Sub Macro2() MsgBox "x, as seen by Macro2 is " & x End Sub
x, as seen by Macro1 is 10
x, as seen by Macro2 is
Макрос2 не отображает значение переменной x, так как переменная x является локальной для параметра Macro1.
Объем личных и общедоступных модулей
Вы можете определить переменные в разделе объявлений модуля (в верхней части модуля, над всеми под процедурами) и настроить область действия переменной с помощью открытой выписки, затемной или закрытой выписки. Если перед переменной поместить открытое заявление, переменная будет доступна всем макросам во всех модулях проекта. Если перед переменной поместить перед переменной утверждение Dim или Private, переменная будет доступна только макросам в модуле, в котором она объявлена.
Чтобы увидеть разницу между общедоступным заявлением и заявлением Dim, выполните следующие действия:
Сохраните и закроите все открытые книги, а затем откройте новую книгу.
Запустите редактор Visual Basic редактора.
Вставка модуля в проект.
Вставка другого модуля в проект.
Введите следующий код в этот модуль:
Sub Macro2() x = x * 3
MsgBox x
End Sub
Запустите Macro_1a макрос в первом модуле.
Измените строку объявления в первом модуле из:
Public x As Integer
Dim x As Integer
Запустите Macro_1a макрос.
Измените строку объявления в первом модуле из:
Dim x As Integer
Private x As Integer
Запустите Macro_1a макрос.
ПРИМЕЧАНИЕ: Если вы хотите ограничить область действия переменной модулем, в котором она объявлена, используйте privatestatement вместо dim. Оба этих эффекта будут одинаковыми, но при использовании закрытой выписки область действия будет более четкой.
Срок жизни переменной
Время, в течение которого переменная сохраняет значение, называется сроком жизни. Значение переменной может изменяться в течение ее жизненного времени, но при этом сохраняется значение. Кроме того, если переменная теряет область действия, она теряет значение.
Инициализация значения переменной
При запуске макроса все переменные инициализируются со значением. Числовая переменная инициализирована до нуля, строка переменной длины — в нулевую строку (""), а строка фиксированной длины заполняется кодом ASCII 0. Переменные Variant инициализируются как пустые. Пустая переменная представлена нулем в числовом контексте и пустой строкой ("") в строковом контексте.
Переменные уровня процедуры
Если переменная объявляется в макрос с помощью макроса Dim, значение переменной сохраняется до тех пор, пока работает макрос. Если этот макрос вызывает другие макрос, значение переменной сохраняется (но не доступно другим макросам), пока работают и другие макросы.
Чтобы продемонстрировать работу переменных на уровне процедуры, выполните указанные ниже действия.
Вставка нового модуля в проект.
В этом модуле введите оба макроса:
Sub Macro1()
'set x as a procedure level variable
Dim x As Integer
MsgBox "инициализированное значение x — " & x
x = 10
MsgBox "x is " & x
'следующая строка запускает Макрос2
Макрос2
MsgBox "x is still " & x
End Sub
Sub Macro2()
MsgBox "x, as seen by Macro2 is " & x
End Sub
the initialized value of x is 0
x, as seen by Macro2 is
Запуск макроса1.
Статическое ключевое слово
Если переменная на уровне процедуры объявлена с помощью статического ключевого слова, переменная сохраняет значение до сброса проекта. Таким образом, если у вас есть статическая переменная, то при следующем вызове процедуры статическая переменная инициализирована до последнего значения.
Чтобы узнать, как работает статическое ключевое слово, выполните следующие действия:
Запустите Макрос1, и вы получите следующее
ПРИМЕЧАНИЕ: Если у вас есть переменная уровня модуля, ее время существования будет таким же, как если бы она была статической переменной на уровне процедуры.
Чтобы проверить срок жизни переменной уровня модуля, выполните указанные ниже действия.
the initialized value of x is 0
the initialized value of x is 10
Сброс проектов для сброса переменных
Если вы хотите сбросить значение статической переменной или переменной на уровне модуля, нажмите кнопку Сброс на
Стандартная панель инструментов или нажмите кнопку Сброс в меню Выполнить.
Читайте также: