Как задать переменную в excel - IT Справочник
Llscompany.ru

IT Справочник
20 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

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

Переменные и константы в VBA

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

Например, константа Pi хранит значение 3,14159265… Число “Пи” не будет изменяться в ходе выполнения программы, но все же хранить такое значение удобнее как константу.

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

Типы данных

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

Очевидно, что пользуясь приведённой выше таблицей и правильно выбирая тип данных, можно использовать память более экономно (например, выбрать тип данных Integer вместо Long или Single вместо Double). Однако, используя более компактные типы данных, нужно внимательно следить за тем, чтобы в коде не было попыток уместить в них не соразмерно большие значения.

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

Примечание переводчика: Говоря о переменных в VBA, стоит упомянуть ещё один очень важный момент. Если мы объявляем переменную, но не присваиваем ей какое-либо значение, то она инициализируется значением по умолчанию:
• текстовые строки – инициализируются пустыми строками;
• числа – значением 0;
• переменные типа Boolean – False;
• даты – 30 декабря 1899.

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

Dim Имя_Переменной As Тип_Данных

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

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

Объявлять переменные в Excel не обязательно. По умолчанию все введённые, но не объявленные переменные в Excel будут иметь тип Variant и смогут принять как числовое, так и текстовое значение.

Таким образом, программист в любой момент сможет использовать новую переменную (даже если она не была объявлена), и Excel будет рассматривать её как переменную типа Variant. Однако, есть несколько причин, почему так поступать не следует:

  1. Использование памяти и скорость вычислений. Если не объявлять переменную с указанием типа данных, то по умолчанию для неё будет установлен тип Variant. Этот тип данных использует больше памяти, чем другие типы данных.Казалось бы, несколько лишних байт на каждую переменную – не так уж много, но на практике в создаваемых программах могут быть тысячи переменных (особенно при работе с массивами). Поэтому излишняя память, используемая переменными типа Variant, по сравнению с переменными типа Integer или Single, может сложится в значительную сумму.К тому же, операции с переменными типа Variant выполняются гораздо медленнее, чем с переменными других типов, соответственно лишняя тысяча переменных типа Variant может значительно замедлить вычисления.
  2. Профилактика опечаток в именах переменных. Если все переменные объявляются, то можно использовать оператор VBA – Option Explicit (о нём расскажем далее), чтобы выявить все не объявленные переменные.Таким образом исключается появление в программе ошибки в результате не верно записанного имени переменной. Например, используя в коде переменную с именем sVAT_Rate, можно допустить опечатку и, присваивая значение этой переменной, записать: “VATRate = 0,175”. Ожидается, что с этого момента, переменная sVAT_Rate должна содержать значение 0,175 – но, конечно же, этого не происходит. Если же включен режим обязательного объявления всех используемых переменных, то компилятор VBA сразу же укажет на ошибку, так как не найдёт переменную VATRate среди объявленных.
  3. Выделение значений, не соответствующих объявленному типу переменной. Если объявить переменную определённого типа и попытаться присвоить ей данные другого типа, то появится ошибка, не исправив которую, можно получить сбой в работе программы.На первый взгляд, это может показаться хорошей причиной, чтобы не объявлять переменные, но на самом деле, чем раньше выяснится, что одна из переменных получила не те данные, которые должна была получить – тем лучше! Иначе, если программа продолжит работу, результаты могут оказаться неверными и неожиданными, а найти причину ошибок будет гораздо сложнее.Возможно также, что макрос будет “успешно” выполнен. В результате ошибка останется незамеченной и работа продолжится с неверными данными!

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

Option Explicit

Оператор Option Explicit заставляет объявлять все переменные, которые будут использованы в коде VBA, и при компиляции выделяет все не объявленные переменные как ошибки (прежде чем будет запущено выполнение кода). Применить этот оператор не сложно – просто запишите в самом верху файла VBA такую строку:

Если хотите всегда вставлять Option Explicit в начало каждого нового созданного модуля VBA, то это можно делать автоматически. Для этого необходимо включить параметр Require Variable Declaration в настройках редактора VBA.

Это делается так:

  • В меню редактора Visual Basic нажмите Tools >Options
  • В появившемся диалоговом окне откройте вкладку Editor
  • Отметьте галочкой параметр Require Variable Declaration и нажмите ОК

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

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

Каждая объявленная переменная или константа имеет свою ограниченную область действия, то есть ограниченную часть программы, в которой эта переменная существует. Область действия зависит от того, где было сделано объявление переменной или константы. Возьмём, к примеру, переменную sVAT_Rate, которая используется в функции Total_Cost. В следующей таблице рассмотрены два варианта области действия переменной sVAT_Rate, объявленной в двух различных позициях в модуле:

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

