Как ввести переменную в excel

Могу ли я создать переменную в excel, возможно, используя макрос или что-нибудь более простое, которое сохраняет мою ценность и затем использует это в формуле,

Моя основная проблема заключается в том, что я пытаюсь ежемесячно составлять формулу для расчета совокупного процента, поэтому для примера: я инвестирую Rs.2,00,000 @ 10% процентной ставки. В обычном методе я должен сделать: — 200000 * (10% / 12), который даст 1666.67, теперь я добавлю (200000 + 1666.67) * (10% / 12), что даст одно значение и так далее. Поэтому я хочу создать то, что может хранить количество и интерес Принципа каждый месяц.

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

Ответов: 2

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

Например, если вы сохранили статическое значение в ячейке A1, вы должны ссылаться на это значение в formul? как $A$1 .

Если вам не нужно статическое значение на той же самой электронной таблице, на которой вы работаете, вы можете создать новый лист с именем «Static» (или как вам нравится), а затем ссылаться на ячейку как Static!$A$1 . Затем лист может быть скрыт, если хотите.

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

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

Вы также можете создать / использовать a Named Range в своей формуле. Чтобы создать Principal :

Формулы> Диспетчер имен> Новый (или введите имя в поле имени, как показано на рисунке ниже)

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

Например, если вы называете свой диапазон Principal , вы можете ссылаться на диапазон (значение) в формуле как таковую:

Это по существу то же самое решение, что упоминалось выше, но может быть более интуитивным, чтобы ссылаться на именованный диапазон (Principal) скорее на заблокированную ячейку, такую ??как ($ A $ 1).

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

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

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

Простейшие формулы

Рассмотрим простой пример, в котором будем использовать таблицу, представленную на рис. 5.27.

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

Обратите внимание, к ячейкам столбца Цена применен формат Денежный. Данный формат добавляет к указанному числу 2 десятичных знака и обозначение денежной единицы. Такой же формат применен и к ячейкам столбца Стоимость, но на текущий момент эти ячейки пусты. В ячейки столбца Стоимость нужно добавить формулы, вычисляющие стоимость каждого товара. Это очень простые формулы, в которых нужно умножить цену единицы товара на его количество. В качестве переменных будут использоваться ячейки из столбцов Цена и Количество. Итак, начнем.

  1. Создайте таблицу, аналогичную приведенной на рис. 5.37.
  2. Выделите ячейку D2, в которой должна рассчитываться стоимость первого товара.
  3. Введите знак = (равно). Ввод любой формулы начинается с этого знака.
  4. Щелкните мышью по ячейке B2. Она будет выделена пунктирной рамкой, и адрес указанной ячейки окажется вставлен в формулу.
  5. Введите знак * (звездочку). Это оператор умножения.
  6. Щелкните мышью по ячейке C2. Адрес этой ячейки будет вставлен в формулу. Формула в ячейке D2 должна иметь вид =B2*C2.
  7. Нажмите клавишу Enter, чтобы завершить ввод формулы (рис. 5.38).
Читайте также:  Как восстановить корзину windows vista

Как только вы завершили ввод формулы, в ячейке D2 мгновенно появился результат вычисления. Значение, указанное в ячейке B2, было умножено на значение, указанное в ячейке C2, и результат умножения был возвращен ячейкой D2 (ячейкой, в которой находится формула). Выделите ячейку D2. Обратите внимание, в выделенной ячейке отображается результат вычисления. Но если вы посмотрите на строку формул, то увидите в ней формулу, содержащуюся в ячейке. Таким образом, в этой строке вы можете редактировать формулу. Также обратите внимание, что мы не вводили адреса ячеек в формулу вручную. Вместо этого мы просто выделяли ячейки, адреса которых нужно подставить в формулу. Тем не менее ручной ввод адресов тоже допускается.

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

  1. Выделите ячейку D2, то есть ячейку, в которой содержится формула.
  2. Нажмите кнопку Копировать в группе Буфер обмена на вкладке Главная (или нажмите сочетание клавиш Ctrl+C). По периметру ячейки появится пунктирная рамка и содержимое ячейки (именно формула, а не возвращенный ей результат), будет помещено в буфер обмена.
  3. Выделите ячейки D3:D9, то есть ячейки, в которые нужно добавить формулу.
  4. Нажмите кнопку Вставить в группе Буфер обмена на вкладке Главная (или сочетание клавиш Ctrl+V). Формула будет вставлена во все выделенные ячейки, и в них мгновенно отобразится результат вычисления (рис. 5.39).

Виды ссылок

Теперь рассмотрим одну особенность, которая позволяет тиражировать формулу сразу в несколько ячеек. Выделите ячейку D3 и посмотрите на строку формул. Вы увидите, что в ячейке содержится формула =B3*C3. Выделите ячейку D7 и убедитесь, что в ней содержится формула =B7*C7. Как так получилось, если мы копировали в буфер формулу =B2*C2?

Дело в том, что Excel распознает смещение формулы в другую ячейку и автоматически применяет это смещение ко всем переменным в формуле. Таким образом, программа считает, что, если вы копируете формулу из второй строки (и в формуле участвуют переменные из второй строки) в третью строку, значит, и к переменным в формуле нужно применить аналогичное смещение. То есть адреса ячеек меняются, например, C2 на C3, C4, C5 и т. д. Это свойство позволяет копировать ранее созданную формулу в сотни ячеек одновременно, не заботясь о корректировке переменных. Ссылки, которые автоматически корректируются при копировании формулы в другие ячейки, называются относительными.

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

