I hate repeating functions, particularly in Excel formulas. Is there any way that I can avoid something like:
=IF( VLOOKUP(A1, B:B, 1, 0) > 10, VLOOKUP(A1, B:B, 1, 0) - 10, VLOOKUP(A1, B:B, 1, 0) )
[The above is just a simple example of the problem, and not a particular formula that I’m working with.]
SandPiper
2,7765 gold badges32 silver badges49 bronze badges
asked Apr 21, 2009 at 10:30
You could define a name for the VLOOKUP part of the formula.
- Highlight the cell that contains this formula
- On the Insert menu, go Name, and click Define
- Enter a name for your variable (e.g. ‘Value’)
- In the Refers To box, enter your VLOOKUP formula:
=VLOOKUP(A1,B:B, 1, 0) - Click Add, and close the dialog
- In your original formula, replace the VLOOKUP parts with the name you just defined:
=IF( Value > 10, Value - 10, Value )
Step (1) is important here: I guess on the second row, you want Excel to use VLOOKUP(A2,B:B, 1, 0), the third row VLOOKUP(A3,B:B, 1, 0), etc. Step (4) achieves this by using relative references (A1 and B:B), not absolute references ($A$1 and $B:$B).
Note:
For newer Excel versions with the ribbon, go to Formulas ribbon -> Define Name. It’s the same after that. Also, to use your name, you can do «Use in Formula», right under Define Name, while editing the formula, or else start typing it, and Excel will suggest the name (credits: Michael Rusch)
Shortened steps: 1. Right click a cell and click Define name… 2. Enter a name and the formula which you want to associate with that name/local variable 3. Use variable (credits: Jens Bodal)
answered Apr 21, 2009 at 10:52
Tim RobinsonTim Robinson
53.2k10 gold badges119 silver badges137 bronze badges
5
Now you can use the function LET to declare variables within Excel formulas.
This function is available since Jun 2020 for Microsoft 365 users.
Given your example, the formula will be:
=LET(MyFunc,VLOOKUP(A1,B:B,1,0), IF(MyFunc > 10, MyFunc - 10, MyFunc ) )
The 1st argument is the variable name and the 2nd argument is the function or range. You can add more pairs of arguments variable, function/range.
After adding the variables, the last argument will be your formula of interest — calling the variables you just created.
For more information, please access the Microsoft webpage here.
answered Jan 13, 2021 at 14:24
1
You could store intermediate values in a cell or column (which you could hide if you choose)
C1: = VLOOKUP(A1, B:B, 1, 0)
D1: = IF(C1 > 10, C1 - 10, C1)
answered Apr 21, 2009 at 10:35
Patrick McDonaldPatrick McDonald
63.7k14 gold badges106 silver badges118 bronze badges
1
Two options:
VLOOKUPfunction in its own cell:=VLOOKUP(A1, B:B, 1, 0)(in say, C1), then formula referencing C1:=IF( C1 > 10, C1 - 10, C1 )- create a UDF:
Function MyFunc(a1, a2, a3, a4)
Dim v as Variant
v = Application.WorksheetFunction.VLookup(a1, a2, a3, a4)
If v > 10 Then
MyFunc = v - 10
Else
MyFunc = v
End If
End Function
answered Apr 21, 2009 at 10:34
codeapecodeape
96.9k24 gold badges155 silver badges184 bronze badges
Yes. But not directly.
Simpler way
- You could post Vlookup() in one cell and use its address in where required. — This is perhaps the only direct way of using variables in Excel.
OR
- You could define Vlookup(reference)-10 as a wrapper function from within VBE Macros. Press Alt+f12 and use that function
answered Apr 21, 2009 at 10:40
lprsdlprsd
83.6k47 gold badges135 silver badges168 bronze badges
Defining a NAME containing the lookup is a neat solution, HOWEVER, it always seems to store the sheet name with the cell reference. However, I think if you delete the sheet name in the » quotes but leave the «!», it may work.
answered Aug 25, 2016 at 10:52
0
There isn’t a way to define a variable in the formula bar of Excel. As a workaround you could place the function in another cell (optionally, hiding the contents or placing it in a separate sheet). Otherwise you could create a VBA function.
answered Apr 21, 2009 at 10:39
JeremyJeremy
6,5402 gold badges24 silver badges33 bronze badges
Not related to variables, your example will also be solved by MOD:
=Mod(VLOOKUP(A1, B:B, 1, 0);10)
eckes
63.5k29 gold badges169 silver badges199 bronze badges
answered Sep 13, 2012 at 14:33
1
I know it’s a little off-topic, but following up with the solution presented by Jonas Bøhmer, actually I think that MOD is the best solution to your example.
If your intention was to limit the result to one digit, MOD is the best approach to achieve it.
ie. Let’s suppose that VLOOKUP(A1, B:B, 1, 0) returns 23. Your IF formula would simply make this calculation: 23 — 10 and return 13 as the result.
On the other hand, MOD(VLOOKUP(A1, B:B, 1, 0), 10) would divide 23 by 10 and show the remainder: 3.
Back to the main topic, when I need to use a formula that repeats some part, I usually put it on another cell and then hide it as some people already suggested.
answered Apr 18, 2013 at 14:32
Knowing how to use Excel’s variables gives you the ability to make your formulas – and by extension the spreadsheets containing those formulas – much easier to understand. For example, the formula “=A1B1 + C2” is cryptic, while “SlopeX + Y_Intercept” is recognizable as the equation of a line. Excel documentation refers to variables as “Names,” so when you want to create and manage Excel variables, you use the commands in the Defined Name group, such as “Name Manager.”
-
Create a new workbook and type the below values in any column of your spreadsheet; all values must be entered in the same column:
d e f g
-
Type any number in the cell directly to the right of the “d” cell. This number is the value that the variable “d” will take on. Type any number you want for the next three cells directly below the preceding cell.
-
Move your mouse over the cell that contains the “d,” then press and hold the left mouse button. Drag down and right until your mouse is over the last number you typed; then release the mouse to select the two columns you typed.
-
Click the “Formulas” tab. Click the “Create from Selection” command in the Defined Names group. Excel displays a dialog with options for specifying which group of cells contains text from which you’d like to make variables.
-
Click the “Left column” option to specify the first column into which you typed as the range holding the variable names.
-
Click any empty cell in the current spreadsheet and enter the formula below into the cell. This formula uses the variables you created with the “Create from Selection” command. After you enter the formula, Excel displays its result—the sum of the numbers you entered in the column to the right of the variable names.
= d + e + f + g
-
Click the “Name Manager” button on the Formulas tab. This displays the Name Manager dialog box, which lets you create and edit Excel variables.
-
Click the “d” variable from the dialog’s list of variables, then click the “Delete” button and close the dialog. Observe that the formula you typed now displays “#NAME?”, which indicates an error in the formula. Your deletion of the “d” variable caused the error.
-
Click the “Name Manager” button again, then click the dialog’s “New” button to indicate that you want to make a new variable. Type “d” in the Name control of the New Name dialog, then click in the “Refers to” control. Click the cell directly to the right of the cell containing the “d” to specify the cell that the new variable represents. Close all dialogs and notice that the formula cell no longer shows the “#NAME” error; it contains the sum of all the numbers in the right column as it originally did.
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Еще…Меньше
Функция LET присваивает имена результатам вычисления. Это позволяет сохранять промежуточные расчеты, значения и определять имена в формуле. Такие имена работают только в области действия функции LET. Как и в программировании переменных, LET выполняется с помощью родного синтаксиса формул Excel.
Чтобы использовать функцию LET в Excel, вам нужно определить пару имен и связанных с ними значений, а также вычисление, которое их использует. Необходимо задать хотя бы одну пару имя-значение (переменную). Функция LETподдерживает до 126 таких пар.
Преимущества
-
Повышенная производительность. Если вы несколько раз записываете в формулу одно и то же выражение, Excel вычисляет результат то же количество раз. Функция LET позволяет вызывать выражение по имени, чтобы выполнить вычисление.
-
Простое чтение и составление Вам больше не нужно помнить, на какой диапазон или ячейку ссылалось выражение, что именно оно делало, а также копировать несколько раз одно выражение. Возможность объявлять и называть переменные позволяет делать формулы более понятными и наглядными для вас и тех, кто будет ими пользоваться.
=LET(имя1, значение_имени1, вычисление_или_имя2, [значение_имени2, вычисление_или_имя3…])
|
Аргумент |
Описание |
|---|---|
|
имя1 Обязательно |
Первое имя, которое нужно назначить Должно начинаться с буквы. Не может быть выводом формулы или конфликтовать с синтаксисом диапазона. |
|
значение_имени1 Обязательно |
Значение, которое присваивается аргументу «имя1». |
|
вычисление_или_имя2 Обязательно |
Одно из следующих значений:
|
|
значение_имени2 Необязательно |
Значение, присваиваемое аргументу «вычисление_или_имя2». |
|
вычисление_или_имя3 Необязательно |
Одно из следующих значений:
|
Примечания:
-
Последний аргумент должен быть вычислением, которое возвращает результат.
-
Имена переменных выравниваются по допустимым именам, которые можно использовать в диспетчере имен. Например, «a» является допустимым, а «c» нет, так как оно конфликтует со ссылками на стили R1C1.
Примеры
Пример 1
Рассмотрим простое выражение «СУММ(x; 1)», где x — это именованная переменная, которой можно присвоить значение (в данном случае x присвоено значение 5).
=LET(x, 5, СУММ(x, 1))
При вводе данных в ячейку эта формула возвращает значение 6.
Пример 2
Предположим, что у вас есть необработанные данные о продажах, и вы хотите отфильтровать данные, чтобы показать результаты одного человека, а затем добавить тире в пустые ячейки.
|
Неотфильтрованные данные |
Отфильтрованные данные |
|---|---|
|
|
|
Хотя это можно сделать и без функции LET, использование функции LET позволяет повысить удобочитаемость формулы, а также в два раза быстрее рассчитать результат, чем без функцииLET.
Образец данных
Если вы хотите проделать это самостоятельно, скопируйте приведенную ниже таблицу в ячейку A1 пустого листа.
|
Представитель |
Область |
Продукт |
Доход |
|
Светлана |
Восточный |
Apple |
10,33 ₽ |
|
Максим |
Южный |
Бананы |
90,90 ₽ |
|
Светлана |
Западный |
Манго |
10,85 ₽ |
|
Максим |
Северный |
80,20 ₽ |
|
|
Максим |
Западный |
Бананы |
10,25 ₽ |
|
Светлана |
Восточный |
Apple |
70,72 ₽ |
|
Максим |
Северный |
Манго |
50,54 ₽ |
Исходная формула
=ЕСЛИ(ЕПУСТО(ФИЛЬТР(A2:D8;A2:A8=»Максим»)); «-«;ФИЛЬТР(A2:D8;A2:A8=»Максим»))
Формула с использованием LET
=LET(КритерийФильтра;«Максим»; ФильтрДиапазона; ФИЛЬТР(A2:D8;A2:A8=КритерийФильтра); ЕСЛИ(ПУСТО(ФильтрДиапазона);»-«; ФильтрДиапазона))
Нужна дополнительная помощь?
Я ненавижу повторяющиеся функции, особенно в формулах Excel. Есть ли способ, которым я могу избежать чего-то вроде:
=IF (VLOOKUP(A1, B:B, 1, 0) > 10, VLOOKUP(A1, B:B, 1, 0) — 10, VLOOKUP(A1, B: B, 1, 0) )
[выше приведен простой пример проблемы, а не конкретная формула, с которой я работаю.]
8 ответов
вы можете определить имя для части VLOOKUP формулы.
- выделить ячейку, содержащую эту формулу
- в меню Вставка выберите пункт имя и нажмите кнопку Определить
- введите имя переменной (например, ‘Value’)
- в поле ссылается на введите формулу VLOOKUP:
=VLOOKUP(A1,B:B, 1, 0) - Нажмите кнопку Добавить и закройте диалоговое окно
- в вашей оригинальной формуле замените части VLOOKUP именем, которое вы просто определено:
=IF( Value > 10, Value - 10, Value )
Шаг (1) важен здесь: я думаю, во второй строке вы хотите использовать Excel VLOOKUP(A2,B:B, 1, 0) третья строка VLOOKUP(A3,B:B, 1, 0), etc. Шаг (4) достигает этого, используя относительные ссылки (A1 и B:B), а не абсолютные ссылки ($A и $B:$B).
вы можете хранить промежуточные значения в ячейке или столбце (которые вы можете скрыть, если выберете)
C1: = VLOOKUP(A1, B:B, 1, 0)
D1: = IF(C1 > 10, C1 - 10, C1)
3
автор: Patrick McDonald
не связанные с переменными, ваш пример также будет решен с помощью MOD:
=Mod(VLOOKUP(A1, B:B, 1, 0);10)
два варианта:
-
VLOOKUPфункция в собственной ячейке:=VLOOKUP(A1, B:B, 1, 0)(В С1), то формула ссылается С1:=IF( C1 > 10, C1 - 10, C1 ) - создать UDF:
Function MyFunc(a1, a2, a3, a4)
Dim v as Variant
v = Application.WorksheetFunction.VLookup(a1, a2, a3, a4)
If v > 10 Then
MyFunc = v - 10
Else
MyFunc = v
End If
End Function
определение имени, содержащего поиск, является аккуратным решением, однако всегда кажется, что имя листа хранится со ссылкой на ячейку. Тем не менее, я думаю, если вы удалите имя листа в » кавычки, но оставьте «!»это может сработать.
нет способа определить переменную в строке формул в 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, например:
=CONCATENATE( IF( AND(LEN(A1)>3,LEN(A1)>=5),CONCATENATE( VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,5),2)),rng,2)," হাজার "),IF( AND(LEN(A1)>3,LEN(A1)=4),CONCATENATE( VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,4),1)),rng,2)," হাজার "),"")),IF(LEN(A1)>2,IF(NUMBERVALUE(LEFT(RIGHT(A1,3),1))>0,CONCATENATE(VLOOKUP(NUMBERVALUE(LEFT(RIGHT(A1,3),1)),rng,2),"শত "),""),""),IF(NUMBERVALUE(IF(LEN(A1)>1, RIGHT(A1,2), RIGHT(A1,1)))>0,VLOOKUP( NUMBERVALUE(IF(LEN(A1)>1, RIGHT(A1,2), RIGHT(A1,1))),rng,2,FALSE),""))
В этой формуле мне снова и снова приходилось использовать ссылку «A1». Мне нужна функция или что-то вроде этого —
=DEFVAR(A1,'somevar',CONCATENATE( IF( AND(LEN(somevar)>3,LEN(somevar)>=5),CONCATENATE(....)
Так что я могу использовать формулу в ячейке M9 вот так:
=DEFVAR(M9,'somevar',CONCATENATE( IF( AND(LEN(somevar)>3,LEN(somevar)>=5),CONCATENATE(....)
Есть ли способ добиться этого?
2 ответа
Лучший ответ
Нет, формулы Excel не позволяют объявлять в них переменные.
Но в этом случае вы могли использовать другую ячейку, содержащую LEN(A1), и использовать ее в своей формуле. Очень умный цикл вычислений Excel гарантирует, что LEN(A1) вычисляется раньше любых зависимых формул.
По сути, вы используете одну или несколько других ячеек для хранения «переменных».
Это также упрощает обслуживание вашей электронной таблицы. По возможности избегайте VBA; (1) сложно контролировать версии, (2) не все компании разрешают использование .xlsm из-за проблем с безопасностью, (3) VBA работает в одном потоке.
1
Bathsheba
11 Дек 2017 в 09:41
Ваш основной вопрос: «объявить переменную в формуле и использовать ее в той же формуле».
Ответ НЕТ.
Любая формула в Excel не позволяет объявлять переменную и использовать ее в дальнейшем. Это обычная практика при программировании, и нет необходимости говорить как, поскольку где-то вы написали, что используете VBA !.
Теперь позвольте мне рассказать вам, как косвенно мы используем переменные в формулах Excel.
Это адрес ячейки, который работает как переменная. Другой — это именованный диапазон, который содержит более одного значения, например массив. Более того, вы также можете использовать формулу как переменную .
Позвольте мне сказать, как: написать формулу в ячейке и просто дать ей имя и использовать это имя в любой формуле, как мы вызываем процедуру при программировании. Я показываю вам простой пример .
Запишите формулу A2, = if (A5> = 1000, Max (B2: B100), 0) и просто назовите ее как MYMAX. Тогда в другой формуле вы можете назвать это как, = if (B2 = «A01», MYMAX, «Nothing»).
Надеюсь, это поможет вам понять, почему нам нужно программирование.
1
Rajesh S
11 Дек 2017 в 07:27