Excelda ikkita ustunni qanday solishtirish mumkin - Excel ma'lumotlarini taqqoslash usullari

Bu kitobning bir bobi: Maykl Girvin. Ctrl + Shift + Enter. Excelda massiv formulalarini o'zlashtirish.

Bir yoki bir nechta shartlarga asoslangan namunalar.Qator excel funktsiyalari taqqoslash operatorlaridan foydalaning. Masalan, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF va AVERAGEIFS. Ushbu funktsiyalar tanlovlarni bir yoki bir nechta shartlarga (mezonlarga) asoslangan holda amalga oshiradi. Muammo shundaki, bu funktsiyalar faqatgina qo'shish, hisoblash va o'rtacha qiymatlarni olishlari mumkin. Agar qidiruvda maksimal qiymat yoki standart og'ish kabi shartlarni qo'yishni istasangiz nima bo'ladi? Bunday hollarda, o'rnatilgan funktsiya mavjud emasligi sababli, siz qator formulasini ixtiro qilishingiz kerak. Bu ko'pincha massivni taqqoslash operatoridan foydalanish bilan bog'liq. Ushbu bobdagi birinchi misol bitta shart bo'yicha minimal qiymatni qanday hisoblashni ko'rsatadi.

Shartga javob beradigan qator elementlarini tanlash uchun IF funktsiyasidan foydalanamiz. Shakl. 4.1 chap jadvalda shahar nomlari ko'rsatilgan ustun va vaqt ko'rsatilgan ustun mavjud. Har bir shahar uchun minimal vaqtni topish va ushbu qiymatni to'g'ri jadvalning mos keladigan katagiga joylashtirish talab qilinadi. Tanlash sharti - shaharning nomi. Agar siz MIN funktsiyasidan foydalansangiz, B ustuni uchun minimal qiymatni topishingiz mumkin. Ammo faqat Oklendga xos bo'lgan raqamlarni qanday tanlash mumkin? Va formulalarni ustunga qanday qilib nusxalash mumkin? Excelda o'rnatilgan MINESLI funktsiyasi mavjud bo'lmaganligi sababli, IF va MIN funktsiyalarini birlashtirgan asl formulani yozishingiz kerak.

Shakl: 4.1. Formulaning maqsadi: har bir shahar uchun minimal vaqtni tanlang

Formatni yoki formatdagi yozuvni yuklab oling

Shaklda ko'rsatilganidek. 4.2, siz MIN funktsiyasi bilan formulani E3 katakchaga kiritishni boshlashingiz kerak. Ammo siz bahslasha olmaysiz raqam1 B ustunidagi barcha qiymatlar?! Siz faqat Oklendga xos bo'lgan qiymatlarni tanlashni xohlaysiz.

Shaklda ko'rsatilganidek. 4.3, keyingi bosqichda IF funktsiyasini argument sifatida kiriting raqam1 MIN uchun. Agar siz MINni IF ga qo'ygan bo'lsangiz.

