Bir neçə müqayisə olunan obyekti müqayisə edərkən Excel cədvəlləri, məlumatlar tez-tez sütunlarda təşkil edilir ki, bu obyektlərin xüsusiyyətlərini sətir-sətir müqayisə etmək rahat olsun. Məsələn, avtomobil modelləri, telefonlar, eksperimental və nəzarət qrupları, bir sıra pərakəndə satış şəbəkəsi və s. böyük rəqəmlər strings vizual təhlili etibarlı olmaya bilər. VLOOKUP, INDEX, POISKPOZ (VLOOKUP, INDEX, MATCH) funksiyaları xanalar arasında məlumatları müqayisə etmək üçün əlverişlidir və ümumi şəkil vermir. Bəs sütunların ümumiyyətlə bir-birinə nə qədər oxşar olduğunu necə tapmaq olar? Sütunlar eynidirmi?
Xəritə Sütunları əlavəsi sizə sütunları xəritələməyə və böyük şəkli görməyə imkan verir:
- İki və ya daha çox sütunu bir-biri ilə müqayisə edin
- Sütunları istinad dəyərləri ilə müqayisə edin
- Dəqiq uyğunluq faizini hesablayın
- Nəticəni vizual pivot cədvəlində təqdim edin
Video dili: ingilis. Subtitrlər: Rus, İngilis. (Qeyd: Video əks etdirməyə bilər Ən son yeniləmələr. Aşağıdakı təlimatlardan istifadə edin.)
Excel 2016, 2013, 2010, 2007-də "Sütunları uyğunlaşdır" əlavə edin
Uyğundur: Microsoft Excel 2016 - 2007, masaüstü Office 365 (32-bit və 64-bit).
Əlavə ilə necə işləmək olar:
İki və ya daha çox sütunu bir-biri ilə müqayisə etmək və uyğunluq faizini necə hesablamaq olar
Məhsulun inkişafı nümunəsini nəzərdən keçirin. Tutaq ki, bir neçə hazır prototipi bir-biri ilə müqayisə etməli və onların nə qədər oxşar, fərqli və bəlkə də eyni olduğunu öyrənməlisiniz.
- XLTools panelində "Xəritə Sütunları" düyməsini basın > "Birlikdə Xəritə Sütunlarını" seçin.
- OK > üzərinə klikləyin
Məsləhət:
seçin dönmə cədvəli nəticə > Ekspres Təhlil ikonasına klikləyin > "Rəng Ölçüsü" tətbiq edin.
Nəticənin oxunması: Tip 1 və Tip 3 prototipləri demək olar ki, eynidir, 99% uyğunluq dərəcəsi onların sətir parametrlərinin 99% uyğun olduğunu göstərir. Tip 2 və Tip 4 ən az oxşardır - onların parametrləri yalnız 30% -ə uyğun gəlir.
Sütunları istinad dəyərlərinə necə müqayisə etmək və uyğunluğu hesablamaq
Məhsulun inkişafı nümunəsini nəzərdən keçirin. Tutaq ki, bir neçə hazır prototipi müəyyən bir hədəf standartı ilə müqayisə etməli, həmçinin prototiplərin bu standartlara uyğunluq dərəcəsini hesablamalısınız.
- Müqayisə etmək üçün sütunları seçin.
Məsələn, prototip məlumatları olan sütunlar. - XLTools panelində Xəritə Sütunları düyməsini klikləyin.
- İstinad sütunlarının diapazonuna Xəritə seçin > İstinad sütunlarını seçin.
Məsələn, standartları olan sütunlar. - Əgər belədirsə, "Sütunlarda başlıqlar var" yoxlayın.
- Uyğunluğu faiz olaraq göstərmək üçün "Faiz uyğunluğunu göstər" seçin.
Əks halda, nəticə 1 (tam uyğunluq) və ya 0 (uyğunluq yoxdur) kimi göstəriləcək. - Nəticənin yeni vərəqdə və ya mövcud vərəqdə yerləşdirilməsini müəyyənləşdirin.
- OK > Bitti düyməsini basın, nəticə pivot cədvəlində təqdim olunur.
Məsləhət: nəticəni şərh etməyi asanlaşdırmaq üçün ona müraciət edin şərti formatlaşdırma:
Nəticənin xülasə cədvəlini seçin > Ekspres Təhlil işarəsinə klikləyin > "Rəng Ölçüsü" tətbiq edin.
Nəticənin oxunuşu: Tip 2 prototipi Standart 2 ilə 99% uyğundur, yəni. Onların sətirlərdəki parametrlərinin 99%-i eynidir. Məhsul 5 Standart 3-ə ən yaxındır - onların parametrlərinin 96%-i eynidir. Eyni zamanda, Məhsul 4 üç standartdan hər hansı birinə cavab verməkdən uzaqdır. İndi prototiplərin hər birinin hədəf istinad dəyərlərindən nə qədər kənara çıxdığı qənaətinə gələ bilərik.
Match Columns əlavəsi hansı tapşırıqları yerinə yetirməyə kömək edə bilər?
Əlavə xanaları sətir-sətir skan edir və sütunlardakı eyni dəyərlərin faizini hesablayır. XLTools "Match Columns" xanalardakı dəyərlərin ümumi müqayisəsi üçün uyğun deyil - dublikatları və ya unikal dəyərləri tapmaq üçün nəzərdə tutulmayıb.
Xəritə Sütunları əlavəsinin fərqli məqsədi var. Onun əsas vəzifəsi, ümumiyyətlə, məlumat dəstlərinin (sütunların) oxşar və ya fərqli olduğunu öyrənməkdir. Əlavə, məsələn, makro səviyyədə daha geniş baxmaq lazım olduqda, böyük miqdarda məlumatın təhlilinə kömək edir. kimi suallara cavab verin:
- Eksperimental qrupların performansı nə qədər oxşardır
- Təcrübə və nəzarət qruplarının nəticələri nə qədər oxşardır
- Eyni kateqoriyadan olan bir neçə məhsul nə qədər oxşar / fərqlidir
- İşçilərin KPI göstəriciləri planlaşdırılan göstəricilərə nə qədər yaxındır?
- Bir neçə pərakəndə satış mağazasının performansı nə qədər oxşardır və s.
Əgər siz böyük cədvəl sənədləri (çoxlu verilənlər/sütunlar) ilə işləyirsinizsə, bütün məlumatların düzgünlüyünə/müvafiqliyinə nəzarət etmək çox çətindir. Buna görə də, təkrarı aşkar etmək üçün Excel sənədində iki və ya daha çox sütunu təhlil etmək çox vaxt lazımdır. İstifadəçinin proqramın bütün funksionallığı haqqında məlumatı yoxdursa, məntiqi olaraq bir sual yarana bilər: excel-də iki sütunu necə müqayisə etmək olar?
Cavab çoxdan bu proqramın tərtibatçıları tərəfindən düşünülmüşdür, onlar əvvəlcə məlumatı müqayisə etməyə kömək edən əmrlər qoyurlar. Ümumiyyətlə, bu məsələnin dərinliyinə getsəniz, fərdi makroslar və düsturlar yazmaq da daxil olmaqla, təxminən onlarla müxtəlif yol tapa bilərsiniz. Amma təcrübə göstərir ki, ortaya çıxan müqayisə ehtiyaclarının öhdəsindən gəlmək üçün üç və ya dörd etibarlı üsul bilmək kifayətdir.
Matçlar üçün Excel-də iki sütunu necə müqayisə etmək olar
Excel-də məlumatlar adətən sətirlər arasında, sütunlar arasında, etalon kimi verilən dəyərlə müqayisə edilir. Sütunları müqayisə etmək zərurəti yaranarsa, siz daxili funksionallıqdan, yəni Match və If hərəkətlərindən istifadə edə bilərsiniz. Və sizə lazım olan tək şey "buraxılışın" yeddinci ilindən tez olmayan Exceldir.
"Təsadüf" funksiyası ilə başlayırıq". Məsələn, müqayisə edilən məlumatlar C3 və B3 ünvanları olan sütunlardadır. Müqayisə nəticəsi hüceyrəyə yerləşdirilməlidir, məsələn, D3. Bu xananı sıxırıq, “formulalar” menyu kataloquna daxil oluruq, “funksiyalar kitabxanası” sətrini tapırıq, açılan siyahıda yerləşdirilmiş funksiyaları açır, “mətn” sözünü tapırıq və “Təsadüf” düyməsini sıxırıq.
Bir anda ekranda görəcəksiniz yeni forma, burada yalnız iki sahə olacaq: "mətn bir", "mətn iki". Onları doldurmalısınız, sadəcə müqayisə edilmiş sütunların ünvanlarını (C3, B3), sonra adi "OK" düyməsini basın. Nəticədə "True" / "False" sözləri ilə nəticəni görəcəksiniz. Prinsipcə, hətta təcrübəsiz bir istifadəçi üçün heç bir şey xüsusilə mürəkkəb deyil! Ancaq bu yeganə üsuldan uzaqdır. Gəlin "Əgər" funksiyasını parçalayaq.
"Əgər" istifadə edərək uyğunluqlar üçün Excel-də iki sütunu müqayisə etmək imkanıəməliyyatdan sonra sonunda göstəriləcək dəyərləri daxil etməyə imkan verir. Kursor daxil edilmənin aparılacağı xanaya yerləşdirilir, "funksiyalar kitabxanası" menyu kataloqu açılır, açılan siyahıda "məntiqi" sətri seçilir və içindəki ilk mövqe "" ilə tutulur. Əgər" əmri. Biz onu seçirik.
Daha sonra əsaslandırılmış doldurma forması çıxarılır. "Məntiq_ifadəsi" funksiyanın özünün formalaşdırılmasıdır. Bizim vəziyyətimizdə bu, iki sütunun müqayisəsidir, ona görə də "B3 \u003d C3" (və ya sütun ünvanlarınızı) daxil edirik. Əlavə sahələr "əgər doğrudursa dəyər", "yalan_əgər dəyər". Burada müsbət/mənfi nəticəyə uyğun gələn məlumatları (etiketlər/sözlər/rəqəmlər) daxil etməlisiniz. Doldurduqdan sonra, həmişəki kimi, "ok" düyməsini basın. Nəticə ilə tanış olaq.
İki sütunun sətir-sətir təhlilini aparmaq istəyirsinizsə, üçüncü sütunda yuxarıda müzakirə olunan hər hansı bir funksiyanı (“əgər” və ya “təsadüf”) qoyuruq. Onun hərəkəti doldurulmuş sütunların bütün hündürlüyünə qədər uzanır. Sonra, üçüncü sütunu seçin, "əsas" sekmesini vurun, görünən qrupda "üslublar" sözünü axtarın. "Sütun/xana seçim qaydaları" açılacaq.
Onlarda "bərabərdir" əmrini seçməlisiniz, sonra birinci sütunu vurun və "Enter" düyməsini basın. Nəticədə, uyğun nəticələrin olduğu sütunların "rənglənməsi" ortaya çıxır. Və sizə lazım olan məlumatları dərhal görəcəksiniz. Bundan əlavə, "Excel-də iki sütunun dəyərlərini necə müqayisə etmək olar" mövzusunun təhlilində Excel-də şərti formatlaşdırma kimi bir üsula keçək.
Excel: şərti formatlaşdırma
Şərti tipin formatlanması sizə nəinki iki müxtəlif sütunu/xananı/sətiri müqayisə etməyə, həm də verilmiş rənglə (qırmızı) onlarda müxtəlif məlumatları vurğulamağa imkan verəcək. Yəni biz təsadüflər yox, fərqlər axtarırıq. Bunu əldə etmək üçün belə hərəkət edirik. Lazımi sütunları seçirik, adlarına toxunmadan, "əsas" menyu qovluğuna gedirik, orada "üslublar" alt bölməsini axtarırıq.
O, "şərti formatlaşdırma" sətrini ehtiva edəcəkdir. Bunun üzərinə klikləməklə, "qayda yaratmaq" funksiyasına ehtiyac duyduğumuz siyahı əldə edirik. Növbəti addım: "format" sətirində = $ A2 düsturunda sürmək lazımdır<>$B2. Bu düstur Excel-ə tam olaraq nəyə ehtiyacımız olduğunu anlamağa kömək edəcək, yəni A sütununun B sütununun dəyərlərinə bərabər olmayan bütün dəyərlərini qırmızı rənglə rəngləməyə kömək edəcək. HLOOKUP / VLOOKUP kimi strukturların iştirakına. Bu düsturlar üfüqi/şaquli dəyər axtarışlarına aiddir. Bu üsulu daha ətraflı nəzərdən keçirək.
HLOOKUP və VLOOKUP
Bu iki düstur üfüqi/şaquli olaraq məlumatları axtarmağa imkan verir. Yəni H üfüqi, V isə şaquli. Müqayisə ediləcək məlumatlar müqayisə edilən dəyərlərin yerləşdiyi yerə nisbətən sol sütundadırsa, VLOOKUP konstruksiyasından istifadə edin. Lakin müqayisə üçün məlumatlar istinad dəyərlərinin göstərildiyi sütundan cədvəlin yuxarı hissəsində üfüqi olaraq yerləşirsə, HLOOKUP konstruksiyasından istifadə edirik.
Verilənləri ikiyə necə müqayisə edəcəyinizi başa düşmək üçün excel sütunlarışaquli olaraq aşağıdakı tam düsturdan istifadə edilməlidir: axtarış_dəyəri,cədvəl_massivi, col_index_num,aralıq_axtarı.
Tapılacaq dəyər "lookup_value" kimi qeyd olunur. Axtarış sütunları "cədvəl massivi" kimi idarə olunur. Sütun nömrəsi "sol_index_num" kimi göstərilməlidir. Üstəlik, bu, dəyəri üst-üstə düşən və geri qaytarılmalı/düzəliş edilməli olan sütundur. Burada "aralıq axtarışı" əmri əlavə bir əmr kimi çıxış edir. Bu, dəyərin dəqiq və ya təxmini edilməsinin lazım olduğunu göstərə bilər.
Bu əmr göstərilməyibsə, hər iki növ üçün dəyərlər qaytarılacaq. HLOOKUP düsturu tam olaraq belə görünür: axtarış_dəyəri,cədvəl_massivi, sətir_index_num,aralıq_axtarı. Onunla işləmək yuxarıda təsvir edilənlə demək olar ki, eynidir. Düzdür, burada bir istisna var. Bu, dəyərləri qaytarılacaq sətri təyin edən sətirin indeksidir. Yuxarıda göstərilən üsulların hamısını aydın şəkildə tətbiq etməyi öyrənsəniz, aydın olur: işləmək üçün daha rahat və universal proqram yoxdur. böyük miqdar data fərqli növlər Exceldən daha çox. Excel-də iki sütunu müqayisə etmək işin yalnız yarısıdır. Axı, əldə edilən dəyərlərlə başqa bir şey etmək lazımdır. Yəni tapılan uyğunluqlar hələ də hansısa şəkildə işlənməlidir.
Dublikat dəyərləri necə idarə etmək olar
Beləliklə, birinci sütunda tapılan, ikinci sütunda tamamilə təkrarlanan rəqəmlər var. Aydındır ki, təkrarları əl ilə düzəltmək çox qiymətli vaxt aparan qeyri-real işdir. Buna görə də, avtomatik düzəliş üçün hazır texnikadan istifadə etməlisiniz.
Bunun işləməsi üçün əvvəlcə sütunlar yoxdursa, onları adlandırmalısınız. Kursoru ilk sətirə düzəldirik, sağ klikləyin, görünən menyuda "insert" seçin. Tutaq ki, başlıqlar "ad" və "dublikat"dır. Sonra, "tarix" qovluğuna ehtiyacınız var, içərisində - "filtr". "Dublikat"ın yanındakı kiçik oxa klikləyin və bütün "quşları" siyahıdan çıxarın. İndi OK düyməsini basın və A sütununun dublikat dəyərləri görünəcək.
Sonra, bu xanaları seçirik, çünki Excel-də yalnız iki sütundakı mətni müqayisə etmək deyil, həm də dublikatları silmək lazım idi. Seçildi, sağ kliklədi, "sətir sil" seçildi, "ok" düyməsini basdı və dəyərlərə uyğun olmayan bir cədvəl əldə etdi. Metod sütunlar eyni səhifədə, yəni bitişik olduqda işləyir.
Beləliklə, Excel-də iki sütunu müqayisə etməyin bir neçə yolunu təhlil etdik. Mən sizə qəsdən skrinşot göstərmədim, çünki onlarda çaş-baş qalacaqsınız.
AMMA ən məşhurlardan birinin əla videosunu hazırladım və sadə yollar sənəddəki iki sütunu müqayisə edərək və indi əhatə olunan materialı birləşdirmək üçün onunla tanış olmağı təklif edirəm.
Məqalə hələ də sizin üçün faydalıdırsa, sosial mediada paylaşın. şəbəkələr və ya uyğun gördüyünüz ulduzların sayına klikləməklə qiymət verin. Təşəkkür edirəm, bu günə qədər, tezliklə görüşərik.
© Alexander Ivanov.
Çox vaxt vəzifə iki element siyahısını müqayisə etməkdir. Bunu əl ilə etmək çox yorucudur və bundan əlavə, səhvlərin olma ehtimalını istisna etmək olmaz. Excel bu əməliyyatı asanlaşdırır. Bu ipucu şərti formatlaşdırmadan istifadə edən metodu təsvir edir.
Əncirdə. 164.1 adların iki çox sütunlu siyahısına misaldır. Şərti formatlaşdırmanın tətbiqi siyahılardakı fərqləri daha aydın göstərə bilər. Siyahıların bu nümunələrində mətn var, lakin bu üsul rəqəmsal məlumatlarla da işləyir.
Birinci siyahı A2:B31-dir, bu diapazon deyilir köhnə siyahı. İkinci siyahı D2:E31 , diapazon adlanır yeni siyahı. Aralıqlar əmrlə adlandırıldı Düsturlar Müəyyən edilmiş adlar Ad təyin edin. Aralıqların adlandırılması isteğe bağlıdır, lakin bu, onlarla işləməyi asanlaşdırır.
Köhnə siyahıya şərti format əlavə etməklə başlayaq.
- Aralıq hüceyrələrini seçin köhnə siyahı.
- seçin.
- Pəncərədə Formatlaşdırma qaydası yaradın adlı elementi seçin Formuladan istifadə edin
- Bu düsturu pəncərə sahəsinə daxil edin (Şəkil 164.2): =COUNTIF(NewList;A2)=0 .
- düyməsini basın Format və şərt doğru olduqda tətbiq olunacaq formatlaşdırmanı təyin edin. Müxtəlif doldurma rənglərini seçmək yaxşıdır.
- OK düyməsini basın.
Bir sıradakı hüceyrələr yeni siyahı oxşar şərti formatlama düsturundan istifadə edin.
- Aralıq hüceyrələrini seçin yeni siyahı.
- seçin Ev Şərti Formatlaşdırma Qaydası Yarat dialoq qutusunu açmaq üçün Formatlaşdırma qaydası yaradın.
- Pəncərədə Qayda yaradın formatlama seçin element Formuladan istifadə edin formatlaşdırılacaq xanaları müəyyən etmək üçün.
- Bu düsturu pəncərə qutusuna daxil edin: =COUNTIF(OldList;D2)=0 .
- düyməsini basın Format və şərt doğru olduqda tətbiq olunacaq formatlaşdırmanı təyin edin (fərqli doldurma rəngi).
- OK düyməsini basın.
Nəticədə köhnə siyahıda olan, lakin yeni siyahıda olmayan adlar vurğulanacaq (şək. 164.3). Bundan əlavə, yeni siyahıda köhnə siyahıda olmayan adlar da vurğulanır, lakin fərqli rəngdədir. Hər iki siyahıda görünən adlar vurğulanmır.
Hər iki şərti formatlama düsturları funksiyadan istifadə edir COUNTIF. Müəyyən bir dəyərin diapazonda neçə dəfə göründüyünü hesablayır. Formula 0 qaytarırsa, bu, elementin diapazonda olmadığını bildirir. Beləliklə, şərti formatlaşdırma öz üzərinə düşür və xananın fon rəngi dəyişir.
Bu kitabdan bir fəsildir: Michael Girvin. Ctrl+Shift+Enter. Excel-də massiv düsturlarının mənimsənilməsi.
Bir və ya bir neçə şərtə əsaslanan seçimlər. Sıra Excel funksiyaları müqayisə operatorlarından istifadə edin. Məsələn, SUMIF, SUMIFS, COUNTIF, COUNTIFS, AVERAGEIF və AVERAGEIFS. Bu funksiyalar bir və ya bir neçə şərt (meyar) əsasında seçimlər edir. Problem ondadır ki, bu funksiyalar yalnız əlavə edə, saya və ortalaya bilir. Və axtarışa şərtlər qoymaq istəyirsinizsə, məsələn, maksimum dəyər və ya standart sapma? Bu hallarda, daxili funksiya olmadığı üçün, bir massiv düsturu icad etməlisiniz. Çox vaxt bu, massiv müqayisə operatorunun istifadəsi ilə əlaqədardır. Bu fəsildəki ilk nümunə bir şərtlə minimum dəyərin necə hesablanacağını göstərir.
Bir şərtə cavab verən massivin elementlərini seçmək üçün IF funksiyasından istifadə edək. Əncirdə. 4.1 sol cədvəldə şəhərlərin adları olan bir sütun və vaxt sütunu var. Hər bir şəhər üçün minimum vaxtı tapmaq və bu dəyəri düzgün cədvəlin müvafiq xanasına yerləşdirmək tələb olunur. Seçim şərti şəhərin adıdır. MIN funksiyasından istifadə etsəniz, B sütununun minimum dəyərini tapa bilərsiniz. Bəs siz yalnız Oklendə aid olan nömrələri necə seçə bilərsiniz? Düsturları sütunun aşağısına necə köçürmək olar? Excel-in daxili MINESLI funksiyası olmadığı üçün siz IF və MIN funksiyalarını birləşdirən orijinal düstur yazmalısınız.
düyü. 4.1. Düsturun məqsədi: hər bir şəhər üçün minimum vaxt seçmək
Qeydi formatda və ya formatda yükləyin
Şəkildə göstərildiyi kimi. 4.2, MIN funksiyası ilə E3 xanasına düstur daxil etməyə başlamalısınız. Ancaq mübahisə edə bilməzsiniz nömrə 1 B sütununun bütün dəyərləri!? Yalnız Auckland ilə əlaqəli dəyərləri seçmək istəyirsən.
Şəkildə göstərildiyi kimi. 4.3, növbəti addımda IF funksiyasını arqument kimi daxil edin nömrə 1 MIN üçün. MIN içərisinə IF qoyursunuz.
Kursoru arqumentin daxil edildiyi nöqtəyə yerləşdirməklə log_ifadəsi funksiyası IF (Şəkil 4.4), siz A3:A8 şəhərlərinin adları ilə diapazonu seçirsiniz və sonra hüceyrə istinadlarını mütləq etmək üçün F4 düyməsini sıxırsınız (məsələn, daha ətraflı məlumat üçün bax). Sonra müqayisə operatorunu, bərabərlik işarəsini yazın. Nəhayət, siz düsturun solundakı xananı seçəcəksiniz, D3, istinadı ona nisbətən buraxacaqsınız. Hazırlanmış şərt A3:A8 diapazonuna baxarkən yalnız Oklendləri seçməyə imkan verəcək.
düyü. 4.4. Arqumentdə massiv operatoru yaradın log_ifadəsi IF funksiyaları
Beləliklə, siz müqayisə operatoru ilə massiv operatoru yaratdınız. Massivin işlənməsi zamanı istənilən vaxt massiv operatoru müqayisə operatorudur, ona görə də onun nəticəsi TRUE və FALSE dəyərlərindən ibarət massiv olacaqdır. Bunu yoxlamaq üçün massivi seçin (bunu etmək üçün alət ipucundakı arqumentə klikləyin log_ifadəsi) və F9 düyməsini basın (Şəkil 4.5). Adətən bir arqumentdən istifadə edirsiniz log_ifadəsi, DOĞRU və ya YANLIŞ qaytarır; burada yaranan massiv çoxlu TRUE və FALSE dəyərlərini qaytaracaq, buna görə də MIN funksiyası yalnız TRUE dəyərinə uyğun gələn şəhərlər üçün minimum rəqəm seçəcək.
düyü. 4.5. TRUE və FALSE dəyərlər massivinə baxmaq üçün alət ipucundakı arqumentə klikləyin log_ifadəsi və F9 düyməsini basın