Предположим, что стоимость товара нужно еще разделить на курс валюты, который указан в единственной отдельно расположенной ячейке E2. Если мы создадим формулу вида =(B2*C2)/E2, то при копировании формулы в третью строку она будет преобразована в =(B3*C3)/E3, что приведет к неверному результату вычисления, поскольку курс валюты указан только в ячейке E2 и нигде больше. Поэтому в формуле =(B2*C2)/E2 нужно заменить относительный адрес ячейки E2 на абсолютный. В абсолютном адресе перед заголовком столбца и номером строки вводится знак $. То есть, чтобы добавить в формулу абсолютный адрес ячейки E2, нужно ввести $E$2, чтобы формула приняла вид =(B2*C2)/$E$2. Теперь при копировании формулы в третью строку, формула будет преобразована в =(B3*C3)/$E$2. Как видите, относительные ссылки в формуле изменились в соответствии со смещением позиции формулы на листе, но ссылка на ячейку с курсом валюты осталась неизменной.

Существуют также смешанные ссылки . Это ссылки, в которых одна из координат является абсолютной, а другая — относительной. Пример: $E2 и E$2. В первом случае столбец в ссылке остается неизменным при любых обстоятельствах, в то время как номер строки может меняться при смещении формулы. Во втором случае, наоборот, строка ссылки закреплена, в то время как столбец в адресе ячейки может меняться. Знак $ для добавления в формулу абсолютной ссылки можно не вводить вручную. При добавлении в формулу ссылки методом выделения соответствующей ячейки, нажмите после выделения ячейки клавишу F4. Каждое нажатие клавиши будет последовательно менять ссылку на абсолютную, смешанную и относительную.

Математические операции

Любая математическая формула в ячейке создается достаточно просто. Вы вводите формулу точно так же, как писали бы ее на бумаге, только вместо переменных подставляете адреса ячеек, в которых они находятся. Предположим, нам нужно создать формулу вида D=1,25*A/(В+С)*2.

Читайте также:  Зона поражения ядерной бомбы

Разберем эту формулу. D — это результат вычисления формулы. Этот результат будет выводиться в ячейке, в которой расположена формула. То есть в электронной версии формулы нам данная буква не нужна. 1,25 и 2 — постоянные числа в формуле, то есть константы. Соответственно, они остаются неизменными. А, В и С — переменные. Эти числа могут меняться, что повлияет на результат вычисления. В электронном виде в качестве переменных используются ячейки. Предположим, что переменная А находится в ячейке C2, B — в ячейке D2, а C — в ячейке E2. Таким образом, формула в ячейке Excel будет иметь вид =1,25*C2/(D2+E2)*2.

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

Математические операторы Excel

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

В колонке Скидка указаны размеры скидки для каждого вида товаров (в процентах). В колонке Стоимость с учетом скидки нужно создать формулу, которая будет высчитывать итоговую стоимость вида товара с вычетом скидки. Формула должна иметь вид =Ст- (Ст/100)*Ск, где Ст — стоимость товара, а Ск — скидка, выраженная в процентах.

  1. Выделите ячейку F2, то есть первую ячейку в колонке Стоимость с учетом скидки.
  2. Введите знак = (равно).
  3. Щелкните мышью по ячейке D2, чтобы подставить в формулу адрес ячейки с возвращенной стоимостью товара.
  4. Введите знак «минус».
  5. Введите круглую открывающую скобку.
  6. Снова щелкните мышью по ячейке D2, чтобы подставить ее в формулу.
  7. Введите знак деления /.
  8. Введите 100.
  9. Введите круглую закрывающую скобку.
  10. Введите знак умножения *.
  11. Щелкните мышью по ячейке F2 (первой ячейке в колонке Скидка). Адрес ячейки будет вставлен в формулу. У вас должна получиться формула =D2− (D2/100)*E2 (рис. 5.41).
  12. Нажмите клавишу Enter, чтобы завершить ввод формулы (рис. 5.42).

В ячейке F2 отображается стоимость товара за вычетом скидки, указанной в ячейке E2. Обратите внимание, в формуле участвует переменная, которая сама по себе является возвращенным результатом. Мы знаем, что в ячейке D2 находится формула, возвращающая стоимость товара, и этот возвращенный результат используется в качестве переменной в формуле в ячейке F2. Нам остается размножить формулу на остальные ячейки столбца Стоимость с учетом скидки.

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

Для построения графика функции одной переменной, прежде всего, необходимо задать множество (диапазон) значений независимой переменной и соответствующее множество значений зависимой (функциональной) переменной. После этого следует воспользоваться мастером построения диаграмм программы 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). Мастер диаграмм представляет собой самостоятельное диалоговое окно с набором вкладок, обеспечивающих задание отдельных свойств элементам графического изображения.

Читайте также:  Видеокамера самсунг full hd 1920 1080 инструкция

Рис. 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 график функции получен после редактирования его свойств в результате изменения цвета фона диаграммы, линии кривой графика и интервала отображения промежуточных значений независимой переменной.