Kursorni argument kiritilgan joyga qo'yish orqali log_expression funktsiyasi IF (4.4-rasm), siz shahar nomlari A3: A8 bo'lgan diapazonni tanlaysiz va keyin F4 tugmachasini bosib katakchalarga murojaatlarni mutlaqo qiling (masalan, batafsil ma'lumot uchun). Keyin siz taqqoslash operatorini, teng belgisini yozasiz. Nihoyat, siz formulaning chap tomonidagi katakchani tanlaysiz - D3, unga havolani nisbiy tuting. Shakllangan shart A3: A8 oralig'ini ko'rishda faqat Oklendni tanlashga imkon beradi.


Shakl: 4.4. Argumentlar bo'yicha massiv operatorini yarating log_expression funktsiyalari IF

Shunday qilib siz taqqoslash operatori yordamida massiv operatorini yaratdingiz. Massivni qayta ishlash jarayonida istalgan vaqtda massiv operatori taqqoslash operatori hisoblanadi, shuning uchun uning ishlashi natijasi HAQIQ va FALSE qiymatlari massivi bo'ladi. Buni tekshirish uchun qatorni tanlang (buning uchun argumentdagi asboblar panelini bosing log_expression) va F9 tugmachasini bosing (4.5-rasm). Odatda bitta argumentdan foydalanasiz log_expression, HAQIQ yoki FALSE qaytarish; bu erda, natijada olingan qator bir nechta TRUE va FALSE qiymatlarini qaytaradi, shuning uchun MIN funktsiyasi faqat TRUE qiymatiga mos keladigan shaharlar uchun minimal raqamni tanlaydi.


Shakl: 4.5. TRUE va FALSE qiymatlari qatorini ko'rish uchun asboblar panelidagi argumentni bosing log_expression va F9 tugmachasini bosing

Bizda bitta buyurtma varag'iga nusxalangan ikkita buyurtma jadvallari mavjud. Excelda ikkita jadval ma'lumotlarini taqqoslash va birinchi jadvalda qaysi pozitsiyalar mavjudligini tekshirish kerak, ikkinchisida emas. Har bir hujayraning qiymatini qo'l bilan taqqoslashning foydasi yo'q.

Excelda gugurt uchun ikkita ustunni solishtiring

Excelda ikkita ustunli qiymatlarni taqqoslash qanday amalga oshiriladi? Ushbu muammoni hal qilish uchun foydalanishni tavsiya etamiz shartli formatlashfaqat bitta ustunda joylashgan elementlarni tezda ajratib ko'rsatish uchun. Jadvallar bilan ishchi varaq:

Birinchi qadam ikkala jadvalga ham nomlarni berishdir. Bu qaysi hujayralar diapazoni taqqoslanayotganini tushunishni osonlashtiradi:

  1. FORMULAS vositasini tanlang - Belgilangan ismlar - Ismni tayinlash.
  2. Ko'rsatilgan oynada "Ism:" maydoniga qiymatni kiriting - Table_1.
  3. "Range:" kirish maydoniga sichqonchaning chap tugmachasini bosing va qatorni tanlang: A2: A15. Va OK tugmasini bosing.


Ikkinchi ro'yxat uchun ham xuddi shunday qiling, lekin ism qo'ying - Table_2. Va navbati bilan C2: C15 oralig'ini ko'rsating.

Foydali maslahat! Nom maydonidan foydalanib, oraliq nomlarini tezroq belgilash mumkin. U formulalar satrining chap tomonida joylashgan. Faqat kataklarning diapazonlarini tanlang va nom maydoniga oraliq uchun mos nomni kiriting va Enter tugmasini bosing.

Endi Excelda ikkita ro'yxatni taqqoslash uchun shartli formatlashdan foydalanamiz. Biz quyidagi natijani olishimiz kerak:

Jadval_1da joylashgan, ammo Table_2da bo'lmagan pozitsiyalar yashil rangda ko'rsatiladi. Shu bilan birga, Table_2-da joylashgan, ammo Table_1-da mavjud bo'lmagan joylar ko'k rang bilan belgilanadi.

Excelda ikkita ustun ma'lumotlarini taqqoslash printsipi

Biz ustun hujayralarini formatlash shartlarini aniqlash uchun COUNTIF funktsiyasidan foydalanganmiz. Ushbu misolda ushbu funktsiya ikkinchi argumentning qiymati (masalan, A2) birinchi argument ro'yxatida necha marta paydo bo'lishini tekshiradi (masalan, Table_2). Agar marta soni \u003d 0 bo'lsa, formuladan HAQI keladi. Bunday holda, katakka shartli formatlash variantlarida ko'rsatilgan maxsus format beriladi. Ikkinchi argumentdagi havola nisbiy, ya'ni tanlangan diapazonning barcha kataklari navbat bilan tekshirilishini anglatadi (masalan, A2: A15). Ikkinchi formula ham xuddi shunday ishlaydi. Xuddi shu printsip turli xil o'xshash vazifalarda qo'llanilishi mumkin. Masalan, Excelda ikkita narxni taqqoslash uchun, hatto

Excelda ishlashda har xil qiymatlar ro'yxatlarini taqqoslash juda keng tarqalgan vazifadir. Umumiy qiymatlar diapazonini va ayniqsa qiymatlar ustunlarini taqqoslash uchun shartli formatlash va funktsiyalar kabi standart Excel vositalaridan foydalanish mumkin. Bundan tashqari, siz bunday vazifalarni VBA makroslari va ularga asoslangan Excel uchun qo'shimchalar yordamida hal qilishingiz mumkin.

Umuman diapazonlarni va xususan ustunlarni taqqoslash zarurati Excelda yaratilgan va vaqt o'tishi bilan o'zgarishni talab qiladigan turli xil hisobotlar, ro'yxatlar va ma'lumotlar bazalarini yangilashda paydo bo'ladi.IN internet tarmoqlari ustunlardagi qiymatlarni taqqoslash uchun har xil funktsiyalar (formulalar) va shartli formatlashdan foydalanadigan juda ko'p misollar tasvirlangan. Bunday usullar biron bir sababga ko'ra mos bo'lmaganlar uchun siz ikkala ustunni ham, har qanday qator va ustunlardan iborat qiymatlarning har qanday diapazonini solishtirishga imkon beradigan boshqa vositadan foydalanishingiz mumkin. Excel uchun plagin yordamida har qanday ikkita diapazonni taqqoslashingiz yoki aniqrog'i bitta diapazonning har bir elementini boshqasining har bir elementi bilan taqqoslashingiz va o'zingiz tanlagan moslik va farqlarni topishingiz mumkin.

Ikki diapazondagi qiymatlar uchun Excel taqqoslash qo'shimchasi

Quyida muhokama qilinadigan qo'shimcha har qanday qator va ustunlardan iborat qiymatlarni ikki diapazonda taqqoslash uchun mo'ljallangan. Ushbu qo'shimchada bitta intervaldagi har bir elementni boshqa diapazondagi har bir element bilan taqqoslash, ushbu diapazonlarda qaysi elementlar bir xil va qaysi biri turlicha bo'lishini aniqlash uchun protsedura qo'llaniladi va kataklarni foydalanuvchi tomonidan belgilangan rang bilan to'ldiradi.

Muloqot oynasidan ko'rinib turibdiki, hamma narsa juda oddiy, foydalanuvchi taqqoslangan ikkita diapazonni tanlaydi, nimani qidirishini, har xil qiymatlarni yoki bir-biriga to'g'ri kelishini belgilaydi, rang tanlaydi va dasturni ishga tushiradi. Qo'shimchani ishlatishning yakuniy natijasi tanlangan diapazonlarning qiymatlaridagi farqlarni yoki bir-birining ustiga chiqishini ko'rsatuvchi ma'lum bir rang bilan to'ldirilgan hujayralardir.

Excelda qiymatlarni taqqoslashda bitta nuance mavjud. Raqamlarni matn sifatida formatlash mumkin, bu har doim ham ingl. (). Ya'ni, Excel-dagi raqam raqamli yoki matnli qiymat bo'lishi mumkin va bu ikki qiymat bir-biriga teng emas. Ko'pincha bu hodisa turli xil xatolarni keltirib chiqaradi. Bunday xatolarni istisno qilish uchun sukut bo'yicha yoqilgan "Raqamlarni matn bilan taqqoslash" opsiyasidan foydalaniladi. Ushbu parametrdan foydalanish raqamli qiymatlarni emas, balki ulardan o'zgartirilgan matnni taqqoslash imkonini beradi.

Qo'shimcha quyidagilarga imkon beradi:

1. Sichqonchani bir marta bosish bilan to'g'ridan-to'g'ri Excel asboblar panelidan so'l dialog oynasini oching;

2. # 2 oralig'ida bo'lmagan # 1 oralig'idagi elementlarni toping;

3. # 1 oralig'ida bo'lmagan # 2 diapazonidagi elementlarni toping;

4. # 2 oralig'ida joylashgan # 1 diapazonidagi elementlarni toping;

5. # 1 oralig'ida joylashgan # 2 diapazonidagi elementlarni toping;

6. kerakli qiymatlarga ega kataklar uchun to'qqizta to'ldirish rangidan birini tanlang;

7. "Chegaralarni cheklash" opsiyasi yordamida diapazonlarni tezda tanlang, shu bilan birga butun qatorlar va ustunlarni tanlashingiz mumkin, tanlangan diapazonni ishlatilgangacha qisqartirish avtomatik ravishda amalga oshiriladi;

8. raqamli qiymatlarni taqqoslash o'rniga, "Raqamlarni matn bilan taqqoslash" parametridan foydalanib, matn qiymatlarini taqqoslashdan foydalaning;

9. oraliq katakchalarini hisobga olmasdan, diapazon kataklaridagi qiymatlarni taqqoslash;

10. Diapazon kataklaridagi qiymatlarni har bir narsaga sezgir bo'lmagan holda taqqoslang.

Excel uchun so'l (qo'shimcha) yordamida ikkita ustunni qanday taqqoslash mumkin?

