Microsoft Excel-ის 10 ძირითადი ფუნქცია მონაცემთა ანალიზისთვის

1. შეერთება

=CONCATENATE არის მონაცემთა ანალიზის ერთ-ერთი ყველაზე მნიშვნელოვანი ფუნქცია, რადგან ის საშუალებას გაძლევთ დააკავშიროთ ტექსტი, რიცხვები, თარიღები და ა.შ. მრავალი უჯრედიდან ერთში. ფუნქცია განსაკუთრებით სასარგებლოა სხვადასხვა უჯრედებიდან მონაცემების ერთ უჯრედში გაერთიანებისთვის. მაგალითად, ის მოსახერხებელია მარკეტინგული კამპანიებისთვის თვალთვალის პარამეტრების შესაქმნელად, API მოთხოვნების შესაქმნელად, ტექსტის დამატება რიცხვის ფორმატში და რამდენიმე სხვა რამისთვის.

ზემოთ მოყვანილ მაგალითში მე მინდოდა თვე და გაყიდვები ერთად ერთ სვეტში. ამისთვის გამოვიყენე ფორმულა =CONCATENATE(A2, B2) C2 უჯრედში, რომ მივიღო იან $700 შედეგი.

ფორმულა: =შეერთება(უჯრედები, რომელთა გაერთიანება გსურთ)

2. LEN

=LEN არის კიდევ ერთი მოსახერხებელი ფუნქცია მონაცემთა ანალიზისთვის, რომელიც არსებითად აჩვენებს სიმბოლოების რაოდენობას ნებისმიერ მოცემულ უჯრედში. ფუნქცია უპირატესად გამოსაყენებელია სათაურის ტეგების ან აღწერების შექმნისას, რომლებსაც აქვთ სიმბოლოების ლიმიტი. ის ასევე შეიძლება სასარგებლო იყოს, როდესაც ცდილობთ გაარკვიოთ განსხვავებები სხვადასხვა უნიკალურ იდენტიფიკატორებს შორის, რომლებიც ხშირად საკმაოდ გრძელია და არა სწორი თანმიმდევრობით.

ზემოთ მოყვანილ მაგალითში მსურდა დათვლა ნახვების რაოდენობაზე, რომლებსაც ყოველთვიურად ვიღებდი. ამისთვის გამოვიყენე ფორმულა =LEN(C2) უჯრედში D2, რომ მივიღო შედეგი 5.

ფორმულა: =LEN(უჯრედი)

3. VLOOKUP

=VLOOKUP ალბათ ერთ-ერთი ყველაზე ცნობადი ფუნქციაა ყველასთვის, ვინც იცნობს მონაცემთა ანალიზს. მისი გამოყენება შეგიძლიათ ცხრილიდან მონაცემების შესატყვისად შეყვანის მნიშვნელობით. ფუნქცია გთავაზობთ დამთხვევის ორ რეჟიმს - ზუსტ და მიახლოებულს, რომელიც კონტროლდება საძიებო დიაპაზონით. თუ დიაპაზონს დააყენებთ FALSE-ზე, ის ეძებს ზუსტ შესატყვისს, მაგრამ თუ დააყენებთ TRUE-ზე, ის ეძებს სავარაუდო შესატყვისს.

ზემოთ მოყვანილ მაგალითში მინდოდა მომენახა ნახვების რაოდენობა კონკრეტულ თვეში. ამისთვის გამოვიყენე ფორმულა =VLOOKUP("Jun", A2:C13, 3) G4 უჯრედში და შედეგად მივიღე 74992. აქ "Jun" არის საძიებო მნიშვნელობა, A2:C13 არის ცხრილის მასივი, რომელშიც ვეძებ "Jun" და 3 არის სვეტის ნომერი, რომელშიც ფორმულა იპოვის ივნისის შესაბამის ხედებს.

