Бид өчигдөр Өгөгдсөн 2 листэнд давхардсан утга хэдэн ширхэг байна бэ? гэсэн нэр бүхий нийтлэл оруулсныг та бүхэн уншсан байх гэж бодож байна. Харин одоо тус нийтлэлийн үргэлжлэл болох “Өгөгдсөн хоёр лист дэхь давхардсан утгыг тодорхойлох” гэсэн нэртэй нийтлэлийг оруулая. Энэхүү нийтлэлээр өгөгдсөн листнүүдэд яг ямар ямар утгууд давхардаж байгааг MS Excel программын стандарт томьёо ашиглан хэрхэн тодорхойлох талаар авч үзэх болно. Энэхүү нийтлэлийн томьёололд MS Excel программын стандарт функцүүд болох =INDEX(), =COUNTIF(), =ROW(), =SMALL(), =MIN() зэрэг функцүүдийг ашиглах болно.
Зиа гол асуудалдаа орцгооё. Бидэнд дараах мэдээлэл өгөгдсөн. Үүнд:
Бид энэхүү өгөгдлийг ӨМНӨХ жишээнд ашигласан болно.
За давхардсан утгыг тодорхойлох зарчимаа ерөнхийд нь тодорхойлоё. Бид С багананд 2 листэнд зэрэг агуулагдаж буй давхардсан утгыг тодорхойлно. Давхардсан утгыг тодорхойлохдоо дараах алхамын дагуу томьёоллыг бичнэ. Үүнд:
- Лист1-н утгуудыг нэг бүрчлэн Лист2-т байгаа эсэхийг шалгана
- Хэрэв Лист1-н утга Лист2-т байвал Лист1-н утгын байршилыг тогтооно. Өөрөөр хэлбэл Лист1-н хэд дэхь мөрөнд байгааг тодорхойлно.
- Эцэст нь Лист1-н байршил тодорхойлогдсон болохоор ямар утга байна гэдийг хялбархаан гаргаж ирэх болно.
Эхний алхамыг хийж гүйцэтгэе. Бид Лист1-н утгууд Лист2-т байгаа эсэхийг шалгахын тулд =COUNTIF() функцийг Array хэлбэрээр ашиглана.
=COUNTIF($B$2:$B$11,$A$2:$A$11)
Дээрх томьёого array хэлбэртэй тул =C2:C11 мужид Лист1-н утгуудыг Лист2-т хэдэн удаа давхардан орсон эсэхийг тодорхойлж гаргана. (Үр дүнг гаргахдаа С2:C11 мужийг сонгоод F2 товч, ctrl+shift+enter)
Хоёр дахь алхамыг хийж гүйцэтгэе. Лист1-н бүх утгуудын лист дэхь байршилыг тодорхойлох томьёог бичие. Өөрөөр хэлбэл Лист1-н утгууд листны хэд дэхь мөрөнд байгааг илэрхийлсэн томьёолол юм. Бид энэхүү томьёололд =ROW(), =MIN() гэсэн функцүүдийг Array хэлбэрээр ашиглах болно. (Үр дүнг гаргахдаа D2:D11 мужийг сонгоод F2 товч, ctrl+shift+enter)
=ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1
Дээрх хоёр томьёоллын үр дүнг доорх зурагт үзүүлэв.
С багананд Лист1-н утгуудыг Лист2-т байгаа эсэхийг шалгасан үр дүн гарсан байна. Лист1-н GG, HH, II, JJ гэсэн утгууд Лист2-т нэг нэг удаа орсон байна. Харин D баганад Лист1-н утгуудыг лист дэх байршлыг тодорхойлсон байна. Манай давхардсан утгууд Лист1-н 7, 8, 9, 10 гэсэн мөрөнд байна.
Одоо дээрх хоёр томьёог =IF() функцээр нэгтгэе. Ингэж нөхцөл зааж нэгтгэдэг учир нь яг давхардсан утгуудын байршлыг тодорхойлох явдал юм.
=IF(COUNTIF($B$2:$B$11,$A$2:$A$11),ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1,””)
Хэрэв Лист1-н утга Лист2-т байвал тус Лист1-н утгын байршилыг тодорхойлно гэсэн агуулгатай томьёолол юм.
Гурав дахь алхам. Нэгэнт давхардсан утгуудын байршил тодорхойлогдсон тул =INDEX() функцийг ашиглан давхардсан утгуудыг тодорхойлоё. Үүний тулд бид дээрх томьёог =INDEX() функцээр өргөтгөнө. Мөн л томьёо Array хэлбэртэй байна.
=INDEX($A$2:$A$11,IF(COUNTIF($B$2:$B$11,$A$2:$A$11),ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1,””))
Дээрх томьёоны үр дүнг E багананд үзүүлэв. (Үр дүнг гаргахдаа E2:E11 мужийг сонгоод F2 товч, ctrl+shift+enter)
Ингээд давхардсан утгууд тодорхойлогдлоо.
Бид дээрх алхамуудыг нэг бүрчлэн хийхгүйгээр зөвхөн С1 нүдэнд ганц томьёо бичээд шууд давхардсан утгуудыг тодорхойлж болно. Үүний тулд бид дээрх томьёонуудыг нэгтгэн, SMALL функцээр сайжруулж болно. Өөрөөр хэлбэл давхардсан утгуудыг заавал тасалтуулахгүйгээр С1, С2, С3 …. гэх мэтчлэн хүснэгтийн эхний хэсэгт шууд үзүүлж болно. Үүний тулд бид SMALL функцээр өргөтгөсөн дараах томьёог ашиглана.
=INDEX($A$2:$A$11,SMALL(IF(COUNTIF($B$2:$B$11,$A$2:$A$11),
ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1,””),ROW(A1)))
Үр дүнг доорх зурагт үзүүлэв. (Үр дүнг гаргахдаа C2 нүдийг сонгоод F2 товч, ctrl+shift+enter. Тэгээд доош нь Copy & Paste)
Хэрэв та #NUM! гэсэн алдааны утгыг харахыг хүсэхгүй бол =IFERROR() гэсэн функцээр дээрх томьёог өргөтгөж болно. Үүний улмаас таны бичсэн томьёо чинь л урт болохоос өөр сөрөг үр дагавар байхгүй.
=IFERROR(INDEX($A$2:$A$11,SMALL(IF(COUNTIF($B$2:$B$11,$A$2:$A$11),
ROW($A$2:$A$11)-MIN(ROW($A$2:$A$11))+1,””),ROW(A1))),””)
Үр дүнг доорх зурагт үзүүлэв. (Үр дүнг гаргахдаа C2 нүдийг сонгоод F2 товч, ctrl+shift+enter. Тэгээд доош нь Copy & Paste)
За ингээд Өгөгдсөн хоёр лист дэхь давхардсан утгыг тодорхойлох томьёолол бэлэн боллоо. Энд нэг зүйлийг анхааруулж хэлэхэд бүх томьёо Array хэлбэртэй байгаа гэдийг санаарай.
Та бүхэнд амжилт хүсье.
Сэтгэгдэл үлдээх