Ustunli taqqoslash - bu o'zboshimchalik bilan diapazonlarni taqqoslashning alohida holati. №1 va # 2 diapazonlarida ikkita ustunni tanlang va siz aniq ustunlarni tanlashingiz mumkin, va sichqoncha yordamida kataklar oralig'ida tanlov doirasini tortib olmaysiz (qulaylik uchun "chegaralarni cheklash" opsiyasi sukut bo'yicha yoqilgan, tanlov diapazondan foydalanib), farqlarni yoki mosliklarni qidirish uchun kerakli harakatni tanlang, katakchalarning to'ldirish rangini tanlang va dasturni ishga tushiring. Quyida siz ikkita ustunda mos keladigan qiymatlarni qidirish natijasini ko'rishingiz mumkin.

Excelda ikkita ma'lumotlar to'plamini taqqoslash qobiliyati ko'pincha katta hajmdagi ma'lumotlarni qayta ishlaydigan va ulkan jadvallar bilan ishlaydigan odamlar uchun foydalidir. Masalan, taqqoslashda ma'lumotlarni kiritish to'g'riligi yoki jadvalga ma'lumotlarni o'z vaqtida kiritishda foydalanish mumkin. Quyidagi maqolada ikkita ustunni Exceldagi ma'lumotlar bilan taqqoslashning bir nechta texnikasi tasvirlangan.

IF shartli operatoridan foydalanish

IF shartli operatoridan foydalanish usuli shundan farq qiladiki, butun massivni emas, balki ikkita ustunni taqqoslash uchun faqat taqqoslash uchun zarur bo'lgan qism ishlatiladi. Quyida amalga oshirish uchun qadamlar keltirilgan bu usul:

Ikkala ustunni taqqoslash uchun ish varag'ining A va B ustunlariga joylashtiring.

C2 katakchaga quyidagi formulani kiriting \u003d IF (ISERROR (SEARCH (A2; $ B $ 2: $ B $ 11; 0)); ""; A2) va uni C11 katagacha kengaytiring. Ushbu formula ketma-ket B ustunidagi A ustunidan har bir elementni qidiradi va agar B ustunida bo'lsa, uning qiymatini qaytaradi.


VLOOKUP almashtirish formulasidan foydalanish

Formulaning printsipi avvalgi uslubga o'xshaydi, farq SEARCH o'rniga. Ushbu usulning o'ziga xos xususiyati, shuningdek, HLP formulasi yordamida ikkita gorizontal massivni taqqoslash qobiliyatidir.

Ikki ustunni A va B ustunlaridagi ma'lumotlar bilan taqqoslash uchun (avvalgi usulga o'xshash), quyidagi formulani \u003d VLOOKUP (A2; $ B $ 2: $ B $ 11; 1; 0) ni C2 katakchaga kiriting va hujayra C11.


