Агуулга
Баяр хүргэе! Та марафон гүйлтийн сүүлчийн өдөр ирлээ 30 хоногийн дотор 30 Excel функц. Энэ нь урт бөгөөд сонирхолтой аялал байсан бөгөөд энэ хугацаанд та Excel-ийн функцүүдийн талаар олон хэрэгтэй зүйлийг сурч мэдсэн.
Марафон гүйлтийн 30 дахь өдөр бид үйл ажиллагааны судалгааг зориулах болно ҮНЭГҮЙ (ШУУД), текстийн мөрөөр заасан холбоосыг буцаана. Энэ функцийн тусламжтайгаар та хамааралтай унждаг жагсаалтуудыг үүсгэж болно. Жишээлбэл, унадаг жагсаалтаас улсаа сонгохдоо хотын унадаг жагсаалтад аль сонголтууд гарч ирэхийг тодорхойлно.
Тиймээс, функцийн онолын хэсгийг нарийвчлан авч үзье ҮНЭГҮЙ (ШУУД БУС) ба түүний хэрэглээний практик жишээг судлаарай. Хэрэв танд нэмэлт мэдээлэл эсвэл жишээ байгаа бол тэдгээрийг сэтгэгдэл дээр хуваалцана уу.
30-р функц: ШУУД БУС
Чиг үүрэг ҮНЭГҮЙ (ШУУД) нь текстийн мөрт заасан холбоосыг буцаана.
ШУУД БУС функцийг хэрхэн ашиглах вэ?
Функцээс хойш ҮНЭГҮЙ (ШУУД ҮЙЛЧИЛГЭЭ) нь текстийн стрингээр өгөгдсөн холбоосыг буцаадаг бөгөөд та үүнийг дараах зорилгоор ашиглаж болно:
- Шилжихгүй анхны холбоос үүсгэ.
- Статик нэртэй мужид лавлагаа үүсгэ.
- Шийт, мөр, баганын мэдээллийг ашиглан холбоос үүсгэнэ үү.
- Тоонуудын шилжилтгүй массив үүсгэ.
Синтакс ШУУД БУС (ШУУД БУС)
Чиг үүрэг ҮНЭГҮЙ (ШУУД) дараах синтакстай байна:
INDIRECT(ref_text,a1)
ДВССЫЛ(ссылка_на_ячейку;a1)
- ref_text (нүдэнд_холбоос) нь холбоосын текст юм.
- a1 – Хэрэв ҮНЭН (ҮНЭН) -тэй тэнцүү эсвэл заагаагүй бол холбоосын хэв маягийг ашиглана A1; мөн FALSE (FALSE) бол хэв маяг R1C1.
ШУУД БУС (ШУУД БУС) занга
- Чиг үүрэг ҮНЭГҮЙ Excel ажлын хуудсан дээрх утгууд өөрчлөгдөх бүрт (ШУУД БУС) дахин тооцоолно. Хэрэв функцийг олон томьёонд ашигладаг бол энэ нь таны ажлын номыг ихээхэн удаашруулж болзошгүй юм.
- Хэрэв функц ҮНЭГҮЙ (ШУУД БУС) нь Excel-ийн өөр ажлын ном руу холбоос үүсгэдэг, тэр ажлын ном нээлттэй байх ёстой, эс тэгвээс томъёо нь алдаа мэдээлнэ #REF! (#LINK!).
- Хэрэв функц ҮНЭГҮЙ (ШУУД БУС) нь мөр, баганын хязгаараас хэтэрсэн мужийг заадаг бол томъёо нь алдаа мэдээлнэ #REF! (#LINK!).
- Чиг үүрэг ҮНЭГҮЙ (ШУУД) динамик нэртэй мужийг лавлах боломжгүй.
Жишээ 1: Шилжихгүй анхны холбоос үүсгэ
Эхний жишээнд C ба E баганууд нь ижил тоонуудыг агуулж байгаа бөгөөд тэдгээрийн нийлбэрийг функцээр тооцдог SUM (SUM) нь мөн адил байна. Гэсэн хэдий ч томъёо нь арай өөр байна. C8 нүдэнд томъёо нь:
=SUM(C2:C7)
=СУММ(C2:C7)
E8 нүдэнд функц ҮНЭГҮЙ (ШУУД БУС) нь E2 эхлэл нүд рүү холбоос үүсгэдэг:
=SUM(INDIRECT("E2"):E7)
=СУММ(ДВССЫЛ("E2"):E7)
Хэрэв та хуудасны дээд талд мөр оруулаад XNUMX-р сарын (XNUMX-р сарын) утгыг нэмбэл C баганын дүн өөрчлөгдөхгүй. Дараах мөр нэмэхэд томьёо өөрчлөгдөнө.
=SUM(C3:C8)
=СУММ(C3:C8)
Гэсэн хэдий ч функц ҮНЭГҮЙ (ШУУД БУС) нь E2-г эхлэлийн нүдээр засдаг тул XNUMX-р сарыг Е баганын дүнгийн тооцоонд автоматаар оруулна. Төгсгөлийн нүд өөрчлөгдсөн боловч эхлэлийн нүдэнд нөлөөлөөгүй.
=SUM(INDIRECT("E2"):E8)
=СУММ(ДВССЫЛ("E2"):E8)
Жишээ 2: Статик нэртэй муж руу холбох
Чиг үүрэг ҮНЭГҮЙ (ШУУД БУС) нь нэрлэсэн мужид лавлагаа үүсгэж болно. Энэ жишээнд цэнхэр эсүүд мужийг бүрдүүлдэг NumList. Нэмж дурдахад В баганын утгуудаас динамик мужийг үүсгэдэг NumListDyn, энэ баганад байгаа тооны тооноос хамаарна.
Хоёр мужын нийлбэрийг функцэд аргумент болгон нэрийг нь өгөх замаар тооцоолж болно SUM (SUM), E3 ба E4 нүднүүдээс харж болно.
=SUM(NumList) или =СУММ(NumList)
=SUM(NumListDyn) или =СУММ(NumListDyn)
Функцэд мужын нэрийг бичихийн оронд SUM (SUM), Та ажлын хуудасны аль нэг нүдэнд бичсэн нэрийг дурдаж болно. Жишээлбэл, хэрэв нэр NumList D7 нүдэнд бичигдсэн бол E7 нүдэнд томъёо дараах байдалтай байна.
=SUM(INDIRECT(D7))
=СУММ(ДВССЫЛ(D7))
Харамсалтай нь функц ҮНЭГҮЙ (ШУУД БУС) нь динамик мужын лавлагааг үүсгэх боломжгүй тул та энэ томьёог E8 нүдэнд хуулах үед алдаа гарах болно. #REF! (#LINK!).
Жишээ 3: Шийт, мөр, баганын мэдээллийг ашиглан холбоос үүсгэ
Та мөр, баганын дугаар дээр тулгуурлан холбоосыг хялбархан үүсгэж, мөн хоёр дахь функцийн аргументийн хувьд ХУДАЛ (FALSE) утгыг ашиглаж болно. ҮНЭГҮЙ (ШУУД БУС). Загварын холбоос ийм байдлаар үүсдэг R1C1. Энэ жишээнд бид хуудасны нэрийг холбоос дээр нэмсэн - 'MyLinks'!R2C2
=INDIRECT("'"&B3&"'!R"&C3&"C"&D3,FALSE)
=ДВССЫЛ("'"&B3&"'!R"&C3&"C"&D3;ЛОЖЬ)
Жишээ 4: Тоонуудын шилжилтгүй массив үүсгэ
Заримдаа та Excel-ийн томъёонд тоонуудын массив ашиглах хэрэгтэй болдог. Дараах жишээн дээр бид В баганад байгаа хамгийн том 3 тоог дундажлахыг хүсэж байна. D4 нүдэнд хийсэн шиг тоонуудыг томьёонд оруулж болно.
=AVERAGE(LARGE(B1:B8,{1,2,3}))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;{1;2;3}))
Хэрэв танд илүү том массив хэрэгтэй бол томъёонд бүх тоог оруулахыг хүсэхгүй байх магадлалтай. Хоёр дахь сонголт бол функцийг ашиглах явдал юм зурвас газар (ROW), D5 нүдэнд оруулсан массивын томъёонд хийсэн шиг:
=AVERAGE(LARGE(B1:B8,ROW(1:3)))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(1:3)))
Гурав дахь сонголт бол функцийг ашиглах явдал юм зурвас газар (STRING) хамт ҮНЭГҮЙ (ШУУД БУС), D6 нүдэнд массивын томъёогоор хийсэн:
=AVERAGE(LARGE(B1:B8,ROW(INDIRECT("1:3"))))
=СРЗНАЧ(НАИБОЛЬШИЙ(B1:B8;СТРОКА(ДВССЫЛ("1:3"))))
Бүх 3 томъёоны үр дүн ижил байна:
Гэсэн хэдий ч, хуудасны дээд талд мөр оруулсан бол томьёоны ишлэлүүд мөр шилжихтэй зэрэгцэн өөрчлөгдөх тул хоёр дахь томьёо нь буруу үр дүн гаргах болно. Одоо хамгийн том гурван тооны дундажийн оронд томьёо нь 3, 4, 5 дахь хамгийн том тооны дундажийг буцаана.
Функцуудыг ашиглах ҮНЭГҮЙ (ШУУД БУС), гурав дахь томьёо нь зөв мөрийн лавлагааг хадгалж, зөв үр дүнг харуулсаар байна.