Однако, если будет вызвана какая-то функция, расположенная в другом модуле, то для неё переменная sVAT_Rate будет не известна.

При попытке использовать sVAT_Rate в другой процедуре, компилятор VBA сообщит об ошибке, так как эта переменная не была объявлена за пределами функции Total_Cost (при условии, что использован оператор Option Explicit).

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

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

Для объявления констант также можно использовать ключевые слова Public и Private, но не вместо ключевого слова Const, а вместе с ним.

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

Это значит, что sVAT_Rate и iMax_Count будут доступны в любом модуле проекта.

Это значит, что sVAT_Rate и iMax_Count будут доступны во всех процедурах текущего модуля, но не будут доступны для процедур, находящихся в других модулях.

Что такое переменная и как правильно её объявить?

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

Для чего нужна переменная? Чтобы хранить значение и применить его позже в любой момент. Например, в ячейке А1 записана сумма, а нажатием на кнопку запускается обновление отчета. После обновления отчета сумма в А1 изменится. Необходимо сверить сумму до обновления с суммой после и в зависимости от этого сделать какое-либо действие. Переменная как раз позволит запомнить значение ячейки до того, как она обновится и использовать именно это значение после обновления.

Читать еще:  Vba excel очистить лист

Требования к переменным:

В качестве имен переменных можно использовать символы букв и числа, но первой в имени переменной всегда должна быть буква. Не допускается использование точки, запятой, пробела и иных знаков препинания, кроме нижнего подчеркивания. Длина имени не должна превышать 254 символов. Так же нельзя использовать в качестве имен для переменных зарезервированные константы редактора VBA(например Sub, Msgbox, ubound, Date и т.п.). Так же для переменных неважен регистр букв.

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

Как видно из таблицы больше всего памяти занимает Variant . Притом это если он хранит числовые данные. Если же такая переменная будет хранить данные строкового типа(текст), то размер занимаемой памяти будет измеряться уже начиная с 22 байт + длина строки, хранящейся в переменной. Чем больше памяти занимает переменная, тем дольше она инициализируется в памяти и тем медленнее код будет выполняться. Вот поэтому и важно явно задавать тип данных, хранимых в переменной — это называется объявить переменную.

Тип данных Decimal больше не используется, поэтому объявить переменную данного типа в VBA не получится — подобная попытка приведет к синтаксической ошибке. Для работы с данными типа Decimal переменную необходимо изначально объявить как Variant или вообще без типа (например Dim a), т.к. тип данных Variant используется в VBA по умолчанию и принимает любой тип данных.

Так же переменным можно назначать и другие типы данных, которых нет в таблице выше — это типы, которые поддерживаются объектной моделью приложений, к которым «подключен» VBA. Например, если зайти в VBA из Excel, то библиотека типов объектной модели Excel подключена по умолчанию и для переменных становится доступно множество типов этой объектной модели. Многие из них знакомы всем: Workbook, Worksheet, Range, Cells, Chart и т.д. Т.е. это книги, листы, ячейки, диаграммы. Типов много, почти на каждый объект и коллекцию. Рассматривать здесь все бессмысленно. Могу лишь привести пару строк кода:

Dim rRange as Range ‘назначили переменной тип ячейка/диапазон Set rRange = Range(«A1») ‘присвоили ссылку на ячейку A1 текущего листа

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

как объявлять переменные
На самом деле все очень просто. Это делается при помощи операторов области действия: Dim , Public , Static и оператора присвоения типа As . Самый распространенный оператор — Dim . Его и возьмем в качестве примера. Синтаксис объявления:

[оператор области действия] Имя_переменной As [тип данных]

Очень частая ошибка при объявлении переменных, совершаемая начинающими изучать VBA:

Dim MyVar1, MyVar2, MyVar3 As Integer

Вроде бы исходя из логики всем переменным присвоен тип данных Integer . Но это ошибочное суждение. Тип Integer присвоен только последней переменной, к которой он «привязан» оператором As — MyVar3. Все остальные переменные имеют тип данных Variant . Т.е. если Вы не задаете каждой переменной свой тип хранимых данных явно(т.е. не указываете для неё тип данных через As), то VBA сам присваивает для такой переменной тип данных Variant , т.к. он может хранить любой тип данных. А вот так выглядит правильное присвоение типа данных:

Dim MyVar1 As Integer, MyVar2 As Integer, MyVar3 As Integer