Ushbu formula har bir elementni taqqoslangan qatordagi asosiy qatordan ko'rib chiqadi va agar u B ustunida topilgan bo'lsa, uning qiymatini qaytaradi. Aks holda, Excel # N / A xatosini qaytaradi.

VBA so'lidan foydalanish

Ikkala ustunni taqqoslash uchun makroslardan foydalanish jarayonni birlashtirishi va ma'lumotlarni tayyorlash vaqtini qisqartirishi mumkin. Qaysi taqqoslash natijasini ko'rsatish kerakligi haqidagi qaror sizning tasavvurlaringiz va makrolardan foydalanish ko'nikmalaringizga bog'liq. Quyida Microsoft-ning rasmiy veb-saytida chop etilgan texnika keltirilgan.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

Sub Find_Matches ()
Xira solishtiring Variant sifatida diapazon, x Variant sifatida, y Variant sifatida
"CompareRange o'zgaruvchisini taqqoslangan diapazonga tenglashtirish
CompareRange \u003d Range-ni o'rnating ("B1: B11")
"Agar taqqoslanadigan diapazon boshqa varaqda yoki ish daftarida bo'lsa,
"quyidagi sintaksisdan foydalaning
"Set CompareRange \u003d Workbooks (" Book2 "). _
"Ishchi varaqlar (" Sheet2 "). Qator (" B1: B11 ")
"
"Tanlangan diapazondagi har bir elementni har bir element bilan taqqoslash
"o'zgaruvchan CompareRange
Tanlangan har bir x uchun
CompareRange uchun har bir y uchun
Agar x \u003d y Keyin x.Offset (0, 2) \u003d x
Keyingi y
Keyingi x
Yakuniy pastki

