Как создать переменную в excel

Как создать таблицу данных с одной или двумя переменными в Excel

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

Как создать таблицу данных с одной или двумя переменными в Excel

Предположим, что у Вас есть книжный магазин и в нем есть 100 книг на продажу. Вы можете продать определенный % книг по высокой цене — $50 и определенный % книг по более низкой цене — $20. Если Вы продаете 60% книг по высокой цене, в ячейке D10 вычисляется общая выручка по форуме 60 * $50 + 40 * $20 = $3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа "что если" в Excel.

Таблица данных с одной переменной.

Что бы создать таблицу данных с одной переменной, выполните следующие действия:

1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).

2. Введите различные проценты в столбце А.

3. Выберите диапазон A12:B17.

Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.

Как создать таблицу данных с одной или двумя переменными в Excel

4. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.

5. Кликните в поле "Подставлять значения по строкам в: "и выберите ячейку C4.

Как создать таблицу данных с одной или двумя переменными в Excel

Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.

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

Как создать таблицу данных с одной или двумя переменными в Excel

Вывод: Если Вы продадите 60% книг по высокой цене, то Вы получите общую выручку в размере $3 800, если Вы продадите 70% по высокой цене, то получите $4 100 и так далее.

Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.

Таблица данных с двумя переменными.

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

1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).

2. Внесите различные варианты высокой цены в строку 12.

3. Введите различные проценты в столбце А.

4. Выберите диапазон A12:D17.

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

Как создать таблицу данных с одной или двумя переменными в Excel

5. На вкладке Данные, кликните на Анализ "что если" и выберите Таблица данных из списка.

6. Кликните в поле "Подставлять значения по столбцам в: " и выберите ячейку D7.

7. Кликните в поле "Подставлять значения по строкам в: " и выберите ячейку C4.

Мы выбрали ячейку D7, потому что высокая цена на книги задается именно в этой ячейке. Мы выбрали ячейку C4, потому что процент продаж по высокой цене задается именно в этой ячейке. Вместе с формулой в ячейке A12, Excel теперь знает, что он должен заменять значение ячейки D7 начиная с $50 и в ячейке С4 начиная с 60% для расчета общей выручки, до $70 и 100% соответсвенно.

Как создать таблицу данных с одной или двумя переменными в Excel

Как создать таблицу данных с одной или двумя переменными в Excel

Вывод: Если Вы продадите 60% книг по высокой цене в размере $50, то Вы получите общую выручку $3 800, если Вы продадите 80% по высокой цене в размере $60, то получите $5 200 и так далее.

Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.

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

Остались вопросы — задавайте их в комментариях ниже, также не забывайте подписываться на нас в социальных сетях.

Понятие переменных и констант в макросах Excel

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

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

Константы могут быть полезными, если необходимо одно и то же большое значение использовать несколько раз. Вместо копирования числа можно просто написать название константы. Например, вы можете использовать константу «Pi» для хранения числа Пи, которое является постоянным значением. Оно очень большое, и каждый раз писать его или искать и копировать довольно затруднительно. А так, достаточно написать два символа, и среда автоматически использует нужное число.

Пользователю Excel необходимо объявлять переменные, если ему надо время от времени менять значение, хранящееся в них. Например, можно задать переменную с названием sVAT_Rate, в которой будет храниться текущая ставка НДС на товар. Если она изменяется, можно быстро скорректировать ее. Особенно это полезно тем, кто ведет бизнес в Соединенных Штатах, где некоторые товары могут вообще не облагаться НДС (а также этот налог отличается в разных штатах).

Типы данных

Понятие переменных и констант в макросах Excel

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

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

Объявление констант и переменных

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

Чтобы объявить переменную, используется оператор Dim. Например, так:

Dim Variable_Name As Integer

Variable_Name – это название переменной. Далее пишется оператор As, указывающий на тип данных. Вместо строк «Variable_Name» и «Integer» можно вставить свое название и тип данных.

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

Const iMaxCount = 5000

Справедливости ради, в некоторых случаях можно обойтись и без объявления переменной, но в таком случае им автоматически будет назначен тип Variant. Тем не менее, делать этого не рекомендуется по следующим причинам:

  1. Variant обрабатывается значительно медленнее, и если таких переменных много, обработка информации может существенно замедлиться на слабых компьютерах. Казалось бы, что те секунды решат? Но если приходится писать большое количество строк кода, а потом еще и запускать его на слабых компьютерах (которые до сих пор продаются, учитывая то, что современные офисные пакеты требуют много оперативной памяти), можно полностью остановить работу. Известны случаи, как непродуманное написание макросов приводило к зависанию смартбуков, которые имеют небольшое количество оперативной памяти и не рассчитаны на выполнение сложных задач.
  2. Допускается наличие опечаток в названиях, предотвращение которых возможно с помощью оператора Option Explicit, позволяющего найти необъявленную переменную, если такая обнаруживается. Это простой способ определения ошибок, поскольку малейшая опечатка приводит к тому, что интерпретатор не может идентифицировать переменную. А если включить режим объявления переменных, интерпретатор просто не даст запустить макрос, если обнаруживаются контейнеры данных, которые не были объявлены в самом начале модуля.
  3. Избежание ошибок, вызванных тем, что значения переменной не соответствуют типу данных. В норме, если присвоить текстовое значение целочисленной переменной, будет выдана ошибка. Да, с одной стороны, без объявления присваивается универсальный тип, но если заранее их объявить, то можно избежать случайных ошибок.

Поэтому, несмотря на все, настоятельно рекомендуется объявлять все переменные в макросах Excel.

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

  1. Строки делаются пустыми.
  2. Числа обретают значение 0.
  3. Переменные с типом Boolean изначально считаются ложными.
  4. По умолчанию выставляется дата 30 декабря 1899 года.

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

Оператор Option Explicit

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

Если вам нужно включать этот оператор в код в каждом случае, это можно сделать с помощью специальной настройки VBA редактора. Чтобы включить эту опцию, необходимо:

  1. Перейти в среде разработки по пути – Tools > Options.
  2. В открывшемся после этого окне следует открыть вкладку Editor.
  3. И наконец, поставить флажок возле пункта Require Variable Declaration.

По завершению этих действий нажать на кнопку «ОК».

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

Область действия констант и переменных

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

Предположим, у нас есть функция Total_Cost() , и в ней используется переменная sVAT_Rate . В зависимости от позиции в модуле, она будет иметь разную область действия:

Dim sVAT_Rate As Single

Function Total_Cost() As Double

Function Total_Cost() As Double

Dim sVAT_Rate As Single

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

Задавать область действия констант можно аналогичным образом, но ключевое слово здесь прописывается вместе с оператором Const.

Вот таблица с наглядным примером, как это работает с константами и переменными.

Public sVAT_Rate As Single

Private sVAT_Rate As Single

Зачем нужны константы и переменные

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

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

Особенно это важно, когда макрос пишется несколькими программистами. Один может знать о том, что какая-то переменная не должна меняться. А другой – нет. Если же указывать оператор Const, иной разработчик будет знать, что данное значение не меняется.

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

Или же сам разработчик может забыть, какие переменные ему можно трогать, а какие – нет. Это часто случается, когда код пишется несколько недель, и его размер становится большим. За это время очень легко забыть даже то, что означает та или иная переменная.

Да, можно в этой ситуации обойтись комментариями, но не проще ли указать слово Const?

Выводы

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

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

Ссылка на основную публикацию