ამ ფუნქციის გამოყენების ერთადერთი მინუსი არის ის, რომ ის მუშაობს მხოლოდ სვეტებად დალაგებულ მონაცემებთან, აქედან გამომდინარე სახელწოდება — ვერტიკალური ძიება. ასე რომ, თუ თქვენ გაქვთ თქვენი მონაცემები რიგებად დალაგებული, ჯერ დაგჭირდებათ რიგების გადატანა სვეტებად.

ფორმულა: =VLOOKUP(საძიებელი_მნიშვნელობა, ცხრილის_მასივი, col_index_num, [range_lookup])

4. INDEX/MATCH

VLOOKUP ფუნქციის მსგავსად, INDEX და MATCH ფუნქცია გამოდგება შეყვანის მნიშვნელობის საფუძველზე კონკრეტული მონაცემების მოსაძიებლად. INDEX და MATCH, როდესაც გამოიყენება ერთად, შეუძლიათ გადალახონ VLOOKUP-ის შეზღუდვები არასწორი შედეგების მიწოდების შესახებ (თუ არ ხართ ფრთხილად). ასე რომ, როდესაც თქვენ დააკავშირებთ ამ ორ ფუნქციას, მათ შეუძლიათ ზუსტად განსაზღვრონ მონაცემთა მითითება და მოძებნონ მნიშვნელობა ერთი განზომილების მასივში. ეს აბრუნებს მონაცემთა კოორდინატებს რიცხვად.

ზემოთ მოყვანილ მაგალითში მინდოდა მომენახა ნახვების რაოდენობა იანვარში. ამისთვის გამოვიყენე ფორმულა =INDEX (A2:C13, MATCH("Jan", A2:A13,0), 3). აქ, A2:C13 არის მონაცემთა სვეტი, რომელიც მინდა დააბრუნოს ფორმულა, "Jan" არის მნიშვნელობა, რომლის შესატყვისი მინდა, A2:A13 არის სვეტი, რომელშიც ფორმულა იპოვის "იანს" და 0 ნიშნავს, რომ მსურს ფორმულამ იპოვოს ზუსტი შესატყვისი ღირებულება.

თუ გსურთ იპოვოთ სავარაუდო შესატყვისი, თქვენ უნდა შეცვალოთ 0 1-ით ან -1-ით. ასე რომ 1 იპოვის უდიდეს მნიშვნელობას ნაკლები ან ტოლი საძიებო მნიშვნელობაზე და -1 იპოვის უმცირეს მნიშვნელობას ნაკლები ან ტოლი საძიებო მნიშვნელობაზე. გაითვალისწინეთ, რომ თუ არ იყენებთ 0, 1 ან -1, ფორმულა გამოიყენებს 1, by.

ახლა, თუ არ გსურთ თვის სახელის მყარი კოდირება, შეგიძლიათ შეცვალოთ იგი უჯრედის ნომრით. ასე რომ, ჩვენ შეგვიძლია შევცვალოთ "Jan" ზემოთ ნახსენებ ფორმულაში F3 ან A2-ით, რომ მივიღოთ იგივე შედეგი.

ფორმულა: =INDEX(მონაცემთა სვეტი, რომლის დაბრუნებაც გსურთ, MATCH (საერთო მონაცემთა წერტილი, რომლის შედარებასაც ცდილობთ, სხვა მონაცემთა წყაროს სვეტი, რომელსაც აქვს მონაცემთა საერთო წერტილი, 0))

5. MINIFS/MAXIFS

=MINIFS და =MAXIFS ძალიან ჰგავს =MIN და =MAX ფუნქციებს, გარდა იმისა, რომ ისინი საშუალებას გაძლევთ აიღოთ მნიშვნელობების მინიმალური/მაქსიმალური ნაკრები და შეესაბამოთ ისინი ასევე კონკრეტულ კრიტერიუმებს. ასე რომ, არსებითად, ფუნქცია ეძებს მინიმალურ/მაქსიმალურ მნიშვნელობებს და ემთხვევა მას შეყვანის კრიტერიუმებთან.