Ushbu kodda CompareRange o'zgaruvchisiga taqqoslangan qator bilan diapazon beriladi. Keyin tanlangan diapazondagi har bir elementni ko'rib chiqadigan va uni taqqoslanadigan har bir element bilan taqqoslaydigan pastadir ishlaydi. Agar bir xil qiymatga ega elementlar topilgan bo'lsa, so'l elementning qiymatini S ustuniga kiritadi.

Makrosdan foydalanish uchun ish sahifasiga qayting, asosiy diapazonni tanlang (bizning holatimizda bu A1: A11 kataklar), Alt + F8 tugmachalarini bosing. Ko'rsatilgan dialog oynasida so'lni tanlang Topish_Uchrashuvlarva ijro etish tugmachasini bosing.


Makrosni bajargandan so'ng natija quyidagicha bo'lishi kerak:

So'rov qo'shimchasidan foydalanish

Natija

Shunday qilib, biz Excelda ma'lumotlarni taqqoslashning bir necha usullarini ko'rib chiqdik, bu sizga ba'zi analitik muammolarni hal qilishda yordam beradi va takroriy (yoki noyob) qiymatlarni topishni osonlashtiradi.

Bir nechta taqqoslanadigan narsalarni taqqoslaganda Excel elektron jadvallari, ma'lumotlar ko'pincha ustunlar bilan tartibga solinadi, shunda ushbu ob'ektlarning xususiyatlarini satrlar bilan taqqoslash qulay bo'ladi. Masalan, avtomobil modellari, telefonlar, eksperimental va nazorat guruhlari, bir qator chakana savdo do'konlari va boshqalar. Ko'p sonli liniyalar bilan vizual tahlil ishonchli bo'lishi mumkin emas. VLOOKUP, INDEX, SEARCH (VLOOKUP, INDEX, MATCH) funktsiyalari katakchalar bo'yicha ma'lumotlarni taqqoslash uchun qulaydir va umumiy rasm bermaydi. Qanday qilib ustunlar bir-biriga o'xshashligini qanday aniqlash mumkin? Ustunlar bir xilmi?

Map Columns qo'shimchasi ustunlarni xaritada ko'rish va katta rasmni ko'rish imkonini beradi:

  • Ikki yoki undan ortiq ustunlarni bir-biri bilan taqqoslang
  • Ustunlarni mos yozuvlar qiymatlari bilan taqqoslang
  • Aniq o'yin foizini hisoblang
  • Natijani vizual pivot jadvalida ko'rsating

