არჩევითი მოთხოვნის არსი არის წყაროს ცხრილიდან სტრიქონების შერჩევა, რომლებიც აკმაყოფილებენ გარკვეულ კრიტერიუმებს (სტანდარტის გამოყენების მსგავსი). მოდით ავირჩიოთ მნიშვნელობები წყაროს ცხრილიდან გამოყენებით. გამოყენებისგან განსხვავებით ( CTRL + SHIFT + Lან მონაცემები / დახარისხება და გაფილტვრა / ფილტრი) შერჩეული სტრიქონები განთავსდება ცალკე ცხრილში.
ამ სტატიაში განვიხილავთ ყველაზე გავრცელებულ შეკითხვებს, მაგალითად: ცხრილის სტრიქონების შერჩევა, რომელთა მნიშვნელობა რიცხვითი სვეტიდან განსაზღვრულ დიაპაზონში (ინტერვალში) შედის; რიგების შერჩევა, რომლის თარიღიც არის გარკვეული პერიოდი; ამოცანები 2 ტექსტური კრიტერიუმებით და სხვა. დავიწყოთ მარტივი კითხვებით.
1. ერთი რიცხვითი კრიტერიუმი (აირჩიეთ ის პროდუქტები, რომელთა ფასი მინიმალურზე მაღალია)
მაგალითი ფაილი, ფურცელი ერთი კრიტერიუმი არის რიცხვი ).
ცალკე ცხრილში აუცილებელია მხოლოდ იმ ჩანაწერების (სტრიქონების) ჩვენება წყარო ცხრილიდან, რომელთა ფასი 25 -ზე მაღალია.
თქვენ შეგიძლიათ მარტივად მოაგვაროთ ეს და შემდგომი ამოცანები დახმარებით. ამისათვის შეარჩიეთ წყარო ცხრილის სათაურები და დააჭირეთ CTRL + SHIFT + L... ჩამოსაშლელი სიის გამოყენებით სათაურში ფასები აირჩიეთ რიცხვის ფილტრები ..., შემდეგ დააყენეთ საჭირო ფილტრაციის პირობები და დააწკაპუნეთ OK.
გამოჩნდება შერჩევის კრიტერიუმების შესაბამისი ჩანაწერები.
კიდევ ერთი მიდგომა არის გამოყენება. არჩეული რიგებისგან განსხვავებით, ისინი განთავსდება ცალკე ცხრილში - ისეთი, როგორიც, მაგალითად, შეიძლება იყოს გაფორმებული წყაროს ცხრილისგან განსხვავებულ სტილში ან განახორციელოს სხვა ცვლილებები.
მოათავსეთ კრიტერიუმი (მინიმალური ფასი) უჯრედში E6 , ცხრილი გაფილტრული მონაცემებისთვის - დიაპაზონში D10: E19 .
ახლა მოდით ავირჩიოთ დიაპაზონი D11: D19 (სვეტი პროდუქტი) და შეიყვანეთ:
ინდექსი (A11: A19;
მცირე (IF ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Მაგივრად შედიდააჭირეთ კლავიატურის მალსახმობას CTRL + SHIFT + ENTER(მაივის ფორმულა იქნება).
E11: E19 (სვეტი ფასი), სადაც ჩვენ შევიყვანთ მსგავსს:
ინდექსი (B11: B19;
მცირე (IF ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
შედეგად, ჩვენ მივიღებთ ახალ ცხრილს, რომელიც შეიცავს მხოლოდ პროდუქტს ფასებით არანაკლებ უჯრედში მითითებულისა E6 .
მიღებული ნიმუშის მოთხოვნის დინამიურობის საჩვენებლად ჩვენ შემოვიღებთ E6 მნიშვნელობა 55. მხოლოდ 2 ჩანაწერი მოხვდება ახალ ცხრილში.
თუ თქვენ დაამატებთ ახალ პროდუქტს ფასი 80 -ით წყაროს ცხრილში, ახალი ჩანაწერი ავტომატურად დაემატება ახალ ცხრილს.
შენიშვნა... თქვენ ასევე შეგიძლიათ გამოიყენოთ და აჩვენოთ გაფილტრული მონაცემები. კონკრეტული ინსტრუმენტის არჩევანი დამოკიდებულია მომხმარებლის წინაშე არსებულ ამოცანებზე.
თუ არ ხარ კომფორტულად გამოყენებისას მასივის ფორმულა, რომელიც აბრუნებს მრავალ მნიშვნელობას, შეგიძლიათ გამოიყენოთ განსხვავებული მიდგომა, რომელიც განიხილება ქვემოთ მოცემულ ნაწილებში: 5. ა, 7, 10 და 11. ამ შემთხვევებში გამოიყენება.
2. ორი რიცხვითი კრიტერიუმი (შეარჩიეთ ის პროდუქცია, რომლის ფასიც დიაპაზონშია)
დაე იყოს საწყისი ცხრილი პროდუქტებისა და ფასების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელირიცხვების დიაპაზონი).
განათავსეთ კრიტერიუმები (ქვედა და ზედა ფასის საზღვრები) დიაპაზონში E5: E6 .
იმათ. თუ საქონლის ფასი ხვდება მითითებულ ინტერვალში, მაშინ ასეთი ჩანაწერი გამოჩნდება ახალი გაფილტრული მონაცემების ცხრილში.
წინა დავალებისგან განსხვავებით, ჩვენ შევქმნით ორს: პროდუქტებს და ფასებს (შეგიძლიათ მათ გარეშე, მაგრამ ისინი მოსახერხებელია ფორმულების წერისას). შესაბამისი ფორმულები უნდა გამოიყურებოდეს სახელების მენეჯერში ( ფორმულები / განსაზღვრული სახელები / სახელების მენეჯერი) შემდეგნაირად (იხ. სურათი ქვემოთ).
ახლა მოდით ავირჩიოთ დიაპაზონი D11: D19 და ჩვენ წარმოგიდგენთ:
ინდექსი (საქონელი;
სულ მცირე (
IF (($ E $ 5<=Цены)*($E$6>= ფასები); ხაზი (ფასები); "");
Მაგივრად შედიდააჭირეთ კლავიატურის მალსახმობას CTRL + SHIFT + ENTER.
ჩვენ შევასრულებთ იგივე მანიპულაციებს დიაპაზონში E11: E19 სადაც ჩვენ შემოგთავაზებთ მსგავსს:
ინდექსი (ფასები;
სულ მცირე (
IF (($ E $ 5<=Цены)*($E$6>= ფასები); ხაზი (ფასები); "");
LINE (ფასები) -LINE ($ B $ 10)) - LINE ($ B $ 10))
შედეგად, ჩვენ მივიღებთ ახალ ცხრილს, რომელიც შეიცავს მხოლოდ პროდუქტებს, რომელთა ფასებიც უჯრედებში მითითებულ ინტერვალშია. E5 და E6 .
მიღებული მოხსენების დინამიზმის საჩვენებლად (შერჩევის მოთხოვნა) შევდივართ E6 მნიშვნელობა 65. წყაროს ცხრილიდან კიდევ ერთი ჩანაწერი დაემატება ახალ ცხრილს, რომელიც დააკმაყოფილებს ახალ კრიტერიუმს.
თუ წყაროს ცხრილს დაამატებთ ახალ პროდუქტს ფასით 25-დან 65-მდე დიაპაზონში, მაშინ ახალ ცხრილში დაემატება ახალი ჩანაწერი.
ნიმუშის ფაილი ასევე შეიცავს მასივის ფორმულებს, რომლებიც ამუშავებენ შეცდომებს, როდესაც ფასის სვეტი შეიცავს შეცდომის მნიშვნელობას, მაგალითად # DIV / 0! (იხ. ფურცელი შეცდომის დამუშავება).
შემდეგი ამოცანები მოგვარებულია მსგავსი გზით, ამიტომ მათ დეტალურად არ განვიხილავთ.
3. ერთი კრიტერიუმი თარიღი (აირჩიეთ ის პროდუქტები, რომელთა მიწოდების თარიღი ემთხვევა მითითებულს)
მაგალითი ფაილი, ფურცელიერთი კრიტერიუმი - თარიღი).
რიგების შესარჩევად გამოიყენება მასივის ფორმულები, მსგავსია ამოცანა 1 (კრიტერიუმის ნაცვლად<= используется =):
=INDEX (A12: A20; SMALL (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
ინდექსი (B12: B20; SMALL (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $11))
4. ორი კრიტერიუმის თარიღი (შეარჩიეთ ის პროდუქცია, რომლის მიწოდების თარიღიც დიაპაზონშია)
დავუშვათ, არის პირველადი ცხრილი საქონლისა და მიწოდების თარიღების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელითარიღის დიაპაზონი).
გაითვალისწინეთ, რომ თარიღი სვეტი არ არის დახარისხებული.
გამოსავალი 1: შეგიძლიათ გამოიყენოთ რიგების გასაფილტრად.
შედით უჯრედში D12 მასივის ფორმულა:
ინდექსი ($ 12: A $ 20;
დიდი (($ E $6<=$B$12:$B$20)*($E$7>= $ B $ 12: $ B $ 20) * (LINE ($ B $ 12: $ B $ 20) -LINE ($ B $ 11));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
შენიშვნა: ფორმულის შეყვანის შემდეგ, ENTER ღილაკის ნაცვლად, დააჭირეთ კლავიშთა კომბინაციას CTRL + SHIFT + ENTER. კლავიატურის ეს მალსახმობი გამოიყენება მასივის ფორმულების შესასვლელად.
დააკოპირეთ მასივის ფორმულა უჯრედების სასურველ რაოდენობამდე. ფორმულა დააბრუნებს მხოლოდ იმ პროდუქტების მნიშვნელობებს, რომლებიც გადაეცა მითითებულ თარიღის დიაპაზონში. დანარჩენი უჯრედები შეიცავს #NUM! შეცდომებს. შეცდომები შიგნით მაგალითი ფაილი (ფურცელი 4 თარიღის დიაპაზონი) .
მსგავსი ფორმულა უნდა იყოს შეტანილი E სვეტის თარიღებისთვის.
საკანში J12 გამოითვლება წყაროს ცხრილის რიგები, რომლებიც აკმაყოფილებს კრიტერიუმებს:
COUNTIFS (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
კრიტერიუმებს აკმაყოფილებს წყაროს ცხრილის სტრიქონები.
გამოსავალი 2: რიგების ასარჩევად შეგიძლიათ გამოიყენოთ მასივის ფორმულები, როგორიცაა Task2 (ე.ი.):
=ინდექსი (A12: A20; SMALL (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20)
ინდექსი (B12: B20; მცირე (IF (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20)
პირველი ფორმულის შესასვლელად შეარჩიეთ უჯრედების დიაპაზონი G12: G20 ... ფორმულის შეყვანის შემდეგ ENTER დაჭერის ნაცვლად დააჭირეთ CTRL + SHIFT + ENTER.
გამოსავალი 3: თუ თარიღი სვეტი დახარისხებულია, მაშინ არ გჭირდებათ მასივის ფორმულების გამოყენება.
პირველ რიგში, თქვენ უნდა გამოთვალოთ სტრიქონების პირველი და ბოლო პოზიციები, რომლებიც აკმაყოფილებენ კრიტერიუმებს. შემდეგ დაბეჭდეთ ხაზები.
ეს მაგალითი კიდევ ერთხელ ნათლად აჩვენებს, თუ რამდენად ადვილია ფორმულების დაწერა.
5. ერთი კრიტერიუმის თარიღი (შეარჩიეთ ის საქონელი, რომლის მიწოდების თარიღი არ არის ადრე / არა უგვიანეს მითითებულისა)
დავუშვათ, არის პირველადი ცხრილი საქონლისა და მიწოდების თარიღების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელი ერთი კრიტერიუმი - თარიღი (არა გვიან) ).
სტრიქონების ასარჩევად, რომელთა თარიღი არ არის ადრე (თარიღის ჩათვლით), გამოიყენეთ მასივის ფორმულა:
=ინდექსი (A12: A20; მცირე (IF ($ E $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
ასევე, მაგალითი ფაილი შეიცავს ფორმულებს პირობებისთვის: არა ადრე (მათ შორის არ არის); არა უგვიანეს (მათ შორის); არა უგვიანეს (არ ჩათვლით).
7. ერთი ტექსტის კრიტერიუმი (აირჩიეთ გარკვეული ტიპის პროდუქტები)
დაე იყოს საწყისი ცხრილი პროდუქტებისა და ფასების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელიერთი კრიტერიუმი - ტექსტი).
8. ორი ტექსტის კრიტერიუმი (აირჩიეთ გარკვეული ტიპის საქონელი, რომელიც მიეწოდება მოცემულ თვეში)
დაე იყოს საწყისი ცხრილი პროდუქტებისა და ფასების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელი 2 კრიტერიუმი - ტექსტი (I) ).
ინდექსი ($ A $ 11: $ A $ 19;
SMALL (IF (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10); 30); LINE (არაპირდაპირი ("A1: A" & ROWS ($ A $ 11: $ A $ 19)))))
გამოხატვა ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19)ადგენს ორივე პირობას (პუნქტი და თვე).
გამოხატვა LINE (არაპირდაპირი ("A1: A" და ROWS ($ A $ 11: $ A $ 19)))ფორმები (1: 2: 3: 4: 5: 6: 7: 8: 9), ე.ი. რიგის ნომრები ცხრილში.
9. ორი ტექსტის კრიტერიუმი (აირჩიეთ გარკვეული ტიპის პროდუქტები)
დაე იყოს საწყისი ცხრილი პროდუქტებისა და ფასების ჩამონათვალით (იხ. მაგალითი ფაილი, ფურცელი2 კრიტერიუმი - ტექსტი (OR)).
მე -7 პრობლემისგან განსხვავებით, შეარჩიეთ რიგები 2 ტიპის საქონლით ().
მასივის ფორმულა გამოიყენება რიგების ასარჩევად:
ინდექსი ($ 11: A $ 19;
LARGE (($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * (ROW ($ A $ 11: $ A $ 19 ) -LINE ($ A $ 10)); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + COUNTIF ($ A $ 11: $ A $ 19; $ E $ 7) -ROWS ($ $ 11: A11) +1))
მდგომარეობა ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)გარანტიას იძლევა, რომ ყვითელი უჯრედებიდან შეირჩევა მხოლოდ განსაზღვრული სახის საქონელი (პროდუქტი 2 და პროდუქტი 3). ამოცანისთვის გამოიყენება + (დამატების) ნიშანი (უნდა დაკმაყოფილდეს მინიმუმ 1 კრიტერიუმი).
ზემოთ მოცემული გამონათქვამი დააბრუნებს მასივს (0: 0: 0: 0: 1: 1: 1: 0: 0). მისი გამრავლება გამოთქმით LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), ე.ი. on (1: 2: 3: 4: 5: 6: 7: 8: 9), ჩვენ ვიღებთ პოზიციების მასივს (ცხრილის რიგის ნომრები), რომელიც აკმაყოფილებს კრიტერიუმებს. ჩვენს შემთხვევაში, ეს იქნება მასივი (0: 0: 0: 0: 5: 6: 7: 0: 0).
როგორც მაგალითი, ჩვენ წარმოგიდგენთ შემდეგ პრობლემის გადაწყვეტას: აირჩიეთ პროდუქტები, რომელთა ფასი დევს გარკვეულ დიაპაზონში და მეორდება მითითებულ რაოდენობაზე ან მეტჯერ.
ავიღოთ საქონლის ტვირთების ცხრილი, როგორც პირველადი.
დავუშვათ, რომ ჩვენ გვაინტერესებს რამდენი და რა საქონელი მიეწოდება 1000 რუბლის ფასად. 2000 რ -მდე (კრიტერიუმი 1). უფრო მეტიც, უნდა იყოს მინიმუმ 3 პარტია ერთი და იგივე ფასით (კრიტერიუმი 2).
გამოსავალი არის მასივის ფორმულა:
მცირე (ROW ($ A $ 14: $ A $ 27) * ($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10); F14 + ($ G $ 8- $ G $ 9))
ეს ფორმულა აბრუნებს ხაზების ნომრებს, რომლებიც აკმაყოფილებს ორივე კრიტერიუმს.
ფორმულა = SUMPRODUCT (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10))ითვლის იმ რიგების რაოდენობას, რომლებიც აკმაყოფილებს კრიტერიუმებს.
11. ჩვენ ვიყენებთ კრიტერიუმის (ნებისმიერი) ან (ყველა) მნიშვნელობას
ვ მაგალითი ფაილი ფურცელზე "11. კრიტერიუმი ნებისმიერი ან (ყველა)"კრიტერიუმის ეს ვარიანტი განხორციელდა.
ამ შემთხვევაში ფორმულა უნდა შეიცავდეს IF () ფუნქციას. თუ მნიშვნელობა (ყველა) არჩეულია, მაშინ ფორმულა გამოიყენება მნიშვნელობების გამოსახატავად ამ კრიტერიუმის გათვალისწინების გარეშე. თუ არჩეულია რაიმე სხვა მნიშვნელობა, მაშინ კრიტერიუმი მუშაობს ჩვეულ რეჟიმში.
IF ($ C $ 8 = "(ყველა)";
მცირე ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $ G $ 7));
SMALL ((ხაზი ($ B $ 13: $ B $ 26) - LINE ($ B $ 12)) * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C $ 26 = $ C $ 8); F13 + ($ G $ 6- $ G $ 7)))
დანარჩენი ფორმულა მსგავსია ზემოთ განხილული.
მონაცემების მიღება
შექმენით ანგარიში ნიმუშის შესახებ ფურცელი 5 -დან სვეტში "თვისებრივი შესრულება, პროცენტი". (ფურცელი 8, ცხრილი 7)
მონაცემების ნიმუშის მისაღებად, თქვენ უნდა შეასრულოთ შემდეგი ნაბიჯები:
განსაზღვრეთ ახალი მასივის ელემენტების რაოდენობა მოცემული მდგომარეობის მიხედვით ცვლადის შეყვანისას InputBox ოპერატორის გამოყენებით
გამოაცხადე და ხელახლა გამოაცხადე ახალი მასივი
შექმენით ახალი მასივი. ამისათვის თქვენ უნდა დააყენოთ ახალი მასივის პირველი ელემენტის ნომერი u = 1. შემდეგ ტარდება ციკლი, რომელშიც შერჩევის პირობა იწერება სვეტისთვის "თვისებრივი შესრულება, პროცენტი". თუ შემოწმების შედეგი მართალია, მაშინ გაანალიზებული მასივის ელემენტი ხდება ახალი მასივის ელემენტი.
ახალი ელემენტის ჩვენება ფურცელ 8-ზე
ქვე ReportSample ()
ფურცლები ("ფურცელი 8"). აირჩიეთ
Dim A () როგორც ვარიანტი
ReDim A (1-დან n1-მდე, 1-დან მეტრამდე)
VVOD "ფურცელი 5", A, n1, მ, 4
C = InputBox ("შეიყვანეთ მდგომარეობა")
ფურცლები ("Sheet8"). უჯრედები (5.11) = C
i = 1-დან n1-მდე
თუ A (მე,
8) >
d = d + 1
ფურცლები ("ფურცელი 8"). უჯრედები (5,10) = დ
Dim B () როგორც ვარიანტი
ReDim B (1 დან d, 1 to m)
i = 1-დან n1-მდე
თუ A (მე,
8)> ფურცლები ("Sheet8"). უჯრედები (5.11) შემდეგ
J = 1 დან მ
B (u, j) = A (i, j)
u = u + 1
I = 1 დან d
J = 1 დან მ
ფურცლები ("ფურცელი 8"). უჯრედები (i + 4, j) = B (i, j)
ნახ. 6 ცხრილის მონაცემები მოპოვების შემდეგ
შერჩევისას ავტომატური მაკრო შექმნა
ჩვენ ჩართავთ მაკრო ჩაწერას. ინსტრუმენტები> მაკრო> ჩაწერის დაწყება> კარგი. გამოჩნდება კვადრატი, სადაც არის ჩაწერის გაჩერების ღილაკი. Sheet5-ზე (ანგარიში) აირჩიეთ ცხრილი სათაურებისა და ჯამების გარეშე, დააკოპირეთ Sheet10-ში (ავტოშერჩევა). აირჩიეთ ცხრილი სათაურების გარეშე და მენიუს პუნქტში აირჩიეთ Data> Filter> AutoFilter> აირჩიეთ პირობა> OK. ჩვენ აღვნიშნავთ სვეტს, რომლითაც ჩვენ დავახარისხებთ. ვასრულებთ მაკროს მუშაობას.
Sub Macro2Sample ()
"Macro2Setup მაკრო
ფურცლები ("ფურცელი 5"). აირჩიეთ
შერჩევა. დააკოპირეთ
ფურცლები ("ფურცელი 9"). აირჩიეთ
ActiveSheet. პასტა
დიაპაზონი ("H5: H17"). აირჩიეთ
განაცხადი. CutCopyMode = მცდარი
შერჩევა. ავტოფილტერი
ActiveSheet. დიაპაზონი ("$ H $ 5: $ H $ 17"). ავტოფილტრის ველი: = 1, კრიტერიუმები 1: = "> 80", _
ოპერატორი: = xlAnd
დიაპაზონი ("G22"). აირჩიეთ
ნახ. 7 ცხრილის მონაცემები ავტომატური შერჩევის შემდეგ
მაქსიმალური და მინიმალური მნიშვნელობის განსაზღვრა
განსაზღვრეთ სვეტების მაქსიმალური და მინიმალური მნიშვნელობები "სულ", "აბსოლუტური შესრულება, პროცენტი.", "თვისებრივი შესრულება" (ცხრილი 9, ფურცელი 10)
მაქსიმალური და მინიმალური მნიშვნელობების დასადგენად, თქვენ უნდა გააკეთოთ შემდეგი:
დააყენეთ მითითების ცვლადი, რომელიც იქნება მიმდინარე მინიმალური (მაქსიმუმი)
მოსახლეობის თითოეული ელემენტი სათითაოდ არის შედარებული არსებულ მინიმუმთან (მაქსიმუმი) და თუ ეს ელემენტი არ აკმაყოფილებს ძებნის პირობებს (მინიმუმის შემთხვევაში, ის უფრო დიდია, ხოლო მაქსიმუმის შემთხვევაში - ნაკლები) , მაშინ საცნობარო მნიშვნელობა იცვლება შედარებული ელემენტის მნიშვნელობით
მიმდინარე მინიმუმის (მაქსიმუმის) ცვლადის ყველა ელემენტის სრული სკანირების შემდეგ აღმოჩნდება ფაქტობრივი მინიმუმი (მაქსიმუმი)
მინიმალური (მაქსიმალური) მნიშვნელობა ნაჩვენებია შესაბამის უჯრედებში
ქვემექსი ()
Dim A () როგორც ვარიანტი
n1 = ფურცლები ("Sheet4"). უჯრედები (5.12)
m = Sheets ("Sheet2"). უჯრედები (5.12)
ReDim A (1-დან n1-მდე, 1-დან მეტრამდე)
VVOD "ფურცელი 5", A, n1, მ, 4
VIVOD "ფურცელი 10", A, n1, მ, 4
VVOD "Sheet10", A, n1, m, 4
j = 3-მდე მ
maxA = 0.00001
minA = 1,000,000
i = 1-დან n1-მდე
თუ A (i, j)> maxA მაშინ
maxA = A (i, j)
თუ A (i, j)< minA Then
minA = A (i, j)
ფურცლები ("ფურცელი10"). უჯრედები (i + 4 + 2, j) = maxA
ფურცლები ("ფურცელი 10"). უჯრედები (i + 4 + 3, j) = minA
მაკრო არის ქმედებების თანმიმდევრობა, რომელიც ჩაწერილია და შენახულია მომავალი მითითებისთვის. შენახული მაკროს დაკვრა შესაძლებელია სპეციალური ბრძანებით. სხვა სიტყვებით რომ ვთქვათ, შეგიძლიათ ჩაწეროთ თქვენი მოქმედებები მაკროში, შეინახოთ იგი და შემდეგ სხვა მომხმარებლებს საშუალება მისცეთ, გაიმეორონ მაკროში შენახული მოქმედებები მარტივი კლავიშის დაჭერით. ეს განსაკუთრებით სასარგებლოა PivotTable ანგარიშების გავრცელებისას.
ვთქვათ, თქვენ გინდათ თქვენს მომხმარებელს მისცეთ PivotTable ანგარიშების დაჯგუფების შესაძლებლობა თვის, კვარტლისა და წლის მიხედვით. ტექნიკურად, დაჯგუფების პროცესი შეიძლება გაკეთდეს ნებისმიერი მომხმარებლის მიერ, მაგრამ ზოგიერთ თქვენს კლიენტს შეიძლება არ გაუჩნდეს ამის გაგება საჭირო. ასეთ შემთხვევაში, თქვენ შეგიძლიათ ჩაწეროთ ერთი მაკრო დაჯგუფებისათვის თვის მიხედვით, მეორე კვარტალში და მესამე წლის მიხედვით. შემდეგ შექმენით სამი ღილაკი - თითო თითოეული მაკროზე. მაშინ თქვენს არა PivotTable მომხმარებელს მხოლოდ უნდა დააჭიროს ღილაკს, რათა სწორად დაჯგუფდეს PivotTable ანგარიში.
PivotTable ანგარიშებში მაკროების გამოყენების მთავარი სარგებელი არის ის, რომ მომხმარებელს საშუალება მიეცეს სწრაფად შეასრულოს შემაჯამებელი ცხრილებიოპერაციები, რომლებსაც ისინი ჩვეულებრივ ვერ ასრულებენ. ეს მნიშვნელოვნად ზრდის მოწოდებული მონაცემების ანალიზის ეფექტურობას.
ჩამოტვირთეთ ჩანაწერი ფორმატში ან გადმოწერეთ მაგალითებით (შიგ Excel ფაილიმაკროებით; პროვაიდერის პოლიტიკა არ გაძლევთ საშუალებას პირდაპირ ატვირთოთ ამ ფორმატის ფაილი საიტზე).
მაკრო ჩაწერა
გადახედეთ ფიგურაში ნაჩვენებ საყრდენ ცხრილს. 1. თქვენ შეგიძლიათ განაახლოთ ეს PivotTable მასში მარჯვენა ღილაკით და არჩევით განაახლეთ... თუ თქვენ ჩაწერეთ მოქმედებები მაკროდ კრებსითი ცხრილის განახლებისას, მაშინ თქვენ ან ნებისმიერ სხვა მომხმარებელს შეუძლია ამ მოქმედებების ხელახლა დაკვრა და მაკროს გაშვების შედეგად კრებსითი ცხრილის განახლება.
ბრინჯი 1. ამ კრებსითი ცხრილის განახლების დროს მოქმედებების ჩაწერა საშუალებას მოგცემთ განაახლოთ მონაცემები მომავალში მაკროს გაშვების შედეგად.
მაკროს ჩაწერის პირველი ნაბიჯი არის დიალოგური ფანჯრის მოწოდება მაკრო ჩაწერა... გადადით ჩანართზე დეველოპერილენტი და დააჭირეთ ღილაკს მაკრო ჩაწერა... (თუ ვერ იპოვით ჩანართს ლენტზე დეველოპერი, აირჩიეთ ჩანართი ფაილიდა დააჭირეთ ღილაკს Პარამეტრები... დიალოგურ ფანჯარაში, რომელიც გამოჩნდება Excel პარამეტრებიაირჩიე კატეგორია ლენტის მორგებადა სიაში მარჯვნივ, მონიშნეთ ყუთი დეველოპერი... შედეგად, ჩანართი გამოჩნდება ლენტზე დეველოპერი.) ალტერნატიული გზადაიწყეთ მაკრო ჩაწერა - დააჭირეთ ღილაკს (სურ. 2).
დიალოგურ ფანჯარაში მაკრო ჩაწერაშეიყვანეთ შემდეგი ინფორმაცია მაკროს შესახებ (სურათი 3):
სახელიმაკრო... სახელი უნდა აღწერდეს მაკროს მიერ შესრულებულ მოქმედებებს. სახელი უნდა დაიწყოს ასოთი ან ხაზგასმით; არ უნდა შეიცავდეს სივრცეებს და სხვა არასწორ სიმბოლოებს; არ უნდა იყოს იგივე, რაც ჩაშენებული Excel სახელი ან სამუშაო წიგნში სხვა ობიექტის სახელი.
კომბინაციაგასაღებები... თქვენ შეგიძლიათ შეიყვანოთ ნებისმიერი ასო ამ ველში. ის გახდება კლავიატურის მალსახმობის ნაწილი, რომელიც გამოყენებული იქნება მაკროს სათამაშოდ. გასაღებების კომბინაცია არჩევითია. ნაგულისხმევად, მხოლოდ Ctrl არის შემოთავაზებული კომბინაციის დასაწყისად. თუ გსურთ, რომ კომბინაცია შეიცავდეს Shift-საც, აკრიფეთ ასო ფანჯარაში Shift კლავიშის დაჭერისას
Შენახვავ... ეს არის ადგილი, სადაც მაკრო ინახება. თუ თქვენ აპირებთ PivotTable ანგარიშის სხვა მომხმარებლებზე გავრცელებას, აირჩიეთ ვარიანტი ესწიგნი... Excel ასევე გაძლევთ საშუალებას შეინახოთ მაკრო ახალი წიგნიან შიგნით პირადი მაკრო წიგნი.
აღწერა... ეს ველი გამოიყენება შექმნილი მაკროს აღწერის შესასვლელად.
ბრინჯი 3. ფანჯრის დაყენება მაკრო ჩაწერა
მას შემდეგ, რაც მაკრო განაახლებს კრებსით ცხრილს, აირჩიეთ სახელი მონაცემთა განახლება... თქვენ ასევე შეგიძლიათ მიანიჭოთ მაკრო კომბინაციას Ctrl გასაღებები+ Shift + Q. გახსოვდეთ, რომ მაკრო შექმნის შემდეგ თქვენ გამოიყენებთ კლავიატურის ამ მალსახმობას მის გასაშვებად. აირჩიეთ ვარიანტი, როგორც ადგილმდებარეობა მაკროის შესანახად Ეს წიგნიდა დააწკაპუნეთ კარგი.
დიალოგში დაჭერის შემდეგ მაკრო ჩაწერაღილაკზე კარგიმაკრო ჩაწერა იწყება. ამ ეტაპზე, ყველა მოქმედება, რომელსაც თქვენ აკეთებთ Excel- ში, ჩაწერილი იქნება.
მარჯვენა ღილაკით PivotTable ცხრილში და აირჩიეთ განაახლეთ(როგორც ნახ. 1-ში, მაგრამ მაკრო ჩაწერის რეჟიმში). საყრდენი ცხრილის განახლების შემდეგ, შეგიძლიათ შეაჩეროთ მაკრო ჩაწერის პროცესი ღილაკის გამოყენებით შეწყვიტე ჩაწერაჩანართები დეველოპერი... ან კვლავ დააწკაპუნეთ ნახატზე ნაჩვენებ ღილაკზე. 2.
ასე რომ თქვენ უბრალოდ ჩაწერეთ თქვენი პირველი მაკრო. ახლა თქვენ შეგიძლიათ შეასრულოთ მაკრო კლავიატურის მალსახმობის გამოყენებით Ctrl + Shift + Q.
მაკრო უსაფრთხოების გაფრთხილება.აღსანიშნავია, რომ მომხმარებლის მიერ მაკროების ჩაწერის შემთხვევაში, ისინი შესრულდება უსაფრთხოების ქვესისტემის მხრიდან ყოველგვარი შეზღუდვის გარეშე. თუმცა განაწილებისას სამუშაო წიგნიმაკროების შემცველი, თქვენ უნდა მიაწოდოთ სხვა მომხმარებლებს შესაძლებლობა, დარწმუნდნენ, რომ სამუშაო ფაილების გახსნისას არ არსებობს რისკი და მაკროების შესრულება არ გამოიწვევს სისტემის ვირუსულ ინფექციას. კერძოდ, დაუყოვნებლივ შეამჩნევთ, რომ ამ თავში გამოყენებული ნიმუშის ფაილი სრულად არ იმუშავებს, თუ კონკრეტულად არ დაუშვებთ Excel-ს მასზე მაკროების გაშვება.
თქვენი მაკროების უსაფრთხოების უმარტივესი გზაა სანდო მდებარეობის შექმნა – საქაღალდე, სადაც განთავსდება მხოლოდ „სანდო“ ვირუსისგან თავისუფალი სამუშაო წიგნები. სანდო მდებარეობა საშუალებას გაძლევთ თქვენ და თქვენს კლიენტებს შეასრულოთ სამუშაო წიგნებში მაკროები უსაფრთხოების ყოველგვარი შეზღუდვის გარეშე (ეს ქცევა გრძელდება მანამ, სანამ სამუშაო წიგნები სანდო საქაღალდეშია).
სანდო მდებარეობის დასაყენებლად მიჰყევით ამ ნაბიჯებს.
აირჩიეთ ლენტის ჩანართი დეველოპერიდა დააჭირეთ ღილაკს მაკრო უსაფრთხოება... ეკრანზე გამოჩნდება დიალოგური ფანჯარა. ნდობის ცენტრი.
დააწკაპუნეთ ღილაკზე დაამატეთ ახალი ადგილმდებარეობა.
დააწკაპუნეთ ღილაკზე მიმოხილვამიუთითეთ საქაღალდე სამუშაო ფაილებისთვის, რომელსაც ენდობით.
მას შემდეგ რაც მიუთითებთ სანდო ადგილს, თვითნებური მაკროები ნაგულისხმევად გააქტიურდება მასში შემავალი ყველა სამუშაო წიგნისთვის.
უსაფრთხოების მოდელი გაუმჯობესდა Excel 2013-ში. სამუშაო წიგნის ფაილები, რომლებიც ადრე "სანდო" იყო, ახლა ახსოვთ; გახსნის შემდეგ Excel სამუშაო წიგნებიდა დააჭირეთ ღილაკს ჩართეთ შინაარსი Excel- ს ახსოვს ეს მდგომარეობა. შედეგად, ეს წიგნი მიეკუთვნება სანდოების კატეგორიას და მისი შემდგომი გახსნისას ზედმეტი კითხვები არ ისმება.
შექმენით მომხმარებლის ინტერფეისი ფორმის კონტროლის გამოყენებით
მაკროს გაშვება Ctrl + Shift + Q კლავიშების კომბინაციის გამოყენებით დაგეხმარებათ, როდესაც PivotTable ანგარიშში მხოლოდ ერთი მაკროა. (გარდა ამისა, მომხმარებლებმა უნდა იცოდნენ ეს კომბინაცია.) მაგრამ დავუშვათ, რომ თქვენ გსურთ მიაწოდოთ თქვენს კლიენტებს მრავალი მაკრო, რომლებიც ასრულებენ სხვადასხვა მოქმედებას. ამ შემთხვევაში, თქვენ უნდა მიაწოდოთ მომხმარებელს გასაგები და მარტივი გზითგაუშვით თითოეული მაკრო საკვანძო კომბინაციების დამახსოვრების გარეშე. იდეალური გადაწყვეტაარის მარტივი მომხმარებლის ინტერფეისიროგორც კონტროლის კოლექცია, როგორიცაა ღილაკები, გადახვევის ზოლები და სხვა ინსტრუმენტები, რომლებიც საშუალებას გაძლევთ შეასრულოთ მაკროები მაუსის დაწკაპუნებით.
Excel გთავაზობთ ინსტრუმენტების ერთობლიობას, რომელიც შექმნილია მომხმარებლის ინტერფეისის პირდაპირ ცხრილებში შესაქმნელად. ამ ინსტრუმენტებს ფორმის კონტროლი ეწოდება. ძირითადი იდეა ისაა, რომ შესაძლებელია ფორმის კონტროლის დაყენება ცხრილიდა მიანიჭეთ მას ადრე ჩაწერილი მაკრო. კონტროლზე მინიჭების შემდეგ, მაკრო იმუშავებს ამ კონტროლზე დაწკაპუნებით.
ფორმის კონტროლი შეგიძლიათ იხილოთ ჯგუფში ფორმის კონტროლილენტის ჩანართები დეველოპერი... საკონტროლო პალიტრის გასახსნელად დააჭირეთ ამ ჯგუფს ღილაკზე ჩასმა(სურ. 4).
ბრინჯი 4. ფორმის კონტროლი ღილაკი
გთხოვთ გაითვალისწინოთ: გარდა ფორმის კონტროლისა, პალიტრა ასევე შეიცავს ActiveX კონტროლი... მიუხედავად იმისა, რომ ისინი მსგავსია, პროგრამულად ისინი სრულიად განსხვავებული ობიექტებია. ფორმის კონტროლიმათი ინვალიდობით და მარტივი პარამეტრებისპეციალურად შექმნილია სამუშაო ფურცლებზე განთავსებისთვის. Ამავე დროს ActiveX კონტროლიგამოიყენება ძირითადად საბაჟო ფორმებში. დაადგინეთ წესი, რომ განათავსოთ მხოლოდ ფორმის კონტროლი თქვენს სამუშაო ფურცლებზე.
თქვენ უნდა აირჩიოთ კონტროლი, რომელიც საუკეთესოდ შეეფერება დავალებას. ამ მაგალითში მომხმარებელს უნდა შეეძლოს PivotTable განახლება ღილაკზე დაჭერით. დააწკაპუნეთ კონტროლზე ღილაკი, გადაიტანეთ მაუსის მაჩვენებელი იმ სამუშაო გვერდზე, სადაც გსურთ ღილაკი იყოს და დააწკაპუნეთ.
მას შემდეგ რაც განათავსებთ ღილაკს ცხრილში, გაიხსნება დიალოგური ფანჯარა მიანიჭეთ მაკრო ობიექტი(სურ. 5). შეარჩიეთ საჭირო მაკრო (ჩვენს შემთხვევაში - მონაცემთა განახლებაჩაწერილია ადრე) და დააწკაპუნეთ კარგი.
ბრინჯი 5. აირჩიეთ ღილაკზე მინიჭებული მაკრო და დააჭირეთ ღილაკს კარგი... ვ ამ საქმესუნდა იქნას გამოყენებული მაკრო მონაცემთა განახლება
PivotTable ანგარიშში ყველა საჭირო კონტროლის განთავსების შემდეგ, თქვენ შეგიძლიათ დააფორმიროთ ცხრილი ძირითადი ინტერფეისის შესაქმნელად. ნახ. 6 აჩვენებს PivotTable ანგარიშს ფორმატირების შემდეგ.
ჩაწერილი მაკროს შეცვლა
მაკროს ჩაწერის შედეგად Excel პროგრამაქმნის მოდულს, რომელიც ინახავს თქვენ მიერ შესრულებულ მოქმედებებს. ყველა ჩაწერილი მოქმედება წარმოდგენილია VBA კოდის ხაზებით, რომლებიც ქმნიან მაკროს. თქვენ შეგიძლიათ დაამატოთ სხვადასხვა ტიპის კრებსითი ცხრილები თქვენს PivotTable ანგარიშებს. ფუნქციონალურობა VBA კოდის შესწორებით სასურველი შედეგის მისაღებად. იმის გასაგებად, თუ როგორ მუშაობს ეს ყველაფერი, მოდით შევქმნათ ახალი მაკრო, რომელიც აჩვენებს მომხმარებელთა პირველ ხუთ ჩანაწერს. გადადით ჩანართზე დეველოპერიდა დააჭირეთ ღილაკს მაკრო ჩაწერა... დიალოგი ყუთი ნაჩვენებია ნახ. 7. დაასახელეთ შექმნილი მაკრო Პირველიდამკვეთებიდა მიუთითეთ შენახვის ადგილი Ეს წიგნი... დააწკაპუნეთ კარგიმაკროს ჩაწერის დასაწყებად.
ჩაწერის დაწყების შემდეგ დააჭირეთ ისარს ყუთის გვერდით Მომხმარებლის სახელი, აირჩიეთ გაფილტვრა ღირებულების მიხედვითდა ვარიანტი პირველი 10(სურ .8 ა). დიალოგურ ფანჯარაში, რომელიც გამოჩნდება, დააკონფიგურირეთ პარამეტრები, როგორც ეს ნაჩვენებია ნახატზე. 8ბ. ეს პარამეტრები გეუბნებათ გაყიდვების თვალსაზრისით აჩვენოთ ხუთეულში შემავალი მომხმარებლების მონაცემები. დააწკაპუნეთ კარგი.
ბრინჯი 8. აირჩიეთ ფილტრი (a) და დაარეგულირეთ პარამეტრები (b), რათა გამოჩნდეს საუკეთესო ხუთეული გაყიდვების მიხედვით
მას შემდეგ რაც წარმატებით ჩაიწერთ ყველა საფეხურს, რომელიც საჭიროა ტოპ 5 გაყიდვების კლიენტის მოსაპოვებლად, გადადით ჩანართზე დეველოპერიდა დააჭირეთ ღილაკს შეწყვიტე ჩაწერა.
ახლა თქვენ გაქვთ მაკრო, რომელიც გაფილტრავს PivotTable– ს გაყიდვების ტოპ 5 მომხმარებლის მოსაპოვებლად. აუცილებელია მაკრო რეაგირება მოახდინოს გადახვევის ზოლის მდგომარეობაზე, ე.ი. გადახვევის ზოლის გამოყენებით, თქვენ უნდა შეგეძლოთ მაკროზე მიუთითოთ იმ მომხმარებლების რაოდენობა, რომელთა მონაცემები გამოჩნდება საყრდენი ცხრილის ანგარიშში. ამრიგად, გადახვევის ზოლის გამოყენებით, მომხმარებელს შეეძლება აიღოს ტოპ ხუთეული, რვა ან ოცდათორმეტი საუკეთესო კლიენტი, როგორც საჭიროდ ჩათვლის.
თქვენს ელცხრილზე გადახვევის ზოლის დასამატებლად გადადით ჩანართზე დეველოპერი, დააწკაპუნეთ ღილაკზე ჩასმააირჩიეთ კონტროლი პალიტრაზე გადახვევის ზოლიდა განათავსეთ იგი სამუშაო ფურცელზე. მარჯვენა ღილაკით დააწკაპუნეთ კონტროლზე გადახვევის ზოლი ობიექტის ფორმატი... გაიხსნება დიალოგური ფანჯარა კონტროლის ფორმატი(სურ. 9). მასში შეიტანეთ შემდეგი ცვლილებები პარამეტრებში: მინიმალური ღირებულებამიანიჭეთ მნიშვნელობა 1, პარამეტრი მაქსიმალური მნიშვნელობა - ღირებულება 200 და ველში უჯრედის კომუნიკაციაშეიყვანეთ მნიშვნელობა $ M $ 2, რათა ნახოთ Scrollbar მნიშვნელობა საკანში M2. დააწკაპუნეთ ღილაკზე კარგიგამოიყენოს ადრე მითითებული პარამეტრები.
ახლა თქვენ უნდა შეესაბამებოდეს ახლახან ჩაწერილ მაკროს Პირველიდამკვეთებიკონტროლით გადახვევის ზოლისამუშაო ფურცელზე. მარჯვენა ღილაკით დააწკაპუნეთ კონტროლზე გადახვევის ზოლიდა ში კონტექსტური მენიუაირჩიე გუნდი მიანიჭეთ მაკრო Macro Assignment დიალოგური ფანჯრის გასახსნელად. მიანიჭეთ ჩაწერილი მაკრო გადახვევის ზოლს FirstN მომხმარებლები... მაკრო გადის ყოველ ჯერზე, როდესაც გადახვევის ზოლს დააწკაპუნებთ. შეამოწმეთ შექმნილი გადახვევის ზოლი. ზოლზე დაჭერის შემდეგ, მაკრო გადის FirstN მომხმარებლებიდა ნომერი M2 უჯრედში შეიცვლება გადახვევის ზოლის სტატუსის მითითებით. ნომერი M2 უჯრედში მნიშვნელოვანია, რადგან ის გამოიყენება მაკრო გადასაადგილებლად.
ერთადერთი, რაც გასაკეთებელია, არის მაკრო დამუშავება M2 უჯრედის ნომრის გადახვევის ზოლთან ასოცირებით. ამისათვის თქვენ უნდა გადახვიდეთ მაკროს VBA-კოდზე. ამისათვის გადადით ჩანართზე დეველოპერიდა დააჭირეთ ღილაკს მაკროები... გაიხსნება დიალოგური ფანჯარა მაკრო(სურ. 10). მასში შეგიძლიათ გაუშვათ, წაშალოთ და შეცვალოთ არჩეული მაკრო. ეკრანზე მაკროს VBA კოდის საჩვენებლად აირჩიეთ მაკრო და დააჭირეთ ღილაკს შეცვლა.
ბრინჯი 10. VBA მაკრო კოდზე წვდომისათვის Პირველიდამკვეთებიაირჩიეთ მაკრო და დააჭირეთ ღილაკს შეცვლა
რედაქტორის ფანჯარა გამოჩნდება ეკრანზე ვიზუალური ძირითადიმაკროს VBA- კოდით (სურ. 11). თქვენი მიზანია შეცვალოთ მყარი კოდირებული ნომერი 5, რომელიც მითითებულია მაკრო ჩაწერისას, მნიშვნელობით M2 უჯრედში, რომელიც მიბმულია გადახვევის ზოლზე. თავდაპირველად, დაფიქსირდა მაკრო, რომ აჩვენოს ხუთეულში ყველაზე მაღალი შემოსავლის მქონე მომხმარებლები.
ამოიღეთ ნომერი 5 კოდიდან და შეიყვანეთ შემდეგი გამონათქვამი:
ActiveSheet.Range ("M2"). ღირებულება
დაამატეთ ორი ხაზი მაკროს დასაწყისში ფილტრების გასასუფთავებლად:
დიაპაზონი ("A4"). აირჩიეთ
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("მომხმარებლის სახელი") .ClearAllFilters
მაკრო კოდი ახლა უნდა გამოიყურებოდეს, როგორც ნაჩვენებია ნახატზე. 12
დახურეთ Visual Basic რედაქტორი და დაუბრუნდით PivotTable ანგარიშს. შეამოწმეთ გადახვევის ზოლი სლაიდერი გადაიტანეთ 11 -ზე. მაკრო უნდა გაუშვას და გაფილტროს 11 ჩანაწერი საუკეთესო კლიენტებიგაყიდვებით.
სინქრონიზაცია ორი მბრუნავი ცხრილი ერთი dropdown
მოხსენება ნაჩვენებია ნახ. 13 შეიცავს ორ საყრდენ ცხრილს. თითოეულ მათგანს აქვს გვერდის ველი, რომელიც საშუალებას გაძლევთ აირჩიოთ გაყიდვების არეალი. პრობლემა ის არის, რომ ყოველ ჯერზე, როდესაც თქვენ ირჩევთ ბაზარს ერთი საყრდენი ცხრილის გვერდის ველში, თქვენ უნდა აირჩიოთ იგივე ბაზარი სხვა საყრდენი ცხრილის გვერდის ველში. მონაცემთა ანალიზის ფაზაში ორ ცხრილს შორის ფილტრების სინქრონიზაცია დიდი პრობლემა არ არის, მაგრამ დიდია შანსი, რომ თქვენ ან თქვენს კლიენტებს მაინც დაგავიწყდეთ ამის გაკეთება.
ბრინჯი 13. ორი საყრდენი ცხრილი შეიცავს გვერდის ველს, რომელიც მონაცემებს ფილტრავს ბაზრის მიხედვით. ერთი ბაზრის მონაცემების გასაანალიზებლად, თქვენ უნდა მოახდინოთ ორივე საყრდენი ცხრილის სინქრონიზაცია
ამ საყრდენი ცხრილების სინქრონიზაციის ერთ -ერთი გზაა ჩამოსაშლელი სიის გამოყენება. იდეა არის მაკრო ჩაწერა, რომელიც ირჩევს სასურველ ბაზარს სფეროდან გაყიდვების ბაზარიორივე ცხრილში. შემდეგ თქვენ უნდა შექმნათ ჩამოსაშლელი სია და შეავსოთ იგი გაყიდვების ბაზრების სახელებით ორი საყრდენი ცხრილიდან. დაბოლოს, ჩაწერილი მაკრო უნდა შეიცვალოს ორივე PivotTable- ის გასაფილტრად ჩამოსაშლელი სიის მნიშვნელობების გამოყენებით. ამ პრობლემის გადასაჭრელად, თქვენ უნდა შეასრულოთ შემდეგი ნაბიჯები.
1. შექმენით ახალი მაკრო და მიეცით სახელი სინჩმარკეტები... როდესაც ჩაწერა დაიწყება, აირჩიეთ ველი გაყიდვების ბაზარი ორივესთვისგაყიდვების საყრდენი მაგიდები კალიფორნიადა შეწყვიტე მაკრო ჩაწერა.
2. აჩვენეთ ფორმის კონტროლის პალიტრა და დაამატეთ ჩამოსაშლელი სია სამუშაო ფურცელს.
3. შექმენით PivotTable– ის ყველა ბაზრის მყარი კოდირებული სია. გაითვალისწინეთ, რომ სიაში პირველი პუნქტია (ყველა). თქვენ უნდა ჩართოთ ეს ელემენტი, თუ გსურთ შეძლოთ ყველა ბაზრის არჩევა ჩამოსაშლელი სიიდან.
4. ამ ეტაპზე, PivotTable ანგარიში უნდა გამოიყურებოდეს, როგორც ნაჩვენებია ნახ. თოთხმეტი.
ბრინჯი 14. თქვენს განკარგულებაშია ყველა საჭირო ინსტრუმენტი: მაკრო, რომელიც ცვლის ველს გაყიდვების ბაზარიორივე საყრდენი ცხრილი, ჩამოსაშლელი სია და ყველა გაყიდვების ბაზრის სია, რომელიც შეიცავს საყრდენ ცხრილს
5. დააწკაპუნეთ ჩამოსაშლელ სიაზე მარჯვენა ღილაკით და აირჩიეთ ბრძანება ობიექტის ფორმატიკონტროლის მორგება.
6. პირველ რიგში, დააყენეთ მნიშვნელობების საწყისი დიაპაზონი, რომელიც გამოიყენება ჩამოსაშლელი სიის შესავსებად, როგორც ეს ნაჩვენებია სურათზე 6-7. 15. ამ შემთხვევაში, ჩვენ ვსაუბრობთ გაყიდვების ბაზრების სიაზე, რომელიც თქვენ შექმენით მე-3 საფეხურზე. შემდეგ მიუთითეთ უჯრედი, რომელიც აჩვენებს არჩეული ელემენტის სერიულ ნომერს (ამ მაგალითში ეს არის უჯრედი H1). Პარამეტრი სიის ხაზების რაოდენობაგანსაზღვრავს რამდენი ხაზი ერთდროულად გამოჩნდება ჩამოსაშლელ სიაში. დააწკაპუნეთ ღილაკზე კარგი.
ბრინჯი 15. ჩამოსაშლელი სიის პარამეტრები უნდა მიუთითებდეს გაყიდვების ბაზრების სიაზე, როგორც ღირებულებების საწყის დიაპაზონს, ხოლო საკანში H1 უნდა იყოს განსაზღვრული, როგორც წამყვანის წერტილი.
7. ახლა თქვენ გაქვთ შესაძლებლობა შეარჩიოთ გაყიდვების ბაზარი ჩამოსაშლელი სიიდან, ასევე განსაზღვროთ დაკავშირებული სერიული ნომერი საკანში H1 (სურ. 16). ჩნდება კითხვა: რატომ გამოიყენება ინდექსის ღირებულება ბაზრის ნამდვილი სახელის ნაცვლად? იმიტომ, რომ ჩამოსაშლელი სია აბრუნებს არა სახელს, არამედ რიცხვს. მაგალითად, თუ თქვენ აირჩიეთ კალიფორნია ჩამოსაშლელი სიიდან, მნიშვნელობა 5 გამოჩნდება უჯრედში H1, რაც ნიშნავს რომ კალიფორნია სიის მეხუთე პუნქტია.
ბრინჯი 16. ჩამოსაშლელი სია ახლა ივსება ბაზრების სახელებით და არჩეული ბაზრის სერიული ნომერი ნაჩვენებია უჯრედში H1.
8. ბაზრის სახელის ნაცვლად სერიული ნომრის გამოსაყენებლად, ის უნდა გაიაროთ INDEX ფუნქციის გამოყენებით.
9. შეიყვანეთ INDEX ფუნქცია, რომელიც სერიულ ნომერს გარდაქმნის H1 უჯრედიდან მნიშვნელობრივ მნიშვნელობად.
10. INDEX ფუნქცია იღებს ორ არგუმენტს. პირველი არგუმენტი წარმოადგენს სიაში არსებული მნიშვნელობების დიაპაზონს. უმეტეს შემთხვევაში, თქვენ იყენებთ იმავე დიაპაზონს, რომელიც ავსებს ჩამოსაშლელ მენიუს. მეორე არგუმენტი არის რიგითი რიცხვი. თუ სერიული ნომერი შეყვანილია უჯრედში (მაგალითად, საკანში H1, როგორც ნახაზი 17), მაშინ შეგიძლიათ უბრალოდ მიმართოთ ამ უჯრედს.
ბრინჯი 17. INDEX ფუნქცია I1 უჯრედში გარდაქმნის თანმიმდევრობის ნომერს H1 უჯრედში მნიშვნელობად. თქვენ გამოიყენებთ მნიშვნელობას უჯრედში I1 მაკროს შესაცვლელად
11. მაკროს რედაქტირება სინჩმარკეტებიი11 უჯრედში არსებული მნიშვნელობის გამოყენებით მყარი კოდირებული მნიშვნელობის ნაცვლად. გადადით ჩანართზე დეველოპერიდა დააჭირეთ ღილაკს მაკროები... ეკრანზე გამოჩნდება დიალოგური ფანჯარა, როგორც ეს ნაჩვენებია ნახატზე. 18. აირჩიეთ მასში მაკრო სინჩმარკეტებიდა დააჭირეთ ღილაკს შეცვლა.
ბრინჯი 18. მაკროს VBA კოდზე წვდომისათვის შეარჩიეთ მაკრო სინჩმარკეტებიდა დააწკაპუნეთ შეცვლა
12. მაკროს ჩაწერისას თქვენ შეარჩიეთ კალიფორნიის გაყიდვების ზონა ველიდან ორივე კრებსითი ცხრილიდან. გაყიდვების ბაზარი... როგორც ჩანს ნახ. 19, კალიფორნიის ბაზარი მყარად არის კოდირებული VBA მაკრო კოდში.
13. ჩაანაცვლეთ California Activesheet.Range ("I1") .Value, რომელიც ეხება მნიშვნელობას I1 უჯრედში. ამ ეტაპზე, მაკრო კოდი უნდა გამოიყურებოდეს, როგორც ნაჩვენებია ნახატზე. 20. მაკროს შეცვლის შემდეგ დახურეთ Visual Basic Editor და დაუბრუნდით ცხრილს.
ბრინჯი 20. შეცვალეთ "კალიფორნია" ActiveSheet.Range ("I1"). მნიშვნელობა და დახურეთ Visual Basic Editor
14. რჩება მხოლოდ მაკროის შესრულების უზრუნველყოფა ჩამოსაშლელ სიაში გაყიდვების არეალის არჩევისას. დააწკაპუნეთ ჩამოსაშლელ მენიუზე მარჯვენა ღილაკით და აირჩიეთ ვარიანტი მიანიჭეთ მაკრო... აირჩიეთ მაკრო სინჩმარკეტიდა დააჭირეთ ღილაკს კარგი.
15. დამალეთ რიგები და სვეტები გვერდის ველებით კრებსით ცხრილებში და თქვენ მიერ შექმნილ ბაზრებისა და ინდექსის ფორმულების სიაში.
ნახ. 21 გვიჩვენებს საბოლოო შედეგს. თქვენ უკვე გაქვთ ინტერფეისი, რომელიც მომხმარებელს საშუალებას აძლევს შეარჩიოს გაყიდვების არე ორივე საყრდენ ცხრილში ერთი ჩამოსაშლელი სიის გამოყენებით.
როდესაც ჩამოსაშლელი სიიდან ირჩევთ ახალ პუნქტს, სვეტების ავტომატურად ხდება ზომის შეცვლა მათში ნაჩვენები ყველა მონაცემისთვის. ამგვარი პროგრამის ქცევა საკმაოდ მოსაწყენია სამუშაო ფურცლის შაბლონის ფორმატირებისას. ამის თავიდან აცილება შეგიძლიათ PivotTable– ზე მარჯვენა დაწკაპუნებით და არჩევით საყრდენი ცხრილის პარამეტრები... ეკრანზე გამოჩნდება ამავე სახელწოდების დიალოგური ფანჯარა, რომელშიც უნდა მონიშნოთ ყუთი განახლებისას სვეტების ზომის ავტომატურად შეცვლა.
შენიშვნა ეფუძნება ჯელენის წიგნს, ალექსანდრე. ... თავი 12.
Excel– ის ინსტრუმენტების გამოყენებით, თქვენ შეგიძლიათ აირჩიოთ კონკრეტული მონაცემები დიაპაზონიდან შემთხვევითი თანმიმდევრობით, ერთი მდგომარეობით ან რამოდენიმე. ასეთი პრობლემების გადასაჭრელად, როგორც წესი, გამოიყენება მასივის ფორმულები ან მაკრო. მოდით შევხედოთ რამდენიმე მაგალითს.
როგორ გავაკეთოთ შერჩევა Excel პირობით
მასივის ფორმულების გამოყენებისას, შერჩეული მონაცემები ნაჩვენებია ცალკე ცხრილში. რა არის უპირატესობა ამ მეთოდითჩვეულებრივ ფილტრთან შედარებით.
წყაროს ცხრილი:
პირველი, მოდით ვისწავლოთ თუ როგორ უნდა გავაკეთოთ შერჩევა ერთი რიცხვითი კრიტერიუმის საფუძველზე. ამოცანაა შეარჩიოთ პროდუქტები, რომელთა ფასი 200 რუბლს აღემატება. ერთი გამოსავალი არის ფილტრაციის გამოყენება. შედეგად, თავდაპირველ ცხრილში დარჩება მხოლოდ ის პროდუქტები, რომლებიც დააკმაყოფილებენ მოთხოვნას.
კიდევ ერთი გამოსავალი არის მასივის ფორმულის გამოყენება. მოთხოვნის შესაბამისი სტრიქონები მოთავსდება ცალკე ცხრილის ანგარიშში.
პირველი, ჩვენ ვქმნით ცარიელ ცხრილს ორიგინალის გვერდით: დუბლიკატი სათაურები, რიგების და სვეტების რაოდენობა. ახალი ცხრილი იკავებს დიაპაზონს E1: G10. ახლა აირჩიეთ E2: E10 (სვეტი "თარიღი") და შეიყვანეთ შემდეგი ფორმულა: ( }.
მასივის ფორმულის მისაღებად დააჭირეთ კლავიშთა კომბინაციას Ctrl + Shift + Enter. შემდეგ სვეტში - "პროდუქტი" - ჩვენ ვწერთ მსგავს მასივის ფორმულას: ( ). INDEX ფუნქციის მხოლოდ პირველი არგუმენტი შეიცვალა.
სვეტში "ფასი" შეიყვანეთ იგივე მასივის ფორმულა, შეცვალეთ INDEX ფუნქციის პირველი არგუმენტი.
შედეგად, ჩვენ ვიღებთ ანგარიშს საქონლის შესახებ, რომლის ფასი 200 რუბლზე მეტია.
ასეთი შერჩევა დინამიურია: როდესაც მოთხოვნა იცვლება ან ახალი პროდუქტები გამოჩნდება წყაროს ცხრილში, ანგარიში ავტომატურად შეიცვლება.
ამოცანა ნომერი 2 - ორიგინალური ცხრილიდან შეარჩიეთ საქონელი, რომელიც გაყიდვაში გამოვიდა 2015 წლის 20 სექტემბერს. ანუ შერჩევის კრიტერიუმია თარიღი. მოხერხებულობისთვის სასურველ თარიღს შევიყვანთ ცალკე უჯრედში, I2.
მასივის მსგავსი ფორმულა გამოიყენება პრობლემის გადასაჭრელად. მხოლოდ კრიტერიუმის ნაცვლად).
მსგავსი ფორმულები შეტანილია სხვა სვეტებში (იხ. პრინციპი ზემოთ).
ახლა ჩვენ ვიყენებთ ტექსტის კრიტერიუმს. I2 უჯრედში თარიღის ნაცვლად შეიყვანეთ ტექსტი "პროდუქტი 1". მოდით ცოტათი შევცვალოთ მასივის ფორმულა: ( }.
ასეთი დიდი შერჩევის ფუნქცია Excel- ში.
შერჩევა Excel– ში მრავალი პირობით
პირველი, ავიღოთ ორი რიცხვითი კრიტერიუმი:
ამოცანაა შეარჩიოთ საქონელი, რომელიც ღირს 400-ზე ნაკლები და 200 რუბლზე მეტი. მოდით გავაერთიანოთ პირობები "*" ნიშანთან. მასივის ფორმულა ასე გამოიყურება: ( }.!}
ეს არის ანგარიშის ცხრილის პირველი სვეტისთვის. მეორე და მესამესთვის - ვცვლით INDEX ფუნქციის პირველ არგუმენტს. შედეგი:
არჩევანის გასაკეთებლად რამდენიმე თარიღით ან რიცხვითი კრიტერიუმით, ვიყენებთ მასივის მსგავს ფორმულებს.
შემთხვევითი შერჩევა Excel-ში
როდესაც მომხმარებელი მუშაობს დიდი თანხამონაცემებს შეიძლება დასჭირდეს შემთხვევითი შერჩევის აღება შემდგომი ანალიზისთვის. თქვენ შეგიძლიათ მიანიჭოთ შემთხვევითი რიცხვი თითოეულ სტრიქონს და შემდეგ მიმართოთ შერჩევას.
ორიგინალური მონაცემთა ნაკრები:
პირველ რიგში, ჩადეთ ორი ცარიელი სვეტი მარცხნივ. A2 უჯრედში ვწერთ ფორმულას RAND (). გავამრავლოთ იგი მთელ სვეტზე:
ახლა ჩვენ ვაკოპირებთ სვეტს შემთხვევითი რიცხვებით და ჩასვით B. სვეტში. ეს აუცილებელია ისე, რომ ეს რიცხვები არ შეიცვალოს დოკუმენტში ახალი მონაცემების შეტანისას.
მნიშვნელობების ჩასასმელად და არა ფორმულისთვის, დააწკაპუნეთ მარჯვენა ღილაკით B სვეტზე და შეარჩიეთ Paste Special tool. ფანჯარაში, რომელიც იხსნება, ჩადეთ ნიშანი "ღირებულებების" პუნქტის წინ:
ახლა შეგიძლიათ დაალაგოთ B სვეტის მონაცემები ზრდადობით ან კლებადობით. ასევე შეიცვლება თანმიმდევრობა, რომლითაც ორიგინალური მნიშვნელობებია წარმოდგენილი. ჩვენ ვირჩევთ ნებისმიერი რაოდენობის ხაზს ზემოდან ან ქვემოდან - მივიღებთ შემთხვევით ნიმუშს.
სხვადასხვა (39)
Excel-ის შეცდომები და ხარვეზები (3)
როგორ მივიღო უნიკალური (დუბლიკატი) მნიშვნელობების სია?
წარმოიდგინეთ სხვადასხვა სახელების დიდი სია, სრული სახელები, პერსონალის ნომრები და ა.შ. და აუცილებელია ამ სიიდან დატოვოს ყველა ერთი და იგივე სახელის სია, მაგრამ ისე, რომ ისინი არ გაიმეორონ საკუთარი თავი - ე.ი. ამოიღეთ ყველა დუბლიკატი ჩანაწერი ამ სიიდან. როგორც მას სხვაგვარად ეძახიან: შექმენით უნიკალური ელემენტების სია, სია განმეორებითი, დუბლიკატების გარეშე. ამის გაკეთების რამდენიმე გზა არსებობს: ჩაშენებული Excel ინსტრუმენტები, ჩაშენებული ფორმულები და, ბოლოს და ბოლოს, კოდის გამოყენება Visual Basic აპლიკაციისთვის (VBA) და საყრდენი ცხრილები. ეს სტატია განიხილავს თითოეულ ვარიანტს.
ჩაშენებული ფუნქციების გამოყენება Excel 2007 და ზემოთ
Excel 2007 და 2010 წლებში ამის გაკეთება ისეთივე მარტივია, როგორც მსხლის ჭურვი - არსებობს სპეციალური გუნდი, რომელსაც ქვია -. იგი მდებარეობს ჩანართზე მონაცემებიქვეგანყოფილება მონაცემთა ინსტრუმენტები
როგორ გამოვიყენოთ ეს ბრძანება. მონიშნეთ სვეტი (ან რამდენიმე)იმ მონაცემებით, რომლებშიც საჭიროა დუბლიკატი ჩანაწერების წაშლა. გადადით ჩანართზე მონაცემები -დუბლიკატების წაშლა.
თუ თქვენ ირჩევთ ერთ სვეტს, მაგრამ მის გვერდით იქნება მეტი სვეტი მონაცემებით (ან მინიმუმ ერთი სვეტი), მაშინ Excel შემოგვთავაზებს არჩევანის გაკეთებას: გააფართოვოთ შერჩევის დიაპაზონი ამ სვეტით ან დატოვეთ არჩევანი ისეთი, როგორიც არის და წაშალეთ მხოლოდ მონაცემები არჩეულ დიაპაზონში. მნიშვნელოვანია გვახსოვდეს, რომ თუ არ გააფართოვებთ დიაპაზონს, მაშინ მონაცემები შეიცვლება მხოლოდ ერთ სვეტში, ხოლო მიმდებარე სვეტში არსებული მონაცემები უცვლელი დარჩება.
გამოჩნდება ფანჯარა დუბლიკატების ამოღების ვარიანტებით 
შეამოწმეთ ყუთები იმ სვეტების წინ, დუბლიკატი, რომელთა წაშლა გსურთ და დააწკაპუნეთ OK. თუ მონაცემთა სათაურები ასევე განლაგებულია არჩეულ დიაპაზონში, მაშინ უმჯობესია დროშის დაყენება ჩემი მონაცემები შეიცავს სათაურებსცხრილში მონაცემების შემთხვევით წაშლის თავიდან ასაცილებლად (თუ ისინი მოულოდნელად მთლიანად ემთხვევა სათაურის მნიშვნელობას).
მეთოდი 1: გაფართოებული ფილტრი
Excel 2003 -ის შემთხვევაში, ყველაფერი უფრო რთულია. არ არსებობს ისეთი ინსტრუმენტი, როგორიც არის წაშალეთ დუბლიკატი... მაგრამ მეორეს მხრივ, არსებობს ისეთი მშვენიერი ინსტრუმენტი, როგორიცაა გაფართოებული ფილტრი... 2003 წელს ეს ინსტრუმენტი შეგიძლიათ იხილოთ მონაცემები -ფილტრი -გაფართოებული ფილტრი... ამ მეთოდის უპირატესობა ის არის, რომ თქვენ შეგიძლიათ გამოიყენოთ იგი სიის შესაქმნელად სხვა დიაპაზონში, ვიდრე ორიგინალური მონაცემების გაფუჭება. 2007-2010 წლებში Excel, ის ასევე არის იქ, მაგრამ ოდნავ დამალული. მდებარეობს ჩანართზე მონაცემები, ჯგუფი დალაგება და გაფილტვრა - Მოწინავე
როგორ გამოვიყენოთ იგი: გაუშვით მითითებული ინსტრუმენტი - გამოჩნდება დიალოგური ფანჯარა: 
- მკურნალობა:Ჩვენ ვირჩევთ დააკოპირეთ შედეგი სხვა ადგილას.
- სიის დიაპაზონი:დიაპაზონის შერჩევა მონაცემებით (ჩვენს შემთხვევაში ეს არის A1: A51).
- კრიტერიუმების დიაპაზონი:ამ შემთხვევაში, დატოვეთ ცარიელი.
- დიაპაზონში კოპირება:ჩვენ ვაჩვენებთ მონაცემების ჩვენების პირველ უჯრედს - ნებისმიერი ცარიელი (სურათზე - E2).
- შეამოწმეთ ყუთი მხოლოდ უნიკალური ჩანაწერები.
- ჩვენ ვაჭერთ კარგი.
Შენიშვნა:თუ გსურთ შედეგის განთავსება სხვა ფურცელზე, მაშინ უბრალოდ ვერ მიუთითებთ სხვა ფურცელს. თქვენ შეძლებთ მიუთითოთ უჯრედი სხვა ფურცელზე, მაგრამ ... ვაი და აჰ ... Excel მისცემს შეტყობინებას, რომ მას არ შეუძლია მონაცემების კოპირება სხვა ფურცლებზე. მაგრამ ეს ასევე შეიძლება გვერდის ავლით და საკმაოდ მარტივად. თქვენ უბრალოდ უნდა გაიქცეთ გაფართოებული ფილტრიიმ ფურცლიდან, რომელზეც ჩვენ გვსურს შედეგის განთავსება. და როგორც საწყისი მონაცემები, ჩვენ ვირჩევთ მონაცემებს ნებისმიერი ფურცლიდან - ეს დასაშვებია.
თქვენ ასევე შეგიძლიათ არ გადაიტანოთ შედეგი სხვა უჯრედებში, მაგრამ გაფილტროთ მონაცემები ადგილზე. მონაცემები არანაირად არ იმოქმედებს - ეს იქნება ჩვეულებრივი მონაცემების გაფილტვრა.
ამისათვის თქვენ უბრალოდ უნდა აირჩიოთ დამუშავების პუნქტში გაფილტრეთ სია, ადგილზე.
მეთოდი 2: ფორმულები
ეს მეთოდი უფრო ძნელი გასაგებია გამოუცდელი მომხმარებლებისთვის, მაგრამ ის ქმნის უნიკალური ღირებულებების ჩამონათვალს ორიგინალური მონაცემების შეცვლის გარეშე. ისე, ის ასევე უფრო დინამიურია: თუ თქვენ შეცვლით მონაცემებს წყაროს ცხრილში, შედეგიც შეიცვლება. ეს ზოგჯერ სასარგებლოა. მე შევეცდები აგიხსნათ რა და რა: მაგალითად, თქვენ გაქვთ ჩამონათვალი მონაცემებით სვეტში A (A1: A51, სადაც A1 არის სათაური). ჩვენ გამოვაჩენთ სიას სვეტში C, დაწყებული C2 უჯრედიდან. ფორმულა C2-ში იქნება შემდეგი:
(= ინდექსი ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
(= ინდექსი ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)))
ამ ფორმულის მუშაობის დეტალური ანალიზი მოცემულია სტატიაში:
უნდა აღინიშნოს, რომ ეს ფორმულა არის მასივის ფორმულა. ეს შეიძლება ითქვას ბრეკეტები, რომელშიც ეს ფორმულაა ჩასმული. და ასეთი ფორმულა შედის უჯრედში კლავიატურის მალსახმობით - Ctrl+ცვლა+შეიყვანეთ... მას შემდეგ რაც C2– ში შევიტანეთ ეს ფორმულა, ჩვენ უნდა დავაკოპიროთ და ჩასვათ იგი რამდენიმე სტრიქონში, რათა ზუსტად გამოვავლინოთ ყველა უნიკალური ელემენტი. როგორც კი ქვედა უჯრედებში ფორმულა ბრუნდება #NUMBER!- ეს ნიშნავს, რომ ყველა ელემენტია ნაჩვენები და აზრი არ აქვს ქვემოთ მოცემული ფორმულის გაჭიმვას. შეცდომის თავიდან ასაცილებლად და ფორმულა უფრო უნივერსალური გახადოთ (ყოველ ჯერზე გაჭიმვის გარეშე, სანამ შეცდომა გამოჩნდება), შეგიძლიათ გამოიყენოთ მარტივი შემოწმება:
Excel 2007 და ზემოთ:
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; მცირე) (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; STRING ($ A $ 1: $ $ 50)); 1)); ""))
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; მცირე) (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ $ 50)); 1)); ""))
Excel 2003 -ისთვის:
(= IF (ISH (მცირე (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; მცირე) თუ (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; რიგი ($ A $ 1: $ A $ 50)); 1))))
(= IF (ISERR (მცირე (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; მცირე) თუ (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) = 0; რიგი ($ A $ 1: $ A $50))) 1))))
შემდეგ შეცდომის ნაცვლად #რიცხვი! (#რიცხვი!)გექნება ცარიელი უჯრედები(მთლად ცარიელი, რა თქმა უნდა - ფორმულებით :-)).
ცოტა მეტი დეტალი ESLIOSHIBKA და IF ფორმულების განსხვავებებისა და ნიუანსების შესახებ (EOSH შეგიძლიათ წაიკითხოთ ამ სტატიაში: როგორ ვაჩვენოთ 0 უჯრედში ფორმულით შეცდომის ნაცვლად
მეთოდი 3: VBA კოდი
ეს მიდგომა მოითხოვს მაკროების გადაწყვეტას და მათთან მუშაობის საბაზისო ცოდნას. თუ არ ხართ დარწმუნებული თქვენს ცოდნაში, გირჩევთ, პირველ რიგში წაიკითხოთ ეს სტატიები:
- რა არის მაკრო და სად შეიძლება მისი პოვნა? სტატიას თან ერთვის ვიდეო გაკვეთილი
- რა არის მოდული? რა მოდულები არსებობს? საჭირო იქნება იმის გასარკვევად, თუ სად შეიყვანოთ ქვემოთ მოყვანილი კოდები
ქვემოთ მოყვანილი ორივე კოდი უნდა იყოს განთავსებული სტანდარტული მოდული... მაკრო უნდა იყოს დაშვებული.
მოდით დავტოვოთ საწყისი მონაცემები იმავე თანმიმდევრობით - სია მონაცემებით არის განთავსებული სვეტში "A" (A1: A51, სადაც A1 არის სათაური)... მხოლოდ ჩვენ ვაჩვენებთ სიას არა C სვეტში, არამედ E სვეტში, E2 უჯრედიდან დაწყებული:
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 to Rows.Count, 1 to 1) შეცდომის შესახებ ახალი კოლექციით განაახლეთ შემდეგი თითოეული პუნქტისთვის დიაპაზონში ("A2", უჯრედები (სტრიქონები. რაოდენობა, 1) . დასასრული (xlUp)). მნიშვნელობა "(! LANG: უჯრედები (რიგები. რიცხვი, 1). დასასრული (xlUp) - განსაზღვრავს ბოლო შევსებულ უჯრედს სვეტში A. დამატება vItem, CStr (vItem) თუ Err = 0 მაშინ li = li + 1: avArr (li, 1) = vItem Else: Err. Clear End If Next End With If li.
ამ კოდის საშუალებით შეგიძლიათ ამოიღოთ უნიკალური მნიშვნელობები არა მხოლოდ ერთი სვეტიდან, არამედ სვეტებისა და რიგების ნებისმიერი დიაპაზონიდან. თუ ხაზის ნაცვლად
Დიაპაზონი ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
მიუთითეთ Selection.Value, შემდეგ კოდის შედეგი იქნება უნიკალური ელემენტების სია აქტიურ ფურცელზე არჩეული დიაპაზონიდან. მხოლოდ ამის შემდეგ იქნებოდა კარგი ღირებულებების გამომავალი უჯრედის შეცვლა- ნაცვლად
განათავსეთ ის, რომელშიც მონაცემები არ არის.
თქვენ ასევე შეგიძლიათ მიუთითოთ კონკრეტული დიაპაზონი:
დიაპაზონი ("C2", უჯრედები (სტრიქონები. რაოდენობა, 3). დასასრული (xlUp)). მნიშვნელობა
უნივერსალური კოდი უნიკალური ღირებულებების შესარჩევად
ქვემოთ მოყვანილი კოდი შეიძლება გამოყენებულ იქნას ნებისმიერ დიაპაზონში. საკმარისია მისი გაშვება, დიაპაზონის მითითება მნიშვნელობებით, მხოლოდ არ განმეორებითი პირობის შესარჩევად (დასაშვებია ერთზე მეტი სვეტი) და უჯრედი შედეგის ჩვენებისათვის. დასკანირებული იქნება მითითებული უჯრედები, რომელთაგან მხოლოდ უნიკალური ღირებულებები(ცარიელი უჯრედები გამოტოვებულია) და მიღებული სია დაიწერება მითითებული უჯრედიდან დაწყებული.
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals როგორც დიაპაზონი, rResultCell როგორც დიაპაზონი შეცდომის შესახებ განაახლეთ შემდეგი "მოითხოვეთ უჯრედების მისამართი უნიკალური მნიშვნელობების შესარჩევადდააყენეთ rVals = Application.InputBox ( "მიუთითეთ უჯრედების დიაპაზონი უნიკალური მნიშვნელობების ასარჩევად", "მონაცემების მოთხოვნა", "A2: A51", ტიპი: = 8) თუ rVals არაფერია მაშინ თუ დააჭირეთ გაუქმების ღილაკს Exit Sub End თუ ”თუ მხოლოდ ერთი უჯრედი არის მითითებული, არჩევას აზრი არ აქვსთუ rVals.Count = 1 მაშინ MsgBox "უნიკალური მნიშვნელობების გასაფილტრად, თქვენ უნდა მიუთითოთ ერთზე მეტი უჯრედი", vbInformation, "www.site" Exit Sub End If "შეწყვიტე ცარიელი რიგები და სვეტები სამუშაო დიაპაზონის გარეთ RVals = გადაკვეთა (rVals, rVals.Parent.UsedRange) თუ მითითებულია მხოლოდ ცარიელი უჯრედები სამუშაო დიაპაზონის გარეთთუ rVals არაფერია მაშინ MsgBox "არასაკმარისი მონაცემები ღირებულებების შესარჩევად", vbInformation, "www.site" Exit Sub End If avVals = rVals.Value "მოითხოვეთ უჯრედი, რომ აჩვენოს შედეგიდააყენეთ rResultCell = Application.InputBox ( "მიუთითეთ უჯრედი შერჩეული უნიკალური მნიშვნელობების ჩასამატებლად", "მოითხოვეთ მონაცემები", "E2", ტიპი: = 8) თუ rResultCell არაფერია მაშინ თუ დააჭირეთ გაუქმების ღილაკს Exit Sub End თუ "განსაზღვრეთ მასივის მაქსიმალური შესაძლო განზომილება შედეგისთვის ReDim avArr (1 რიგებიდან. რაოდენობა, 1 -დან 1 -მდე) "კოლექციის ობიექტის გამოყენებით "აირჩიეთ მხოლოდ უნიკალური ჩანაწერები, "რადგან კოლექციები არ შეიძლება შეიცავდეს დუბლიკატ მნიშვნელობებსშეცდომით ახალი კოლექციით განაგრძეთ შემდეგი თითოეული x avVals– ში თუ Len (CStr (x)) შემდეგ "გამოტოვეთ ცარიელი უჯრედები. დამატება x, CStr (x) "თუ დამატებული ელემენტი უკვე არსებობს კოლექციაში, მოხდება შეცდომა "თუ შეცდომა არ არის, ეს მნიშვნელობა ჯერ არ არის შეყვანილი, "დაამატე შედეგად მასივითუ Err = 0 მაშინ li = li + 1 avArr (li, 1) = x სხვა "დარწმუნდით, რომ გაასუფთავეთ შეცდომის ობიექტი Err.Clear End If End If Next End With "ჩაწერეთ შედეგი ფურცელზე, დაწყებული მითითებული უჯრედიდანთუ li მაშინ rResultCell.Cells (1, 1). ზომის შეცვლა (li). ღირებულება = avArr End Sub |
Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "მოითხოვეთ უჯრედების მისამართი უნიკალური მნიშვნელობების შესარჩევად უჯრედების უნიკალური მნიშვნელობების ასარჩევად " , "მონაცემთა მოთხოვნა", "A2: A51", ტიპი: = 8) თუ rVals არაფერია, მაშინ "თუ ღილაკზე გაუქმება დააწკაპუნეთ Exit Sub End If", თუ მხოლოდ ერთი უჯრედია მითითებული , თუ rVals.Count = 1 მაშინ არჩევას აზრი არ აქვს მაშინ MsgBox "უნიკალური მნიშვნელობების ასარჩევად, თქვენ უნდა მიუთითოთ ერთზე მეტი უჯრედი", vbInformation, "www.site" Exit Sub End If "ჩვენ ვწყვეტთ ცარიელ რიგებს და სვეტებს სამუშაო დიაპაზონის გარეთ დაყენება rVals = გადაკვეთა (rVals, rVals.Parent.UsedRange) "თუ მხოლოდ ცარიელი უჯრედებია მითითებული სამუშაო დიაპაზონის გარეთ "(! LANG: მოითხოვეთ უჯრედი შედეგის საჩვენებლად Set rResultCell = Application.InputBox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
მეთოდი 4: საყრდენი ცხრილები
რამდენიმე არასტანდარტული გზავიღებთ უნიკალურ ღირებულებებს.
- აირჩიეთ ერთი ან მეტი სვეტი ცხრილში, გადადით ჩანართზე ჩასმა-ჯგუფი მაგიდა -PivotTable
- დიალოგურ ფანჯარაში შექმენით PivotTableჩვენ ვამოწმებთ მონაცემთა დიაპაზონის (ან ნაკრების) შერჩევის სისწორეს ახალი წყარომონაცემები)
- მიუთითეთ PivotTable-ის მდებარეობა:
- ახალი სამუშაო ფურცელი
- არსებული სამუშაო ფურცელი
- დაადასტურეთ შექმნა ღილაკის დაჭერით კარგი
რადგანაც საყრდენი ცხრილები, სტრიქონების ან სვეტების არეში მოთავსებული მონაცემების დამუშავებისას ირჩევს მათგან მხოლოდ უნიკალურ მნიშვნელობებს შემდგომი ანალიზისათვის, მაშინ ჩვენგან აბსოლუტურად არაფერია საჭირო გარდა საყრდენი ცხრილის შექმნისა და მონაცემების განთავსებისა სასურველი სვეტი რიგების ან სვეტების არეში.
მაგალითად, სტატიისთვის თანდართული ფაილის გამოყენებით, მე:
რა უხერხულობაა ამ შემთხვევაში კრებსით ცხრილებთან მუშაობისას: თუ წყაროს მონაცემები შეიცვლება, კრებსითი ცხრილი ხელით უნდა განახლდეს: აირჩიეთ კრებსითი ცხრილის ნებისმიერი უჯრედი - მაუსის მარჯვენა ღილაკი - განაახლეთან ჩანართი მონაცემები -განაახლეთ ყველაფერი -განაახლეთ... და თუ წყაროს მონაცემები დინამიურად და კიდევ უფრო უარესად შეივსება, საჭირო იქნება წყაროს მონაცემების დიაპაზონის ხელახლა დაზუსტება. და კიდევ ერთი მინუსი - საყრდენი ცხრილის მონაცემების შეცვლა შეუძლებელია. ამიტომ, თუ მომავალში საჭირო იქნება მიღებულ სიაზე მუშაობა, მაშინ შექმნის შემდეგ სასურველი სიაშეჯამების გამოყენებით, ის უნდა იყოს კოპირებული და ჩასმული სასურველ ფურცელზე.
იმისათვის, რომ უკეთ გაიაზროთ ყველა ნაბიჯი და ისწავლოთ საყრდენი ცხრილების გამოყენება, მე გირჩევთ რომ წაიკითხოთ სტატია ზოგადი ინფორმაცია საყრდენი ცხრილების შესახებ - მას თან ერთვის ვიდეო გაკვეთილი, რომელშიც მე ნათლად ვაჩვენებ ძირითადთან მუშაობის სიმარტივეს და მოხერხებულობას საყრდენი ცხრილების მახასიათებლები.
თანდართულ მაგალითში, აღწერილი ტექნიკის გარდა, იწერება ფორმულისა და კოდის უნიკალური ელემენტების მოპოვების ოდნავ უფრო რთული ვარიაცია, კერძოდ: კრიტერიუმებით უნიკალური ელემენტების მოპოვება... რაზე ვსაუბრობთ: თუ გვარის ერთ სვეტში, ხოლო მეორეში (B) არის გარკვეული მონაცემები (ფაილში ეს არის თვეები) და გსურთ ამოიღოთ B სვეტის უნიკალური მნიშვნელობები მხოლოდ არჩეული გვარი. ასეთი უნიკალური მოპოვების მაგალითები განლაგებულია ფურცელზე კრიტერიუმებით მოპოვება.
გადმოწერეთ მაგალითი:
(108.0 KiB, 14,152 ჩამოტვირთვა)
სტატია დაეხმარა? გაუზიარე ბმული მეგობრებს! ვიდეო გაკვეთილები