ზემოთ მოყვანილ მაგალითში მინდოდა მეპოვა მინიმალური ქულები მოსწავლის სქესის მიხედვით. ამისთვის გამოვიყენე ფორმულა =MINIFS (C2:C10, B2:B10, "M") და მივიღე შედეგი 27. აქ C2:C10 არის სვეტი, რომელშიც ფორმულა ეძებს ქულებს, B2:B10 არის სვეტი, რომელშიც ფორმულა მოძებნის კრიტერიუმებს (სქესი) და "M" არის კრიტერიუმები.

ანალოგიურად, მაქსიმალური ქულების მისაღებად გამოვიყენე ფორმულა =MAXIFS(C2:C10, B2:B10, "M") და მივიღე შედეგი 100.

ფორმულა MINIFS-ისთვის: =MINIFS(მინ_დიაპაზონი, კრიტერიუმების_დიაპაზონი1, კრიტერიუმები1,...)

MAXIFS-ის ფორმულა: =MAXIFS(მაქსიმალური_დიაპაზონი, კრიტერიუმების_დიაპაზონი1, კრიტერიუმები1,...)

6. AVERAGEIFS

=AVERAGEIFS ფუნქცია საშუალებას გაძლევთ იპოვოთ საშუალო მონაცემები კონკრეტული ნაკრებისთვის ერთი ან მეტი კრიტერიუმის საფუძველზე. ამ ფუნქციის გამოყენებისას უნდა გახსოვდეთ, რომ თითოეული კრიტერიუმი და საშუალო დიაპაზონი შეიძლება განსხვავებული იყოს. თუმცა, =AVERAGEIF ფუნქციაში, როგორც კრიტერიუმების დიაპაზონი, ასევე ჯამის დიაპაზონი უნდა იყოს იგივე ზომის დიაპაზონი. შეამჩნიეთ განსხვავება მხოლობით და მრავლობით რიცხვში ამ ფუნქციებს შორის? ჰოდა, სწორედ აქ უნდა იყოთ ფრთხილად.

ამ მაგალითში მინდოდა მეპოვა საშუალო ქულა სტუდენტების სქესის მიხედვით. ამისთვის გამოვიყენე ფორმულა, =AVERAGEIFS(C2:C10, B2:B10, "M") და შედეგად მივიღე 56.8. აქ C2:C10 არის დიაპაზონი, რომელშიც ფორმულა მოძებნის საშუალოს, B2:B10 არის კრიტერიუმების დიაპაზონი და "M" არის კრიტერიუმები.

ფორმულა: =AVERAGEIFS(საშუალო_დიაპაზონი, კრიტერიუმები_დიაპაზონი1, კრიტერიუმები1,...)

7. COUNTIFS

ახლა, თუ გსურთ დათვალოთ იმ შემთხვევების რაოდენობა, რომლებიც მონაცემთა ნაკრები აკმაყოფილებს კონკრეტულ კრიტერიუმებს, თქვენ უნდა გამოიყენოთ =COUNTIFS ფუნქცია. ეს ფუნქცია საშუალებას გაძლევთ დაუმატოთ უსაზღვრო კრიტერიუმები თქვენს შეკითხვას და ამით უმარტივეს გზას გახდის შეყვანის კრიტერიუმების საფუძველზე დათვლის პოვნა.

ამ მაგალითში მინდოდა მეპოვა იმ სტუდენტების რაოდენობა, რომლებმაც მიიღეს გამსვლელი ქულა (ე.ი. >=40). ამისთვის გამოვიყენე ფორმულა =COUNTIFS(B2:B10, "M", C2:C10, ">=40"). აქ, B2:B10 არის დიაპაზონი, რომელშიც ფორმულა მოძებნის პირველ კრიტერიუმებს (სქესი), "M" არის პირველი კრიტერიუმი, C2:C10 არის დიაპაზონი, რომელშიც ფორმულა მოძებნის მეორე კრიტერიუმს (ნიშნებს), ხოლო ">=40" არის მეორე. კრიტერიუმები.

ფორმულა: =COUNTIFS(კრიტერიუმების_დიაპაზონი1, კრიტერიუმები1,...)

8. SUMPRODUCT