Video tili: ingliz tili. Subtitrlar: rus, ingliz. (Diqqat: video aks ettirmasligi mumkin oxirgi yangilanishlar... Quyidagi ko'rsatmalardan foydalaning.)

Excelga 2016, 2013, 2010, 2007 yillarda "Muvofiq ustunlar" ni qo'shing

Quyidagilar uchun javob beradi: Microsoft Excel 2016 - 2007, ish stoli Office 365 (32-bit va 64-bit).

Qo'shimcha bilan qanday ishlash kerak:

Ikki yoki undan ortiq ustunlarni qanday qilib bir-biri bilan taqqoslash va o'yin foizini hisoblash

Mahsulot ishlab chiqarish misolini ko'rib chiqing. Siz bir nechta tayyor prototiplarni bir-biri bilan taqqoslashingiz va ularning qanchalik o'xshash, farqli va ehtimol bir xilligini bilib olishingiz kerak deylik.

  1. XLTools asboblar panelidagi Xarita ustunlari tugmachasini bosing\u003e Bir-biriga xarita ustunlarini tanlang.

  3. OK\u003e tugmasini bosing


Maslahat:
Iltimos tanlang xulosa jadvali natija\u003e Ekspress tahlil piktogrammasini bosing\u003e "Rang o'lchovi" ni qo'llang.

Natijani o'qish: 1-toifa va 3-turdagi prototiplar deyarli bir xil, 99% mos kelish darajasi ularning satrlardagi parametrlarining 99% bir xil ekanligini anglatadi. 2-toifa va 4-turi eng kam o'xshashdir - ularning parametrlari atigi 30% ga to'g'ri keladi.

Qanday ustunlarni mos yozuvlar qiymatlari bilan taqqoslash va mos kelish darajasini hisoblash

Mahsulot ishlab chiqarish misolini ko'rib chiqing. Sizga bir nechta tayyor prototiplarni ma'lum maqsadli standart bilan taqqoslash, shuningdek prototiplarning ushbu standartlarga javob berish darajasini hisoblash kerak deylik.

  1. Taqqoslash uchun ustunlarni tanlang.
    Masalan, prototip ma'lumotlariga ega ustunlar.
  2. XLTools asboblar panelidagi Map Columns tugmasini bosing.
  3. Ma'lumotlar ustunlari oralig'iga xarita\u003e Ma'lumot ustunlarini tanlang.
    Masalan, standartlarga ega ustunlar.
  4. Agar shunday bo'lsa, "ustunlar sarlavhalarni o'z ichiga oladi" ni tekshiring.
  5. Moslik foizini foiz sifatida ko'rsatish uchun "Muvofiqlik foizini ko'rsatish" -ni belgilang.
    Aks holda, natija 1 (to'liq o'yin) yoki 0 (mos kelmaydigan) sifatida ko'rsatiladi.
  6. Natija qaerga joylashtirilishi kerakligini ko'rsating: yangi varaqda yoki mavjud varoqda.
  7. OK\u003e Finish tugmachasini bosing, natija burilish jadvalida ko'rsatilgan.


Maslahat: Natijani izohlashni osonlashtirish uchun unga shartli formatlashni qo'llang:
Natijaning burilish jadvalini tanlang\u003e Tezkor tahlil belgisini bosing\u003e "Rang o'lchovi" ni qo'llang.

Natijani o'qish: 2-turdagi prototip 99% standart 2-ga mos keladi, ya'ni. Satrlardagi parametrlarining 99% bir xil. Mahsulot 5 standart 3 ga eng yaqin - ularning parametrlarining 96% bir xil. Shu bilan birga, 4-mahsulot uchta standartning har biriga javob berishdan yiroq. Endi biz prototiplarning har biri maqsadli mos yozuvlar qiymatlaridan qanchalik chetga chiqishini xulosa qilishimiz mumkin.

Match Columns qo'shimchasi qanday vazifalarni bajarishda yordam beradi?

Qo'shimcha kataklarni satrlar bo'yicha skaner qiladi va ustunlardagi bir xil qiymatlarning foizini hisoblab chiqadi. XLTools Match Columns hujayralardagi qiymatlarni normal taqqoslash uchun mos emas - bu dublikatlar yoki noyob qiymatlarni topish uchun mo'ljallanmagan.

Map Columns qo'shimchasi boshqa maqsadga ega. U asosiy vazifa - umuman, ma'lumotlar to'plamlari (ustunlar) qanday o'xshash yoki farq qilishini bilib oling. Qo'shimcha, masalan, kengroq so'l darajasiga qarash kerak bo'lganda katta hajmdagi ma'lumotlarni tahlil qilishga yordam beradi. quyidagi savollarga javob bering:

  • Eksperimental guruhlarning ishlashi qanchalik o'xshash
  • Eksperimental va nazorat guruhlarining natijalari qanchalik o'xshash
  • Bir xil toifadagi bir nechta mahsulot bir-biriga qanchalik o'xshash / farq qiladi
  • Xodimlarning koeffitsientlari rejalashtirilgan ko'rsatkichlarga qanchalik yaqin?
  • Bir nechta chakana do'konlarning ko'rsatkichlari qanchalik o'xshash va hk.


O'qishni tavsiya etamiz