Excel funksiyasini baholash funktsiyasini ishlatganda #REF qaytaradi

Menda ma'lum bir hujayraning qiymatiga qarab formulani matn sifatida qaytaradigan qo'ng'iroq bor.

Qaytgan formulalar (matn sifatida) quyidagicha ko'rinadi:

SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()))

Shundan so'ng formulani baholash uchun EVALUATE() funktsiyasidan foydalaning, lekin INDIRECT() dan foydalanish sababli #REF xatoni olaman.

This Microsoft Support Page was the only reference that I could find to this particular problem, and it doesn't seem to offer an appropriate workaround.

Qanday qilib: a) INDIRECT dan foydalanishni oldini olish uchun formulani qayta tuzing yoki b) yaxshi o'ynash uchun EVALUATE() ni olasizmi?

EDIT -ni tanlang

Qidiruv jadvali quyidagicha ko'rinadi:

Type A                    SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()))
Type B                    SUM(INDIRECT("AF"&row()),INDIRECT("AG"&row()), INDIRECT("AH"&Row()))

Boshqa bir varaqda, B1: 200 hujayralarining mazmuni A turi yoki B turi hisoblanadi. Ulanish hujayraning qiymatiga qarab formula mag'lubiyatini qaytaradi va uni Bx ga qo'yadi. So'ngra natijani berish uchun simni baholayman.

0
XLM makrosining yoki ishchi varaqning bir qismi sifatida evaluation() ni ishlatasizmi?
qo'shib qo'ydi muallif Tim Williams, manba
Qidiruvni ko'rsatishni xohlaysizmi? Qator() uchun nimani anglatishi aniq emas. Ikkalasi ham shunga o'xshash maqsadga xizmat qiladigan uchun, bilvosita va bilvosita kerak emasligiga ishonchim komil ...
qo'shib qo'ydi muallif Tim Williams, manba
Belgilangan nomni = EVALUATE (jami ("A" & ROW ()), INDIRECT ("B" & ROW ())) bilan mos yozuvlar bilan ishlatish uchun OK ishlaydi: bu bizga muvaffaqiyatsiz bo'lgan aniq bir misol keltira olasizmi?
qo'shib qo'ydi muallif Charles Williams, manba
Ehtimol, ROW o'z argumentiga asoslangan holda array funksiyasini qaytaradi? Ya'ni, ROW() tanlangan satr raqami bilan bitta elementli qatorni qaytaradi. ROW (a42: q99) qaytaradi {42; 43; ...; 99}, va hokazo. Faqat Excelga ega bo'lmaganligi sababli, bu sharhga qo'shmoqchi bo'lasiz.
qo'shib qo'ydi muallif jtolle, manba
Men aslida uni noma'lum qatorda ishlataman, keyin men hujayradan qo'ng'iroq qilaman.
qo'shib qo'ydi muallif Andy F, manba
@Tim, iltimos, tahrirlash jadvali bilan mening tahrirlashga qarang. INDIRECT formulani baholay olmaydi, faqat matnni hujayra mos yozuvi deb sharhlaydi. Shuning uchun ikkalasiga ham kerak.
qo'shib qo'ydi muallif Andy F, manba
@Charles, albatta. C1 uyasiga "typeA" matnini joylashtiring. A1 va B1 hujayralarida navbati 1 va 2 ni joylashtiring. Siz taklif qilgan formula bilan "typeA" deb nomlangan yangi intervalli qo'shing. Endi D1 hujayrasida = INDIRECT (C1) formula o'rnating. #REF xatoni olasiz. Keling, turli tipdagi formulalar bilan "tipB", "tipC" va boshqalar kabi ismlar bilan bir necha nomlangan intervalgacha ega ekanligimni tasavvur qiling. C1-ga kiritgan qiymatiga qarab, D1 xujayrasidagi SUM-ni namoyish qilishni xohlayman.
qo'shib qo'ydi muallif Andy F, manba

4 javoblar

Javob 3-chi javob:

namuna uchun tashakkur: bu adashgan formulani baholash uchun INDIRECT dan foydalanmoqchi bo'lganingiz sababli bajarilmaydi va INDIRECT faqat arizalarni formulalar bilan ishlamaydi.

You need to use EVALUATE instead, but there is no built-in EVALUATE worksheet function (the EVALUATE you are using in the defined name is an ancient XLM Macro function).
I suggest you use my EVAL VBA UDF instead

