查看: 1754|回复: 3
EVALUATE,SUBSTITUTE,ISTEXT结合的函数语言谁给解释下
阅读权限10
在线时间 小时
=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!E5,&[&,&*ISTEXT(&&[&),&]&,&]&&)&))。函数语言是什么，谁给解释下。（表格E5内容类似：(1+1.6)*1.1[墙长+墙长*墙高]+(1+1.6)*0.15[墙长+墙长*墙梁搭接]之类的），作为一个工程计算中常用的计算公式，很想知道下函数语言是什么，谁给解释下，方便记忆呀
阅读权限95
在线时间 小时
1、SUBSTITUTE(Sheet1!E5,&[&,&*ISTEXT(&&[&)&&&&&&&&&把E5中的[,替换成*ISTEXT(&[，结果是：(1+1.6)*1.1*ISTEXT(&[墙长+墙长*墙高]+(1+1.6)*0.15*ISTEXT(&[墙长+墙长*墙梁搭接]。
2、SUBSTITUTE(SUBSTITUTE(Sheet1!E5,&[&,&*ISTEXT(&&[&),&]&,&]&&)&)&&&&&&&&&&&把上一步结果中的],替换成]&)，结果是：(1+1.6)*1.1*ISTEXT(&[墙长+墙长*墙高]&)+(1+1.6)*0.15*ISTEXT(&[墙长+墙长*墙梁搭接]&)。Istext文本判断后相当于：(1+1.6)*1.1*1+(1+1.6)*0.15*1。
3、EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!E5,&[&,&*ISTEXT(&&[&),&]&,&]&&)&)&&&&&&&&&&&即：=(1+1.6)*1.1*1+(1+1.6)*0.15*1，结果是：3.25。
阅读权限200
在线时间 小时
EVALUATE.jpg (69.39 KB, 下载次数: 13)
12:03 上传
阅读权限10
在线时间 小时
厉害，真的厉害，学习了
最新热点 /1
ExcelHome每周都有线上直播公开课，
国内一流讲师真身分享，高手贴身答疑，
赶不上直播还能看录像，
关键居然是免费的！
厚木哥们都已经这么努力了，
你还好意思说学不好Office。
玩命加载中，请稍候
玩命加载中，请稍候
Powered by
本论坛言论纯属发表者个人意见，任何违反国家相关法律的言论，本站将协助国家相关部门追究发言者责任！ & & 本站特聘法律顾问：徐怀玉律师 李志群律师Escape characters, Delimiters and Quotes
The PowerShell escape character is the grave-accent(`)
The escape character can be used in three ways:
1) When used at the end of a line, it is a continuation character - so the command will continue on the next line.
indicate that the next character following should be passed without substitution. For example $myVariable will normally be expanded to display the variables contents but
`$myVariable will just be passed as $myVariable
3) When used inside double quotation marks, the escape character indicates that the following character should be interpreted as a 'special' character.
Special characters
Special characters are used to format/position string output.
Alert bell/beep
Form feed (use with printer output)
Carriage return
Carriage return + New line
Horizontal tab
Vertical tab (use with printer output)
The `r (carriage return) is ignored in PowerShell (ISE) Integrated Scripting Environment host application console, it does work in a PowerShell console session.
Using the Escape character to avoid special meaning.
To avoid using a Grave-accent as the escape character
To avoid using # to create a comment
To avoid using ' to delimit a string
To avoid using & to delimit a string
When setting a string variable the # character does not need to be escaped, but at the command line
# will act as a comment unless escaped:
PS C:\& echo 1 # 1
PS C:\& echo 1 `# 1
The escaped quotes allow quotation marks to be displayed on screen rather than being interpreted as the start or end of a string. The same effect can also be achieved by doubling-up the quote marks: && or ''
For powershell.exe, the horizontal tab stops are every 8th character.
PS C:\> Write-Host &First Line `nSecond line&
First Line
Second Line
PS C:\& &12`t90`n&
PS C:\> Write-Host &Header1`tHeader2 `n123.45`t600&
Header1 &Header2
123.45&&&600
PS C:\> &`a `a&&Two audible beeps are produced.&
Quotation Marks
Either single or double
quotes may be used to specify a literal string.
Single-Quoted Strings (')
When you enclose a string in single quotation marks, any variable names in the string such as '$myVar' will appear exacly as typed when the command is processed. Expressions in single-quoted strings are not evaluated, not even escape characters or any of the Special characters listed above. If the string contains any embedded single quotes, they must be doubled
$msg = 'Every &lecture& should cost $5000'
Double-Quoted Strings (&)
When you enclose a string in double quotation marks, any variable names in the string such as "$myVar" will be
replaced with the variable's value when the
command is processed. You can prevent this substitution by prefixing the $ with an escape character. Any embedded double quotes can be escaped `& or doubled (replace
$msg = &Every &&lecture&& should cost `$5000&
$msg = &Every 'lecture' should cost `$5000&
&The value of & + '$var' + &is '$var'&
&The value of `$var is '$var'&
$query = &SELECT * FROM Customers WHERE Name LIKE '%JONES%'&
Verbatim arguments --%
In PowerShell 3.0 the special marker --% is a signal to PowerShell to stop interpreting any remaining characters on the line. This can be used to call a non-PowerShell utility and pass along some quoted parameters exactly as is.
for example instead of escaping every character that PowerShell may interpret:
PS C:\& FIND.EXE '&Search Text&' &C:\Program Files `(x86`)\Demo\text.txt&
we can instead use:
PS C:\& FIND.EXE
--% &Search Text& &C:\Program Files (x86)\Demo\text.txt&
Any PowerShell
after the --%
won’t be expanded but you can work around this by building up the
command string using more than one variable:
$command = 'FIND.EXE --%'
&C:\Program Files (x86)\Demo\text.txt&
& $command &Search Text& $
If the command type is Application, the parameter --% is not passed to the command. The arguments after --% have any
(strings surrounded by %) automatically expanded. For example:
PS C:\& FINDSTR.EXE --% &%ProgramFiles%&
In the above %ProgramFiles% is replaced with the value $env:ProgramFiles
Concatenating Strings
Concatenate strings with +
In many cases, when combining simple strings, the + operator is not needed:
PS C:\& $first = "abcde"
PS C:\& $second = "FGHIJ"
PS C:\& "$first $second"
abcde FGHIJ
but when combining more complex objects, the + operator becomes very useful:
For example if we retrieve an environment variable
$drive = gci SystemDrive
This returns a DictionaryEntry object with .name and .value properties.
# Adding this to a string will give the wrong result
PS C:\& "aaa $drive bbb"
aaa System.Collections.DictionaryEntry bbb
# Concatenating it with a string is closer, but still wrong:
PS C:\& "aaa " + $drive + " bbb"
aaa System.Collections.DictionaryEntry bbb
# Specify the .value property and concatenate that with + to get the desired result:
PS C:\& "aaa " + $drive.value + " bbb"
aaa C: bbb
# Alternatively use the $( )
PS C:\& "aaa $($drive.value) bbb"
aaa C: bbb
(an easier method would be using $drive = $SystemDrive which will return a system.string in the first place.)
Backslash \
In many programming languages including C# (.Net) and most UNIX shells the escape character is \
PowerShell is a Windows shell, and Windows already uses the backslash as a path separator
C:\Windows\&.
In Windows CMD the escape character is
although this was not a great choice as ^ is a valid character in NTFS filenames.
The PowerShell designers could have adopted the
forward slash as a path separator C:/Windows/& , and then allocated the backslash as the escape character but this would have caused huge confusion and so instead they left paths unchanged and allocated ` as the escape character.
The backtick ` is a valid character in NTFS filenames, so should you encounter one in a filename, it will need to be escaped.
In some PowerShell expressions (matching operations) a backslash character will be interpreted as the start of a , (e.g. \w = match word) this is the industry-standard regex syntax.
To escape this and
treat the backslash as an ordinary character, double it (replace \ with \\ )
PowerShell currently uses
to process command line inputs, but this could potentially change in the future.
Here strings
A here string is a
single-quoted or double-quoted string which can span multiple lines.
Expressions in single-quoted strings are not evaluated.
lines in a here-string are interpreted as strings, even though they are
not enclosed in quotation marks.
$myHereString = @'
some text with &quotes& and variable names
$printthis
some more text
$anotherHereString = @&
The value of `$var is $var
some more text
Inside a here-string, double and single quotes are not special but quoted literally, all line breaks are preserved.
The @ character is also used to create , create
Delimiters
Delimiters separate one parameter from the next - they split the command line up into words.
The standard delimiter for PowerShell is the
space character, in addition the
both accept options for splitting strings with a choice of delimiter.
&Be not angry that you cannot make others as you wish them to be, since you cannot make yourself as you wish to be& - Thomas A Kempis
- Richard L. Mueller
Jose Barreto
Run a PowerShell expression
- Combine a path and child-path
- Pass objects down the pipeline
- PowerShell Variables (int, String)Access denied |
used Cloudflare to restrict access
Please enable cookies.
What happened?
The owner of this website () has banned your access based on your browser's signature (3cf887dc6ccf92ca-ua98).定义单元格名称求和(计算式加文字)_百度文库
两大类热门资源免费畅读
续费一年阅读会员，立省24元！
定义单元格名称求和(计算式加文字)
阅读已结束，下载文档到电脑
想免费下载本文？
定制HR最喜欢的简历
你可能喜欢EXCEL中定义名称中 EVALUATE(SUBSTITUTE(SUBSTITUTE($I7,&[&,&*ISTEXT(&&[&),&]&,&]&&)&)怎么解释_百度知道
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。
EXCEL中定义名称中 EVALUATE(SUBSTITUTE(SUBSTITUTE($I7,&[&,&*ISTEXT(&&[&),&]&,&]&&)&)怎么解释
$I7单元格为一计算式，如3+2
我有更好的答案
2*ISTEXT(&[宽度]&)*3*ISTEXT(&quot.html其中：使用2次SUBSTITUTE将I7单元格中的计算式里面的[号;)再使用EVALUATE执行计算，也就是2*TRUE*3*TRUE，替换为*ISTEXT(&[将]号替换为]&)比如2[宽度]*3[长度]。 如果I7的计算式不会有[备注]文字，不需要这么麻烦，直接定义名称里用=EVALUATE(.com/gouweicao78/blog/item/40a1ff8d543034.html" target="_blank">http.baidu，替换后就是，最终得到6:///gouweicao78/blog/item/40a1ff8d543034;[长度]&quot这个是计算带有备注文字的计算式。<a href="http://hi
采纳率：46%
来自团队：
为您推荐：
其他类似问题
您可能关注的内容
&#xe675;换一换
回答问题，赢新手礼包&#xe6b9;