Как сделать переменную в эксель
data_client
Как создать таблицу данных с одной или двумя переменными в Excel
Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.
Таблица данных с одной переменной.
Что бы создать таблицу данных с одной переменной, выполните следующие действия:
1. Выберите ячейку B12 и введите =D10 (ссылка на общую выручку).
2. Введите различные проценты в столбце А.
3. Выберите диапазон A12:B17.
Мы будет рассчитывать общую выручку, если Вы продаете 60% книг по высокой цене, 70% книг по высокой цене и т.д.
4. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.
5. Кликните в поле «Подставлять значения по строкам в: «и выберите ячейку C4.
Мы выбрали ячейку С4 потому что проценты относятся к этой ячейке (% книг, проданных по высокой цене). Вместе с формулой в ячейке B12, Excel теперь знает, что он должен заменять значение в ячейке С4 с 60% для расчета общей выручки, на 70% и так далее.
Примечание: Так как мы создает таблицу данных с одной переменной, то вторую ячейку ввода («Подставлять значения по столбцам в: «) мы оставляем пустой.
Примечание: Строка формул показывает, что ячейки содержат формулу массива. Таким образом, Вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:B17 и нажмите Delete.
Таблица данных с двумя переменными.
Что бы создать таблицу с двумя переменными, выполните следующие шаги.
1. Выберите ячейку A12 и введите =D10 (ссылка на общую выручку).
2. Внесите различные варианты высокой цены в строку 12.
3. Введите различные проценты в столбце А.
4. Выберите диапазон A12:D17.
Мы будем рассчитывать выручку от реализации книг в различных комбинациях высокой цены и % продаж книг по высокой цене.
5. На вкладке Данные, кликните на Анализ «что если» и выберите Таблица данных из списка.
6. Кликните в поле «Подставлять значения по столбцам в: » и выберите ячейку D7.
7. Кликните в поле «Подставлять значения по строкам в: » и выберите ячейку C4.
Примечание: строка формул показывает, что ячейки содержат формулу массива. Таким образом, вы не можете удалить один результат. Что бы удалить результаты, выделите диапазон B13:D17 и нажмите Delete.
Как в офисе.
Никто не спорит, что работая в 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 количество закрывающих и открывающих скобок не совпадает, то Excel выдаст сообщение об ошибке и предложит вариант ее исправления.
Относительные ссылки Excel
Выше были рассмотрены относительные ссылки, которые указывают на некоторые ячейки: А1, В1 и др. Их называют относительными, так как при копировании формулы в другую ячейку, содержащиеся в ней формулы изменяются на величину перемещения.
Например, если взять ячейку С1, содержащую формулу =A1+В1, и скопировать в ячейку С2, то формула в последней будет =A2+В2. Если копировать по строкам дальше, то формула будет соответственно изменяться, указывая на ячейки той строки, куда выполняется копирование. Это очень удобно для расчета данных таблицы построчно.
Допустим, есть небольшая табличка с какими-то данными. Достаточно создать формулу в итоговом столбце для 1-й строки и скопировать ее на другие строки протаскиванием. Результаты появятся в итоговом столбце для каждой строки. При этом в адресах ячеек изменятся номера строк на величину перемещения формулы.
Этот прием можно проводить и для копирования в другие столбцы, адреса ячеек в формуле изменят имена столбцов на величину смещения формулы относительно исходной (копируемой).
Абсолютные ссылки Excel
Чтобы лучше разобраться в различиях ссылок, давайте рассмотрим такой пример: допустим, надо получить в столбце С сумму чисел, указанных в столбцах А и В, умноженную на некоторый коэффициент, размещенный в ячейке D1 и равный 0,5.
Для выполнения этого задания следует в ячейке с адресом С1 разместить формулу, которая будет вычислять данные. Это удобно выполнить, вводя знаки действия с клавиатуры, а адреса ячеек удобно вводить щелчком мыши на нужных ячейках (при этом их адреса будут заноситься в формулу).
После этого устанавливаем текстовый курсор на адрес ячейки D1 и нажимая на клавишу F4 делаем ссылку абсолютной — $D$1. После этого формула примет такой вид:
Обратите внимание, что в ячейке и в строке формул ссылки на ячейки подсвечены разными цветами. Этими же цветами отмечены ячейки, на которые они указывают.
Ссылки на другие листы книги Excel
Следует отметить, что можно также ссылаться и на другие листы текущей книги Excel (вашего файла) точно так же, как и на ячейки текущего листа. Честно говоря, можно ссылаться и на листы других книг (при этом ссылка будет называться внешней ссылкой), но об этом в другой статье.
Например, чтобы записать в ячейку А1 (Лист 1) ссылку на ячейку А15 листа 2, надо проделать следующие действия:
после этого опять будет выполнен переход на Лист 1 в ячейку А15, где появится формула Excel. =Лист2!А15.
При работе в Excel редактирование формулы выполняется так же, как и редактирование текстовой строки, записанной в ячейку. Т.е. надо курсор Excel (белый крестик) установить на ячейку с формулой и выполнить двойной щелчок мышью или нажать клавишу F2. При этом в формуле появится текстовый курсор и выполняется редактирование.
Как в офисе.
Главной особенностью программы Excel является возможность выполнять различные вычисления в таблицах с данными. Вычисления могут быть как простыми (с применением элементарных арифметических операций), так и сложными (с применением математических, экономических, статистических или логических функций).
Использование формул
Простейшие формулы
Рассмотрим простой пример, в котором будем использовать таблицу, представленную на рис. 5.27.
В таблице приведен перечень канцелярских товаров с указанием цены за единицу товара и количества каждого товара. Нам нужно рассчитать стоимость каждого товара и вывести итоговую сумму, то есть вычислить, сколько денег понадобится для закупки всего набора то варов.
Обратите внимание, к ячейкам столбца Цена применен формат Денежный. Данный формат добавляет к указанному числу 2 десятичных знака и обозначение денежной единицы. Такой же формат применен и к ячейкам столбца Стоимость, но на текущий момент эти ячейки пусты. В ячейки столбца Стоимость нужно добавить формулы, вычисляющие стоимость каждого товара. Это очень простые формулы, в которых нужно умножить цену единицы товара на его количество. В качестве переменных будут использоваться ячейки из столбцов Цена и Количество. Итак, начнем.
Как только вы завершили ввод формулы, в ячейке D2 мгновенно появился результат вычисления. Значение, указанное в ячейке B2, было умножено на значение, указанное в ячейке C2, и результат умножения был возвращен ячейкой D2 (ячейкой, в которой находится формула). Выделите ячейку D2. Обратите внимание, в выделенной ячейке отображается результат вычисления. Но если вы посмотрите на строку формул, то увидите в ней формулу, содержащуюся в ячейке. Таким образом, в этой строке вы можете редактировать формулу. Также обратите внимание, что мы не вводили адреса ячеек в формулу вручную. Вместо этого мы просто выделяли ячейки, адреса которых нужно подставить в формулу. Тем не менее ручной ввод адресов тоже допускается.
Итак, мы создали формулу, высчитывающую стоимость первого товара. Аналогичную формулу нужно добавить и в остальные ячейки столбца D. Но делать это вышеописанным методом нецелесообразно. В нашем примере используется таблица с несколькими строками, но на практике таких строк может быть несколько сотен. Поэтому нам нужно скопировать уже созданную формулу в остальные ячейки столбца D. Делается это просто.
Виды ссылок
Теперь рассмотрим одну особенность, которая позволяет тиражировать формулу сразу в несколько ячеек. Выделите ячейку D3 и посмотрите на строку формул. Вы увидите, что в ячейке содержится формула =B3*C3. Выделите ячейку D7 и убедитесь, что в ней содержится формула =B7*C7. Как так получилось, если мы копировали в буфер формулу =B2*C2?
Дело в том, что Excel распознает смещение формулы в другую ячейку и автоматически применяет это смещение ко всем переменным в формуле. Таким образом, программа считает, что, если вы копируете формулу из второй строки (и в формуле участвуют переменные из второй строки) в третью строку, значит, и к переменным в формуле нужно применить аналогичное смещение. То есть адреса ячеек меняются, например, C2 на C3, C4, C5 и т. д. Это свойство позволяет копировать ранее созданную формулу в сотни ячеек одновременно, не заботясь о корректировке переменных. Ссылки, которые автоматически корректируются при копировании формулы в другие ячейки, называются относительными.
Однако в ряде случаев в формуле нужно использовать строго определенную переменную, которая не должна менять своего адреса при копировании формулы в другую ячейку. Предположим, что в нескольких строках должно выполняться однотипное вычисление, но в нем используется переменная из строго заданной ячейки (пусть, например, это будет отдельно расположенная от таблицы ячейка, в которой указан курс валюты). В этом случае необходимо указать абсолютную ссылку.
Математические операции
Любая математическая формула в ячейке создается достаточно просто. Вы вводите формулу точно так же, как писали бы ее на бумаге, только вместо переменных подставляете адреса ячеек, в которых они находятся. Предположим, нам нужно создать формулу вида 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)*Ск, где Ст — стоимость товара, а Ск — скидка, выраженная в процентах.
В ячейке F2 отображается стоимость товара за вычетом скидки, указанной в ячейке E2. Обратите внимание, в формуле участвует переменная, которая сама по себе является возвращенным результатом. Мы знаем, что в ячейке D2 находится формула, возвращающая стоимость товара, и этот возвращенный результат используется в качестве переменной в формуле в ячейке F2. Нам остается размножить формулу на остальные ячейки столбца Стоимость с учетом скидки.
Диспетчер имен в Excel
Диспетчер имен в Excel применяется для присвоения символьных имён ячейкам или диапазонам ячеек, а также для редактирования созданных имён. Присвоенные имена можно считать обычными переменными, которые допускается использовать внутри различных выражений Excel.
Основное назначение Диспетчера имён в Excel заключается в присвоении символьных имён отдельным ячейкам или диапазонам ячеек. В определённых случаях это может быть весьма удобно, поскольку позволяет в дальнейшем использовать выбранные имена в различных формулах.
Как открыть диспетчер имен в Excel, чтобы посмотреть как он выглядит? В верхнем выберите раздел «Формулы» и нажмите кнопку «Диспетчер имён» (кнопка обычно находится примерно посередине панели, но вообще это зависит от размера экрана и того, менялись ли стандартные настройки панелей Excel).
Сам Диспетчер имён представляет собой всплывающее окно, которое показано на скриншоте ниже. На рисунке показан Диспетчер имен в Excel 2010 версии программы, но в целом его вид практически не различается от версии к версии (к примеру, Диспетчер имен в Excel 2016 практически такой же).
Далее рассмотрено как задавать имена и использовать их в собственных формулах.
Как использовать Диспетчер имён в Excel
Прежде всего нужно понимать те ситуации, в которых вообще имеет смысл задавать ячейкам фиксированные имена. Обычно это требуется в тех случаях, когда размеры некоторых таблиц (или их частей) остаются неизменными, причём не меняется и смысл контента в ячейках.
К примеру, если в какой-то ячейке находится некоторый числовой коэффициент, то возможно имеет смысл задать этой ячейке имя, по которому можно будет сразу понять, что именно находится в данной ячейке.
Фактически можно считать, что Excel позволяет создавать переменные вида «x=10», при этом слева будет имя переменной, а справа — её значение.
Как задать имя и изменить его
Откройте Диспетчер имён и нажмите кнопку «Создать. ». При этом откроется маленькое окошко, которое Вы можете видеть на скриншоте выше. Для каждой переменной требуется обязательно указать:
Также можно указать необязательный комментарий в качестве пояснения. Обычно это поле оставляют просто пустым.
Ограничения для имён
Вы не можете присвоить абсолютно любое имя переменной, поскольку в Excel есть некоторые ограничения на имена переменных. При несоблюдении правил именования переменных Вы увидите ошибку.
Правила задания имён переменных следующие:
Например, мы можем создать переменную с именем «ЧислоПИ». Это правильное имя.
Использование переменных
Имена переменных можно использовать вместо адресов ячеек или диапазонов в формулах. Ставить имя переменной в кавычки или как-то ещё специально обозначать не требуется — Excel самостоятельно определит, что указанное имя является переменной, если на данном листе эта переменная имеет значение в соответствие с её областью видимости.
Если в имени переменной есть ошибка или её область видимости не позволяет использовать переменную на данном листе, то в результате в формуле будет ошибка.
Что ещё можно сказать о переменных в Excel
Обратите внимание, что имена при помощи Диспетчера можно присваивать не только обычным ячейкам, но и объединённым. В этом случае объединённая ячейка считается за одну, как и принято в Excel.
Напомним, что в Excel адресом объединённой ячейки является верхняя левая ячейка диапазона, объединением которого и была получена ячейка.
При выделении ячейки или диапазона, для которых задано имя, в левом верхнем углу таблицы вместо адреса ячейки появляется заданное имя.
Задавайте ячейкам имена в тех случаях, когда предполагается использовать это имя более одного раза. Иначе особого смысла в использовании имён нет.
Примеры заданных переменных и их использование
После статьи Вы можете скачать файл Эксель, в котором приводится простой пример задания имени диапазону ячеек, а также использование имени в формулах. Также некоторые подобные примеры Вы можете найти в прикреплённых файлах к другим статьям по Excel на нашем сайте.
Для комплексного изучения программы Вы можете воспользоваться нашим специальным учебным по Excel, с учебной программой и примерами видео-уроков из которого можно познакомиться по указанной ссылке.
Если Вы нашли интересный пример использования Диспетчера имён, то поделитесь этим в комментариях после статьи. Это совсем несложно, но поможет другим пользователям, изучающим работу в Эксель.
Вы можете просмотреть любой прикреплённый документ в виде PDF файла. Все документы открываются во всплывающем окне, поэтому для закрытия документа пожалуйста не используйте кнопку «Назад» браузера.
- Определение и использование имен в формулах Excel.pdf
Вы можете скачать прикреплённые ниже файлы для ознакомления. Обычно здесь размещаются различные документы, а также другие файлы, имеющие непосредственное отношение к данной публикации.
Существуют ли такие вещи, как переменные в Формуле Excel?
Я ненавижу повторяющиеся функции, особенно в формулах Excel. Есть ли способ, которым я могу избежать чего-то вроде:
[выше приведен простой пример проблемы, а не конкретная формула, с которой я работаю.]
8 ответов
вы можете определить имя для части VLOOKUP формулы.
вы можете хранить промежуточные значения в ячейке или столбце (которые вы можете скрыть, если выберете)
не связанные с переменными, ваш пример также будет решен с помощью MOD :
определение имени, содержащего поиск, является аккуратным решением, однако всегда кажется, что имя листа хранится со ссылкой на ячейку. Тем не менее, я думаю, если вы удалите имя листа в » кавычки, но оставьте «!»это может сработать.
нет способа определить переменную в строке формул в Excel. В качестве обходного пути вы можете поместить функцию в другую ячейку (необязательно, скрывая содержимое или помещая его в отдельный лист). В противном случае вы можете создать функция VBA.
Я знаю, что это немного не по теме, но следуя решению, представленному Йонасом Бемером, на самом деле я думаю, что 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.
вернемся к основной теме, Когда мне нужно использовать формулу, которая повторяет какую-то часть, я обычно помещаю ее в другую ячейку, а затем скрываю ее, как уже предлагали некоторые люди.