За та бүхэнд энэ өдрийн мэндийг хүргье. Юуны өмнө одоо оруулах гэж буй нийтлэл маань өмнөх хугацаанд оруулж байсан Өгөгдсөн хоёр лист дэхь давхардсан утгыг тодорхойлох гэсэн нийтлэлтэй зарим талаараа төстэй болохыг сануулая. Иймээс энэхүү нийтлэлийг уншихдаа өмнөх нийтлэлтэйгээ харьцуулан уншихад илүүдэхгүй гэдийг зөвлөе. Бид өмнө нь өгөгдсөн хоёр листний утгуудыг өөр хооронд нь харьцуулах гэж нилээдгүй томьёо бичсэн. Харин өнөөдөр өгөгдсөн листний хоосон мөрийг хэрхэн алга болгох тухай томьёоллыг авч үзэх болно.

Бидэнд дараах мэдээлэл өгөгджээ. Үүнд: Өгөгдсөн мэдээлэл гэдэг нь A1:A15 муж дахь мэдээлэл.

Бид Лист_1-н хэлбэрийг Зурагт үзүүлсний дагуу Лист_2-т оруулах шаардлагатай болсон. Энэ тохиолдолд бид MS Excel программд ямар томьёолол бичиж оруулах бэ? гэдэг асуулт юм.

Ингээд дээрх асуудлыг шийдвэрлэх ерөнхий зарчимаа тодорхойлоё. Үүнд:

1-рт. Лист_1-т хамаарагдаж буй муж дахь нүд бүрт хоосон утга байгаа эсэхийг шалгана.

2-рт. Лист_1-т хамаарагдаж буй муж дахь нүд бүрийн байршлыг тодорхойлно.

3-рт. Лист_1 дэхь мужийн утга агуулагдаж байгаа нүдний байршилыг тодорхойлж эрэмблэнэ.

4-рт. Эрэмблэгдсэн байршилд дахь утгыг Лист_1-сэ тодорхойлно.

1-р алхам: Бид Лист_1-н утгуудад хоосон утга байгаа эсэхийг шалгана. Үүнийг бид =IF() функцийг хэлбэрээр ашиглан гүйцэтгэнэ.

=IF($A$2:$A$15<>"","Yes","No")

Хэрэв Лист_1 буюу =А2:A15 мужийн нүднүүдэд утга байвал YES, хэрэв байхгүй бол NO гэсэн хариултыг тус томьёо өгнө.

2-р алхам: Бид Лист_1-т байгаа нүднүүдийн байршлыг тогтоохдоо дараах томьёоллыг ашиглана. Бид энэхүү томьёололд =ROW(), =MIN() гэсэн функцүүдийг Array хэлбэрээр ашиглах болно. (Үр дүнг гаргахдаа B2:B15 мужийг сонгоод F2 товч, ctrl+shift+enter).

=ROW($A$2:$A$15)-ROW($A$2)+1

Дээрх томьёо нь Лист_1-н утгууд листны хэд дэхь мөрөнд байгааг илэрхийлсэн томьёолол юм.

Дээрх хоёр томьёоллын үр дүнг доорх зурагт үзүүлэв.

B багананд Лист1-н нүд бүрт утга байгаа эсэхийг шалгасан байна. Тухайн нэг нүдэнд утга агуулагдаж байгаа тохиолдолд YES гэсэн хариултыг өгсөн бол утга агуулагдаагүй тохиолдолд NO гэсэн хариултыг өгчээ. Харин C баганад Лист1-н утгуудыг лист дэх байршлыг тодорхойлсон байна.

3-р алхам: Одоо дээрх хоёр томьёог нэгтгэе. Нэгтгэхдээ эхний томьёонд хоёр дахь томьёог орлуулах замаар нэгтгэнэ. Өөрөөр хэлбэл эхний томьёог бага зэрэг өөрчилж, YES хариултын оронд =ROW($A$2:$A$15)-ROW($A$2)+1 функцийг, NO хариултын оронд "" гэсэн тэмдэглэгээг оруулж өгнө.

Ингэж томьёог нэгтгэснээр Лист_1-н утга агуулагдаж буй нүднүүдийн байршил тодорхой болж ирнэ.

=IF($A$2:$A$15<>"",ROW($A$2:$A$15)-ROW($A$2)+1,"")

Үр дүнг дараах зурагт үзүүлэв.

Дээрх томьёог ашигласнаар утга агуулсан нүднүүдийн байршил тодорхой болсон. Одоо томьёог =SMALL() функцээр өргөтгөн байршилаар нь эрэмблэнэ.

Доорх эрэмблэх томьёоны үр дүнг мужаар нь биш, зөвхөн нэг нүд дээр гаргана. Манай жишээний хувьд E2 нүд дээр гаргая

=SMALL(IF($A$2:$A$15<>"",ROW($A$2:$A$15)-ROW($A$2)+1,""),ROW(A1))

Үр дүнг доорх зурагт үзүүлэв. (Үр дүнг гаргахдаа E2 нүдийг сонгоод F2 товч, ctrl+shift+enter. Тэгээд доош нь Copy & Paste)

4-р алхам: Дээрх хэлбэрээр байршил тодорхой болсон тул тухайн байршилд харгалзах утгуудыг Лист_1-ээс тодорхойлоё. Үүний тулд бид хамгийн сүүлийн томьёог =INDEX() функцээр өргөтгөнө.

=INDEX($A$2:$A$15,SMALL(IF($A$2:$A$15<>"",

ROW($A$2:$A$15)-ROW($A$2)+1,""),ROW(A1)))

Тус томьёог доорх зурагт үзүүлсний дагуу D2 нүдэнд бичээд шууд ctrl+shift+enter коммандаар эхний үр дүнг гаргана. Үүний дараагаар Copy, paste комманд ашиглан D3:D15 мужид бусад үр дүнг гаргана. (Үр дүнг гаргахдаа E2 нүдийг сонгоод F2 товч, ctrl+shift+enter. Тэгээд доош нь Copy & Paste)

эрэв та #NUM! гэсэн алдааны утгыг харахыг хүсэхгүй бол =IFERROR() гэсэн функцээр дээрх томьёог өргөтгөж болно. Үүний улмаас таны бичсэн томьёо чинь л урт болохоос өөр сөрөг үр дагавар байхгүй.

=IFERROR(INDEX($A$2:$A$15,SMALL(IF($A$2:$A$15<>"",

ROW($A$2:$A$15)-ROW($A$2)+1,""),ROW(A2))),"")

За ингээд Өгөгдсөн лист дэхь хоосон нүдийг хэрхэн арилгах тухай томьёолол бэлэн боллоо. Энд нэг зүйлийг анхааруулж хэлэхэд бүх томьёо Array хэлбэртэй байгаа гэдийг санаарай.

Бусадтай хуваалцах

Зар сурталчилгаа