anhtuan1066 đã viết:
Cái EVALUATE này tôi chỉ biết dùng, chả giãi thích dc... chỉ biết rằng bạn chỉ có thể dùng nó khi đặt name... còn chèn công thức trực tiếp vào cell thì thua
Nghĩ cũng lạ... chính cái file của tôi đang gữi ở trên, hôm qua mở đi mở lại mấy lần đều báo macro XL4.. giờ tải về mở thử thì lại chẳng thấy gì...
Bác anhtuan1066 này đúng là cao thủ (mà lại còn có cả môn sinh kêu thầy nữa chứ).
Tôi mới "đào mỏ" được cái này, anh em tham khảo thêm nhé:
Excel Tips and Tricks
This month, while answering a question on our question Forum I was reminded of an
old Excel 4.0 Macro function called EVALUATE.
The question was along these lines. A user had cells with contents like;
A1= 21+69+89+25+31
A2= 21*25
A3= 100/10
A4= 100/10*(10*10+10)
A5= 100/10*10*10+10
None of these cells had an equal sign and as such the cells were seen as Text by Excel. The person wanted to leave the original cell contents intact and use Column B to return the result of the equations.
The usual suspect of = "="&A1 was tried in cell B1 but only resulted in B1 showing =21+69+89+25+31 and NOT evaluating the formula. It was at this point the word EVALUATE came into my mind. At first I tried =EVALUATE(A1). Excel did not like this and came back with an error message "That Function is not valid". After this, the penny dropped fully I remembered how it had to be done. See the steps below
1) Select cell B1
2) Go to Insert>Name>Define
3) Type the name Result (can be any valid range name)
4) In the Refers to: box type: =EVALUATE($A1)
5) Click Add then OK.
It is very important to note that I selected cell B1 and used a Relative Row reference for $A1.
I then simply entered =Result into cell B1 and copied it down and it
worked! It even obeys the use of Parenthesis as in the case of: 100/10*(10*10+10)
Another interesting one I answered was for a user who wanted to stop his SUM
Function reference automatically changing when adding a cell and shifting all to the right. For example, in cell A1 was the Function: =SUM($B1:$Z1)
The user was inserting cells in Column B and this was causing =SUM($B1:$Z1)
to change to =SUM($C1:$AA1) when inserting a cell in B1. That is, selecting cell B1, right clicking, choosing Insert and then checking Shift cells right and clicking OK.
The solution was in the use of the INDIRECT function.