=SUMPRODUCT ფუნქცია გეხმარებათ ერთად გავამრავლოთ დიაპაზონები ან მასივები და შემდეგ დააბრუნოთ პროდუქტების ჯამი. ეს საკმაოდ მრავალმხრივი ფუნქციაა და შეიძლება გამოყენებულ იქნას მასივების დასათვლელად და შესაჯამებლად, როგორიცაა COUNTIFS ან SUMIFS, მაგრამ დამატებითი მოქნილობით. თქვენ ასევე შეგიძლიათ გამოიყენოთ სხვა ფუნქციები SUMPRODUCT-ში მისი ფუნქციონირების კიდევ უფრო გასაფართოებლად.

ამ მაგალითში მინდოდა მეპოვა ყველა გაყიდული პროდუქტის ჯამი. ამისთვის გამოვიყენე ფორმულა =SUMPRODUCT(B2:B8, C2:C8). აქ B2:B8 არის პირველი მასივი (გაყიდული პროდუქციის რაოდენობა) და C2:C8 არის მეორე მასივი (თითოეული პროდუქტის ფასი). შემდეგ ფორმულა ამრავლებს თითოეული გაყიდული პროდუქტის რაოდენობას მის ფასთან და შემდეგ ამატებს ყველაფერს, რომ მიაწოდოს მთლიანი გაყიდვები.

ფორმულა: =SUMPRODUCT(მასივი1, [მასივი2], [მასივი3],...)

9. TRIM

=TRIM ფუნქცია განსაკუთრებით სასარგებლოა, როდესაც მუშაობთ მონაცემთა ნაკრებთან, რომელსაც აქვს რამდენიმე სივრცე ან არასასურველი სიმბოლო. ფუნქცია საშუალებას გაძლევთ მარტივად ამოიღოთ ეს ადგილები ან სიმბოლოები თქვენი მონაცემებიდან, რაც საშუალებას გაძლევთ მიიღოთ ზუსტი შედეგები სხვა ფუნქციების გამოყენებისას.

ამ მაგალითში მინდოდა ამომეღო ყველა დამატებითი სივრცე სიტყვებს Mouse და pad შორის A7-ში. ამისთვის გამოვიყენე ფორმულა =TRIM(A7).

ფორმულამ უბრალოდ წაშალა დამატებითი სივრცეები და მიიტანა შედეგი მაუსის პანელი ერთი სივრცით.

ფორმულა: =TRIM(ტექსტი)

10. იპოვეთ/მოძებნეთ

დამრგვალება არის FIND/SEARCH ფუნქციები, რომლებიც დაგეხმარებათ კონკრეტული ტექსტის იზოლირებაში მონაცემთა ნაკრების ფარგლებში. ორივე ფუნქცია საკმაოდ მსგავსია იმით, რასაც აკეთებენ, გარდა ერთი ძირითადი განსხვავებისა - =FIND ფუნქცია აბრუნებს მხოლოდ რეგისტრის მგრძნობიარე შესატყვისებს. იმავდროულად, =SEARCH ფუნქციას არ აქვს ასეთი შეზღუდვები. ეს ფუნქციები განსაკუთრებით სასარგებლოა ანომალიების ან უნიკალური იდენტიფიკატორების ძიებაში.

ამ მაგალითში მინდოდა მეპოვა, რამდენჯერ გამოჩნდებოდა „Gui“ Guiding Tech-ში, რისთვისაც გამოვიყენე ფორმულა =FIND(A2, B2), რომელმაც მიიღო შედეგი 1. ახლა, თუ მსურს გამომეკვლია რამდენჯერ გამოჩნდა „gui“ Guiding Tech-ში, მომიწევდა გამოვიყენო =SEARCH ფორმულა, რადგან ის არ არის რეგისტრირებული.

პოვნის ფორმულა: =იპოვე(იპოვე_ტექსტი, ტექსტის შიგნით, [დაწყების_ ნომერი])

ძიების ფორმულა: =ძებნა(იპოვე_ტექსტი, ტექსტის შიგნით, [დაწყების_ ნომერი])