Это и есть объявление переменных. Т.е. сначала идет оператор области действия ( Dim , Public , Static ), сразу за ним имя переменной, затем оператор As и тип.
Но это не все. Некоторые типы переменным можно присваивать еще короче — даже без оператора As :

Dim MyVar1%, MyVar2%, MyVar3%

Всего шесть типов, которые можно объявить подобным методом:
! — Single
# — Double
$ — String
% — Integer
& — Long
@ — Currency
На что стоит обратить внимание, при объявлении переменных подобным образом: между именем переменной и знаком типа не должно быть пробелов.
Я лично в большинстве статей предпочитаю использовать первый метод, т.е. полное указание типа. Это читабельнее и понятнее. В каких-то проектах могу использовать краткое указание, в общих(разработка в команде) — полное. В своих кодах Вы вправе использовать удобный Вам метод — ошибки не будет.

Теперь разберемся с операторами области действия( Dim , Public и Static ):

  • Dim — данный оператор используется для объявления переменной, значение которой будет храниться только в той процедуре, внутри которой данная переменная объявлена. Во время запуска процедуры такая переменная инициализируется в памяти, Вы можете использовать её значение внутри только этой процедуры и по завершению процедуры переменная выгружается из памяти(обнуляется) и данные по ней теряются. Переменную, объявленную подобным образом еще называют локальной переменной. Однако с помощью данного оператора можно объявить переменную, которая будет доступна в любой процедуре модуля. Необходимо объявить переменную вне процедуры — в области объявлений(читать как первой строкой в модуле, после строк объявлений типа — Option Explicit ). Тогда значение переменной будет доступно в любой процедуре лишь того модуля, в котором данная переменная была объявлена. Такие переменные называются переменными уровня модуля.
  • Static — данный оператор используется для объявления переменной, значение которой предполагается использовать внутри процедуры, но не теряя значения данной переменной по завершении процедуры. Переменные данного типа обычно используют в качестве накопительных счетчиков. Такая переменная инициализируется в памяти при первом запуске процедуры, в которой она объявлена. По завершении процедуры данные по переменной не выгружаются из памяти, но однако они не доступны в других процедурах. Как только Вы запустите процедуру с этой переменной еще раз — данные по такой переменной будут доступны в том виде, в котором были до завершения процедуры. Выгружается из памяти такая переменная только после закрытия проекта(книги с кодом). В простонародье такие переменные чаще называют глобальными(возможно из-за того, что раньше подобные переменные объявлялись при помощи оператора Global, который в настоящее время устарел и не используется)
  • Public — данный оператор используется для объявления переменной, значение которой будет храниться в любой процедуре проекта. Переменная, объявленная подобным образом, должна быть объявлена вне процедуры — в области объявлений. Такая переменная загружается в память во время загрузки проекта(при открытии книги) и хранит значение до выгрузки проекта(закрытия книги). Использовать её можно в любом модуле и любой процедуре проекта. Важно: объявлять подобным образом переменную необходимо строго в стандартном модуле. Такие переменные называются переменными уровня проекта.
    Для большего понимания того, где и как объявлять переменные уровня проекта два небольших примера.
    Неправильное объявление

Option Explicit Sub main() Public MyVariable As String MyVariable = «Глобальная переменная» ‘показываем текущее значение переменной MsgBox MyVariable ‘пробуем изменить значение переменной Call sub_main ‘показываем измененное значение переменной MsgBox MyVariable End Sub ‘доп.процедура изменения значения переменной Sub ChangeMyVariable() MyVariable = «Изменили её значение» End Sub

переменные не будут видны во всех модулях всех процедур и функций проекта, потому что:
1. Оператор Public недопустим внутри процедуры(между Sub и End Sub), поэтому VBA при попытке выполнения такой процедуры обязательно выдаст ошибку — Invalid Attribut in Sub or Function.
2. Даже если Public заменить на Dim — это уже будет переменная уровня процедуры и для других процедур будет недоступна.
3. Т.к. объявление неверное — вторая процедура( ChangeMyVariable ) ничего не знает о переменной MyVariable и естественно, не сможет изменить именно её.
Правильное объявление

‘выше глобальных переменных и констант могут быть только декларации: Option Explicit ‘принудительное объявление переменных Option Base 1 ‘нижняя граница объявляемых массивов начинается с 1 Option Compare Text ‘сравнение текста без учета регистра ‘глобальная переменная — первой строкой, выше всех процедур Public MyVariable As String ‘далее процедуры и функции Sub main() MyVariable = «Глобальная переменная» ‘показываем текущее значение переменной MsgBox MyVariable, vbInformation, «www.excel-vba.ru» ‘пробуем изменить значение переменной Call ChangeMyVariable ‘показываем измененное значение переменной MsgBox MyVariable, vbInformation, «www.excel-vba.ru» End Sub ‘доп.процедура изменения значения переменной Sub ChangeMyVariable() MyVariable = «Изменили её значение» End Sub