Public Function EVAL(theInput As Variant) As Variant
'
' if UDF evaluate the input string as though it was on this sheet
' else evaluate for activesheet
'
Dim vEval As Variant
Application.Volatile
On Error GoTo funcfail
If not IsEmpty(theInput) then
If TypeOf Application.Caller.Parent Is Worksheet Then
vEval = Application.Caller.Parent.Evaluate(Cstr(theInput))
Else
vEval = Application.Evaluate(cstr(theInput))
End If
If IsError(vEval) Then
EVAL = CVErr(xlErrValue)
Else
EVAL = vEval
End If
End If
Exit Function
funcfail:
EVAL = CVErr(xlErrNA)
End Function

va undan keyin aniqlangan nomlarni typeA = SUM (Sheet3! RC1, Sheet3RC2) kabi mos yozuvlar bilan ishlating.

There are some "quirks" of EVALUATE that you should be aware of: see
http://www.decisionmodels.com/calcsecretsh.htm

2
qo'shib qo'ydi
Bu men erishmoqchi bo'lgan narsalar uchun eng yaqin natija. Sizning yordamingiz va sabringiz uchun juda minnatdormiz.
qo'shib qo'ydi muallif Andy F, manba

Charlzning javobini kengaytirish uchun: bu funktsiya izlashga e'tibor beradi va satr sonini baholaydi. Men sizning formulalaringizni (masalan, sizning misolingizda) faqat yakuniy natijani qaytaruvchi hujayra bilan bir qatorda ma'lum qiymatlarni yig'ish uchun mo'ljallangan deb o'ylayman.

Mening qidirish jadvalim shunday ko'rinadi:

TypeA   SUM(B ,D) 
TypeB   SUM(C,D)

UDF:

Public Function GetAndRunCalc(CalcType As String)
Application.Volatile
    Dim ac As Object, rw, f

    On Error GoTo haveError

    If TypeOf Application.Caller.Parent Is Worksheet Then
        Set ac = Application.Caller
        rw = ac.Parent.Range(ac.Address).Row
        'adjust for your lookup table...
        f = Application.VLookup(CalcType, Sheet1.Range("B4:C5"), 2, False)
        If Not IsError(f) Then
            f = Replace(f, "", rw)
            GetAndRunCalc = ac.Parent.Evaluate(f)
        Else
            GetAndRunCalc = "Type??"
        End If
    Else
        GetAndRunCalc = "Must be called from worksheet cell"
    End If

    Exit Function

haveError:
    GetAndRunCalc = CVErr(xlErrValue)

End Function 
0
qo'shib qo'ydi

If I understand what you are trying to do correctly, why not get the resukt directly from the cell ..
I don't think you need indirect because you just need the text of the formula
(altho I am not sure what the ROW() is supposed to be doing)
SUM("AF"&row(),"AG"&row())

0
qo'shib qo'ydi
Charlz, satr() hozirgi qatorga havola qiladi. Afsuski, shunga o'xshash sumka yaratish ishlamaydi, chunki matnni hujayra sifatida sharhlash uchun INDIRECT kerak. Bu ishlamaydigan = SUM ("AF2", "AG2") va = SUM (AF2, AG2) o'rtasidagi farq.
qo'shib qo'ydi muallif Andy F, manba

If you just want to use a Defined Name to add column AF and AG on the current row then just use relative references (easier to define in R1C1 mode, then switch back to A1) Defined Name AndySum has Refersto formula of =SUM(RC32,RC33)
Then wherever you use AndySum in a formula it will add the contents of columns AF and AG on that row.

0
qo'shib qo'ydi
Shunday ekan, nima uchun Look formula ni IF yoki CHOOSE bilan tanlab olingan Ismlardagi turli xil lazzatlardan birini tanlash uchun ishlatmang
qo'shib qo'ydi muallif Charles Williams, manba
Tushunyapman, lekin har doim ham AF va AG qo'shilmasligi kerak. Men Timni talab qilgani kabi savolni tahrir qildim, ammo buni amalga oshirishga harakat qilayotganimni yaxshiroq tushuntirishga yordam berishi mumkin. Sabr-toqatingiz uchun tashakkur.
qo'shib qo'ydi muallif Andy F, manba
Mening qidirish jadvalimda bir necha yuz turga ega bo'lishim mumkin, chunki bir necha yuz belgilangan nomga va juda uzoq IF formulasiga olib keladi ...
qo'shib qo'ydi muallif Andy F, manba