Если при этом вместо Public записать Dim , то эта переменная будет доступна из всех функций и процедур того модуля, в котором записана, но недоступна для функций и процедур других модулей.
Переменные уровня проекта невозможно объявить внутри модулей классов( ClassModule , ЭтаКнига(ThisWorkbook) , модули листов , модули форм(UserForm) — подробнее про типы модулей: Что такое модуль? Какие бывают модули?)

  • Операторы области действия так же могут применяться и к процедурам. Для процедур доступен еще один оператор области действия — Private . Объявленная подобным образом процедура доступна только из того модуля, в котором записана и такая процедура не видна в диалоговом окне вызова макросов(Alt+F8)
  • Как правильно назвать переменную:

    «Что самое сложное в работе программиста? — выдумывать имена переменным.» 🙂 А ведь придумать имя переменной тоже не так-то просто. Можно, конечно, давать им имена типа: a, d, f, x, y и т.д.(я сам иногда так делаю, но либо в простых кодах, либо для специального запутывания кода). Но стоит задуматься: а как Вы с ними будете управляться в большом коде? Код строк на 10 еще потерпит такие имена, а вот более крупные проекты — не советовал бы я в них оперировать такими переменными. Вы сами запутаетесь какая переменная как объявлена и какой тип данных может хранить и что за значение ей присвоено. Поэтому лучше всего давать переменным осмысленные имена и следовать соглашению об именовании переменных. Что за соглашение? Все очень просто: перед основным названием переменной ставится префикс, указывающий на тип данных, который мы предполагаем хранить в данной переменной. Про имеющиеся типы данных я уже рассказал выше. А ниже приведена примерная таблица соответствий префиксов типам данных:

    VBA Excel. Переменная диапазона ячеек (As Range)

    Присвоение диапазона ячеек объектной переменной в VBA Excel. Адресация ячеек в переменной диапазона и работа с ними. Определение размера диапазона. Примеры.

    Присвоение диапазона ячеек переменной

    Чтобы переменной присвоить диапазон ячеек, она должна быть объявлена как Variant, Object или Range:

    Чтобы было понятнее, для чего переменная создана, объявляйте ее как Range.

    Присваивается переменной диапазон ячеек с помощью оператора Set:

    В выражении Range(Cells(3, 4), Cells(26, 18)) вместо чисел можно использовать переменные.

    Для присвоения диапазона ячеек переменной можно использовать встроенное диалоговое окно Application.InputBox, которое позволяет выбрать диапазон на рабочем листе для дальнейшей работы с ним.

    Адресация ячеек в диапазоне

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

    Индексация ячеек в присвоенном диапазоне осуществляется слева направо и сверху вниз, например, для диапазона размерностью 5х5:

    Индексация строк и столбцов начинается с левой верхней ячейки. В диапазоне этого примера содержится 5 строк и 5 столбцов. На пересечении 2 строки и 4 столбца находится ячейка с индексом 9. Обратиться к ней можно так:

    Обращаться в переменной диапазона можно не только к отдельным ячейкам, но и к части диапазона (поддиапазону), присвоенного переменной, например,

    обращение к первой строке присвоенного диапазона размерностью 5х5:

    и обращение к первому столбцу присвоенного диапазона размерностью 5х5:

    Работа с диапазоном в переменной

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

    равнозначны. В обоих случаях информационное сообщение MsgBox выведет значение ячейки с индексом 6.

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

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

    Пример 1 – работа со значениями

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

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

    Пример 2 – работа с форматами

    Продолжаем работу с тем же диапазоном рабочего листа «C6:E8»:

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

    Пример 3 – копирование и вставка диапазона из переменной

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

    Скопировать и вставить диапазон полностью со значениями и форматами можно при помощи метода Copy, указав место вставки (ячейку) на рабочем листе.

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

    data_client

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    Microsoft Excel

    трюки • приёмы • решения

    Построение графика функции одной переменной средствами Excel

    Для построения графика функции одной переменной, прежде всего, необходимо задать множество (диапазон) значений независимой переменной и соответствующее множество значений зависимой (функциональной) переменной. После этого следует воспользоваться мастером построения диаграмм программы MS Excel для изображения графика заданной функциональной зависимости. При этом задание значений независимой переменной удобно выполнить с помощью рассмотренной ранее операции автозаполнения. Ниже приводится описание последовательности практических действий для примера построения графика функции: f(x) = 2x 2 — Зx — 5 в интервале изменения независимой переменной: х∈[-5, 5] .

    Для начала рекомендуется создать новую книгу с именем Графики Функций. На отдельном листе в ячейку А1 введем текст «Значения переменной:», а в ячейку В1 введем текст «Значения функции:». Хотя исходная независимая переменная принимает значения из непрерывного интервала действительных чисел [-5, 5], для построения графика необходимо для этой независимой переменной задать дискретные значения. Для наших целей вполне достаточно рассмотреть последовательный диапазон значений от -5 до 5 с интервалом их изменения, равным 0,1.

    Для задания этого диапазона значений в ячейку А2 введем наименьшее значение интервала изменения независимой переменной: число -5, а в ячейку А3 — число -4,9. После чего вторым способом автозаполнения запишем диапазон значений независимой переменной в ячейки А2:А102. Далее в ячейку В2 введем формулу: =2*A2^2-3*A2-5 , которую с помощью первого способа автозаполнения запишем в ячейки В2:В102. Результат выполнения данной последовательности операций по подготовке исходных данных будет иметь следующий вид (рис. 1).

    Рис. 1. Исходные данные для построения графика функции одной переменной

    Для построения графика необходимо воспользоваться мастером диаграмм, который может быть вызван с помощью кнопки стандартной панели инструментов или операции главного меню: Вставка → Диаграмма (рис. 2). Мастер диаграмм представляет собой самостоятельное диалоговое окно с набором вкладок, обеспечивающих задание отдельных свойств элементам графического изображения.

    Рис. 2. Внешний вид диалогового окна мастера диаграмм (шаг 1 из 4)

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

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

    Рис. 3. Спецификация источника данных для построения графика с помощью мастера диаграмм (шаг 2 из 4)

    Первая из вкладок (рис. 3, а) служит для задания диапазона значений рядов данных, которые будут отображены на диаграмме. С этой целью на втором шаге построения диаграммы необходимо выбрать ячейки с данными, которые должны быть отображены на соответствующем графике. Применительно к рассматриваемому примеру это значения функции, которые содержатся в диапазоне ячеек В2:В102. Для указания этих значений следует установить переключатель Строка / столбец так, чтобы в левом окне были Элементы легенды (ряды). После этого выполнить щелчок на строке «Значения функции:», и затем на расположенной выше кнопке Изменить или Добавить.

    В результате будет открыто дополнительное небольшое окно, в строке Значения необходимо указать источник данных диаграммы. Для указания источника данных следует на рабочем листе «Квадратный трехчлен» с помощью мыши или клавиатуры выделить диапазон значений функции В2:В102. Он будет иметь специальную рамку в форме мерцающей пунктирной линии, а в соответствующей строке появится надпись с указанием имени рабочего листа и абсолютных адресов ячеек этого диапазона (рис. 3, б). Далее на этом же шаге работы мастера диаграмм следует задать подписи по горизонтальной оси. С этой целью необходимо перейти на строку «Значения функции:» и выполнить щелчок на кнопке с именем Изменить и выбрать строку Имя ряда. Оба шага можно выполнить одновременно.

    Рис. 4. Спецификация подписи горизонтальной оси X для построения графика с помощью мастера диаграмм (шаг 2 из 4)

    В результате будет открыто дополнительное небольшое окно, в единственной строке которого необходимо указать источник данных для независимой переменной графика. Для указания соответствующего источника данных следует на рабочем листе «Квадратный трехчлен» с помощью мыши или клавиатуры выделить диапазон значений функции А2:А102. Выделенный диапазон также приобретет специальную рамку в форме мерцающей пунктирной линии, а в соответствующей строке появится надпись с указанием имени рабочего листа и абсолютных адресов ячеек этого диапазона (рис. 4, б). После выбора спецификации рядов данных и подписей оси X для графика следует перейти к третьему шагу построения диаграммы с помощью мастера диаграмм (рис. 5).

    Рис. 5. Спецификация названия диаграммы и выбор варианта ее размещения с помощью мастера диаграмм (шаги 3 и 4 из 4)

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

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

    Рис. 6. Результат построения графика функции: f(x) = 2x² — Зx — 5 в интервале изменения независимой переменной: х∈[-5, 5]

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

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

    Ссылка на основную публикацию
    ВсеИнструменты 220 Вольт
    Adblock
    detector