შინაარსი
რა არის დინამიური მასივები
2018 წლის სექტემბერში Microsoft-მა გამოუშვა განახლება, რომელიც ამატებს სრულიად ახალ ინსტრუმენტს Microsoft Excel-ს: Dynamic Arrays და მათთან მუშაობის 7 ახალ ფუნქციას. ეს ყველაფერი, გაზვიადების გარეშე, რადიკალურად ცვლის ფორმულებთან და ფუნქციებთან მუშაობის ჩვეულ ტექნიკას და აწუხებს, სიტყვასიტყვით, ყველა მომხმარებელს.
განვიხილოთ მარტივი მაგალითი არსის ასახსნელად.
დავუშვათ, გვაქვს მარტივი ცხრილი ქალაქ-თვეების მონაცემებით. რა მოხდება, თუ ფურცლის მარჯვნივ ავარჩევთ რომელიმე ცარიელ უჯრედს და შევიტანთ მასში ფორმულას, რომელიც დაუკავშირდება არა ერთ უჯრედს, არამედ დაუყოვნებლივ დიაპაზონს?
Excel-ის ყველა წინა ვერსიაში, დაწკაპუნების შემდეგ შეიყვანეთ ჩვენ მივიღებთ მხოლოდ ერთი პირველი უჯრედის B2 შიგთავსს. სხვა როგორ?
კარგად, ან შესაძლებელი იქნებოდა ამ დიაპაზონის შეფუთვა რაიმე სახის აგრეგაციის ფუნქციაში, როგორიცაა =SUM(B2:C4) და ამისთვის გრანდიოზული ჯამის მიღება.
თუ ჩვენ გვჭირდებოდა უფრო რთული ოპერაციები, ვიდრე პრიმიტიული ჯამი, როგორიცაა უნიკალური მნიშვნელობების ამოღება ან ტოპ 3, მაშინ უნდა შეგვეტანა ჩვენი ფორმულა, როგორც მასივის ფორმულა კლავიატურის მალსახმობის გამოყენებით. Ctrl+გადაიტანოს+შეიყვანეთ.
ახლა ყველაფერი სხვაგვარადაა.
ახლა ასეთი ფორმულის შეყვანის შემდეგ, ჩვენ შეგვიძლია უბრალოდ დააჭიროთ შეიყვანეთ – და შედეგად დაუყოვნებლივ მიიღეთ ყველა ის მნიშვნელობა uXNUMXbuXNUMXb, რომელსაც ჩვენ მივმართეთ:
ეს არ არის ჯადოსნური, მაგრამ ახალი დინამიური მასივები, რომლებიც ახლა Microsoft Excel-ს აქვს. კეთილი იყოს თქვენი მობრძანება ახალ სამყაროში 🙂
დინამიურ მასივებთან მუშაობის თავისებურებები
ტექნიკურად, მთელი ჩვენი დინამიური მასივი ინახება პირველ უჯრედში G4, რომელიც ავსებს უჯრედების საჭირო რაოდენობას მარჯვნივ და ქვევით თავისი მონაცემებით. თუ თქვენ აირჩევთ მასივის რომელიმე სხვა უჯრედს, მაშინ ბმული ფორმულების ზოლში იქნება უმოქმედო, რაც აჩვენებს, რომ ჩვენ ვართ ერთ-ერთ „ბავშვთა“ უჯრედში:
ერთი ან მეტი "ბავშვის" უჯრედის წაშლის მცდელობა არაფერზე არ მიგვიყვანს - Excel დაუყოვნებლივ გადათვლის და შეავსებს მათ.
ამავდროულად, ჩვენ შეგვიძლია უსაფრთხოდ მივმართოთ ამ "ბავშვის" უჯრედებს სხვა ფორმულებში:
თუ თქვენ დააკოპირებთ მასივის პირველ უჯრედს (მაგალითად, G4-დან F8-მდე), მაშინ მთელი მასივი (მისი მითითებები) გადავა იმავე მიმართულებით, როგორც ჩვეულებრივ ფორმულებში:
თუ ჩვენ გვჭირდება მასივის გადატანა, მაშინ საკმარისი იქნება გადაადგილება (მაუსით ან კომბინაციით Ctrl+X, Ctrl+V), ისევ, მხოლოდ პირველი მთავარი უჯრედი G4 – მის შემდეგ ის ახალ ადგილას გადაინაცვლებს და ჩვენი მთელი მასივი კვლავ გაფართოვდება.
თუ თქვენ გჭირდებათ ფურცელზე სხვაგან მიუთითოთ შექმნილი დინამიური მასივი, მაშინ შეგიძლიათ გამოიყენოთ სპეციალური სიმბოლო # ("ფუნტი") მისი წამყვანი უჯრედის მისამართის შემდეგ:
მაგალითად, ახლა თქვენ შეგიძლიათ მარტივად შექმნათ ჩამოსაშლელი სია უჯრედში, რომელიც ეხება შექმნილ დინამიურ მასივს:
დინამიური მასივის შეცდომები
მაგრამ რა მოხდება, თუ არ არის საკმარისი სივრცე მასივის გასაშლელად, ან თუ მის გზაზე არის უჯრედები, რომლებიც უკვე დაკავებულია სხვა მონაცემებით? შეხვდით ფუნდამენტურად ახალი ტიპის შეცდომებს Excel-ში – #ᲒᲐᲓᲐᲪᲔᲛᲐ! (#დაღვრა!):
როგორც ყოველთვის, თუ ჩვენ დავაწკაპუნებთ ხატს ყვითელი ბრილიანტით და ძახილის ნიშნით, მივიღებთ პრობლემის წყაროს უფრო დეტალურ ახსნას და შეგვიძლია სწრაფად ვიპოვოთ ჩარევის უჯრედები:
მსგავსი შეცდომები მოხდება, თუ მასივი გადის ფურცლიდან ან მოხვდება გაერთიანებულ უჯრედში. თუ დაბრკოლებას მოაცილებთ, მაშინ ყველაფერი დაუყოვნებლივ გამოსწორდება.
დინამიური მასივები და ჭკვიანი მაგიდები
თუ დინამიური მასივი მიუთითებს კლავიატურის მალსახმობით შექმნილ „ჭკვიან“ ცხრილზე Ctrl+T ან მთავარი – ფორმატირება როგორც ცხრილი (მთავარი - ფორმატირება როგორც ცხრილი), მაშინ ის ასევე დაიმკვიდრებს თავის ძირითად ხარისხს - ავტომატური ზომის.
ახალი მონაცემების ბოლოში ან მარჯვნივ დამატებისას, ჭკვიანი ცხრილი და დინამიური დიაპაზონი ასევე ავტომატურად გაიჭიმება:
თუმცა, არსებობს ერთი შეზღუდვა: ჩვენ არ შეგვიძლია გამოვიყენოთ დინამიური დიაპაზონის მითითება ჭკვიანი ცხრილის შიგნით ფორუმებში:
დინამიური მასივები და Excel-ის სხვა ფუნქციები
კარგი, შენ ამბობ. ეს ყველაფერი საინტერესო და სასაცილოა. არ არის საჭირო, როგორც ადრე, ფორმულის ხელით გაჭიმვა ორიგინალური დიაპაზონის პირველი უჯრედის მითითებით ქვემოთ და მარჯვნივ და ეს ყველაფერი. Და სულ ეს არის?
მთლად ასე არა.
დინამიური მასივები არ არის მხოლოდ კიდევ ერთი ინსტრუმენტი Excel-ში. ახლა ისინი ჩაშენებულია Microsoft Excel-ის გულში (ან ტვინში) - მისი გაანგარიშების ძრავა. ეს ნიშნავს, რომ ახლა ჩვენთვის ნაცნობი Excel-ის სხვა ფორმულები და ფუნქციები ასევე მხარს უჭერენ დინამიურ მასივებთან მუშაობას. მოდით შევხედოთ რამდენიმე მაგალითს, რათა წარმოდგენა მოგცეთ მომხდარი ცვლილებების სიღრმეზე.
გადაადგილება
დიაპაზონის გადასატანად (სტრიქონების და სვეტების შეცვლა) Microsoft Excel-ს ყოველთვის ჰქონდა ჩაშენებული ფუნქცია ტრანსპ (ტრანსპოზირება). თუმცა, მის გამოსაყენებლად ჯერ სწორად უნდა აირჩიოთ შედეგების დიაპაზონი (მაგალითად, თუ შეყვანის დიაპაზონი იყო 5×3, მაშინ უნდა აირჩიოთ 3×5), შემდეგ შეიყვანოთ ფუნქცია და დააჭიროთ კომბინაცია Ctrl+გადაიტანოს+შეიყვანეთ, რადგან მას შეეძლო მუშაობა მხოლოდ მასივის ფორმულის რეჟიმში.
ახლა შეგიძლიათ უბრალოდ აირჩიოთ ერთი უჯრედი, შეიყვანოთ მასში იგივე ფორმულა და დააწკაპუნოთ ნორმაზე შეიყვანეთ – დინამიური მასივი ყველაფერს თავისით გააკეთებს:
გამრავლების ცხრილი
ეს არის მაგალითი, რომელიც მე მოვიყვანე, როდესაც მთხოვეს ვიზუალურად გამომეჩინა მასივის ფორმულების უპირატესობები Excel-ში. ახლა, მთელი პითაგორას ცხრილის გამოსათვლელად, საკმარისია დავდგეთ პირველ უჯრედში B2, შეიყვანოთ ფორმულა, რომელიც ამრავლებს ორ მასივს (1..10 რიცხვების ვერტიკალური და ჰორიზონტალური ნაკრები) და უბრალოდ დააწკაპუნეთ შეიყვანეთ:
წებოვნება და კორპუსის გარდაქმნა
მასივები შეიძლება არა მხოლოდ გამრავლდეს, არამედ სტანდარტული ოპერატორის & (აპერსანდი) ერთად დამაგრდეს. დავუშვათ, რომ ორი სვეტიდან უნდა ამოვიღოთ სახელი და გვარი და ჩავასწოროთ გადახტომის შემთხვევა თავდაპირველ მონაცემებში. ჩვენ ამას ვაკეთებთ ერთი მოკლე ფორმულით, რომელიც ქმნის მთელ მასივს, შემდეგ კი მასზე ვიყენებთ ფუნქციას პროპნახი (სათანადო)რეესტრის მოსაწესრიგებლად:
დასკვნა ტოპ 3
დავუშვათ, რომ გვაქვს რიცხვების თაიგული, საიდანაც გვინდა გამოვიტანოთ სამი საუკეთესო შედეგი, დავალაგოთ ისინი კლებადობით. ახლა ეს კეთდება ერთი ფორმულით და ისევ ყოველგვარი Ctrl+გადაიტანოს+შეიყვანეთ როგორც ადრე:
თუ გსურთ, რომ შედეგები განთავსდეს არა სვეტში, არამედ ზედიზედ, მაშინ საკმარისია ამ ფორმულის ორწერტილები (ხაზის გამყოფი) ჩაანაცვლოთ მძიმით (ელემენტების გამყოფი ერთი ხაზის ფარგლებში). Excel-ის ინგლისურ ვერსიაში ეს გამყოფები არის მძიმით და მძიმეებით.
VLOOKUP ამოიღებს რამდენიმე სვეტს ერთდროულად
ფუნქციები VPR (VLOOKUP) ახლა თქვენ შეგიძლიათ ამოიღოთ მნიშვნელობები არა ერთიდან, არამედ რამდენიმე სვეტიდან ერთდროულად - უბრალოდ მიუთითეთ მათი რიცხვები (ნებისმიერი სასურველი თანმიმდევრობით) როგორც მასივი ფუნქციის მესამე არგუმენტში:
OFFSET ფუნქცია აბრუნებს დინამიურ მასივს
მონაცემთა ანალიზისთვის ერთ-ერთი ყველაზე საინტერესო და სასარგებლო (VLOOKUP-ის შემდეგ) ფუნქცია არის ფუნქცია განკარგვა (OFFSET), რომელსაც ერთ დროს მივუძღვენი ჩემი წიგნის მთელი თავი და აქ სტატია. ამ ფუნქციის გაგებისა და დაუფლების სირთულე ყოველთვის იყო ის, რომ შედეგად მან დააბრუნა მონაცემების მასივი (დიაპაზონი), მაგრამ ჩვენ ვერ დავინახეთ, რადგან Excel-მა ჯერ კიდევ არ იცოდა როგორ ემუშავა მასივებს გარეთ.
ახლა ეს პრობლემა წარსულშია. ნახეთ, როგორ ახლა, ერთი ფორმულისა და OFFSET-ის მიერ დაბრუნებული დინამიური მასივის გამოყენებით, შეგიძლიათ ამოიღოთ ყველა მწკრივი მოცემული პროდუქტისთვის ნებისმიერი დახარისხებული ცხრილიდან:
მოდით შევხედოთ მის არგუმენტებს:
- A1 - საწყისი უჯრედი (საცნობარო წერტილი)
- ПОИСКПОЗ(F2;A2:A30;0) - გადაადგილების გაანგარიშება საწყისი უჯრედიდან ქვემოთ - პირველ ნაპოვნი კომბოსტომდე.
- 0 – „ფანჯრის“ ცვლა მარჯვნივ საწყისი უჯრედის მიმართ
- СЧЁТЕСЛИ(A2:A30;F2) - დაბრუნებული "ფანჯრის" სიმაღლის გაანგარიშება - ხაზების რაოდენობა, სადაც არის კომბოსტო.
- 4 — „ფანჯრის“ ზომა ჰორიზონტალურად, ანუ გამომავალი 4 სვეტი
დინამიური მასივების ახალი ფუნქციები
ძველ ფუნქციებში დინამიური მასივის მექანიზმის მხარდაჭერის გარდა, რამდენიმე სრულიად ახალი ფუნქცია დაემატა Microsoft Excel-ს, რომლებიც გამახვილებულია სპეციალურად დინამიურ მასივებთან მუშაობისთვის. კერძოდ, ესენია:
- GRADE (სორტირება) – ახარისხებს შეყვანის დიაპაზონს და აწარმოებს დინამიურ მასივს გამომავალზე
- SORTPO (ᲓᲐᲚᲐᲒᲔᲑᲐ) - შეუძლია ერთი დიაპაზონის დახარისხება მეორის მნიშვნელობების მიხედვით
- ფილტრი (ფილტრი) – ამოიღებს რიგებს წყაროს დიაპაზონიდან, რომლებიც აკმაყოფილებენ მითითებულ პირობებს
- უნიკალური (უნიკალური) - ამოიღებს უნიკალურ მნიშვნელობებს დიაპაზონიდან ან შლის დუბლიკატებს
- SLMASSIVE (RANDARRAY) – წარმოქმნის მოცემული ზომის შემთხვევითი რიცხვების მასივს
- მშობიარობის შემდგომ (SEQUENCE) — აყალიბებს მასივს რიცხვების მიმდევრობიდან მოცემული საფეხურით
მეტი მათ შესახებ - ცოტა მოგვიანებით. ისინი ღირს ცალკე სტატია (და არა ერთი) გააზრებული შესწავლისთვის 🙂
დასკვნები
თუ თქვენ წაიკითხეთ ყველაფერი ზემოთ დაწერილი, მაშინ ვფიქრობ, უკვე ხვდებით განხორციელებული ცვლილებების მასშტაბებს. ამდენი რამ Excel-ში ახლა შეიძლება გაკეთდეს უფრო მარტივად, მარტივად და უფრო ლოგიკურად. უნდა ვაღიარო, რომ ცოტა შოკირებული ვარ იმით, რამდენი სტატიის გასწორება მომიწევს ახლა აქ, ამ საიტზე და ჩემს წიგნებში, მაგრამ მე მზად ვარ ამის გაკეთება მსუბუქი გულით.
შედეგების შეჯამება, pluses დინამიური მასივები, შეგიძლიათ დაწეროთ შემდეგი:
- შეგიძლიათ დაივიწყოთ კომბინაცია Ctrl+გადაიტანოს+შეიყვანეთ. Excel ახლა ვერ ხედავს განსხვავებას "რეგულარულ ფორმულებსა" და "მაივის ფორმულებს" შორის და მათ იგივენაირად ექცევა.
- ფუნქციის შესახებ პროდუქტი (SUMPRODUCT), რომელიც ადრე გამოიყენებოდა მასივის ფორმულების გარეშე შესაყვანად Ctrl+გადაიტანოს+შეიყვანეთ თქვენ ასევე შეგიძლიათ დაივიწყოთ - ახლა ეს საკმაოდ მარტივია თანხა и შეიყვანეთ.
- ჭკვიანი ცხრილები და ნაცნობი ფუნქციები (SUM, IF, VLOOKUP, SUMIFS და ა.შ.) ახლა ასევე სრულად ან ნაწილობრივ მხარს უჭერს დინამიურ მასივებს.
- არსებობს ჩამორჩენილი თავსებადობა: თუ Excel-ის ძველ ვერსიაში გახსნით სამუშაო წიგნს დინამიური მასივებით, ისინი გადაიქცევიან მასივის ფორმულებად (ხვეულ ბრეკეტებში) და გააგრძელებენ მუშაობას „ძველი სტილით“.
იპოვა რაღაც ნომერი მინუსები:
- თქვენ არ შეგიძლიათ წაშალოთ ცალკეული სტრიქონები, სვეტები ან უჯრედები დინამიური მასივიდან, ანუ ის ცხოვრობს როგორც ერთი ერთეული.
- თქვენ არ შეგიძლიათ დინამიური მასივის დახარისხება ჩვეულებრივი გზით მონაცემები – დახარისხება (მონაცემები - დალაგება). ახლა ამისთვის სპეციალური ფუნქციაა. GRADE (სორტირება).
- დინამიური დიაპაზონი ვერ გადაიქცევა სმარტ ცხრილად (მაგრამ შეგიძლიათ დინამიური დიაპაზონის შექმნა ჭკვიანი ცხრილის საფუძველზე).
რა თქმა უნდა, ეს არ არის დასასრული და დარწმუნებული ვარ, Microsoft მომავალშიც გააგრძელებს ამ მექანიზმის გაუმჯობესებას.
საიდან შემიძლია გადმოვწერო?
და ბოლოს, მთავარი კითხვა 🙂
Microsoft-მა პირველად გამოაცხადა და აჩვენა დინამიური მასივების გადახედვა Excel-ში ჯერ კიდევ 2018 წლის სექტემბერში კონფერენციაზე. Ignite. მომდევნო რამდენიმე თვეში, პირველად მოხდა ახალი ფუნქციების საფუძვლიანი ტესტირება და ჩართვა კატები თავად Microsoft-ის თანამშრომლები, შემდეგ კი მოხალისე ტესტერები Office Insiders-ის წრიდან. წელს, განახლება, რომელიც ამატებს დინამიურ მასივებს, დაიწყო თანდათანობით გავრცელება Office 365-ის რეგულარული აბონენტებისთვის. მაგალითად, მე მხოლოდ აგვისტოში მივიღე ჩემი Office 365 Pro Plus (თვიური მიზნობრივი) გამოწერით.
თუ თქვენს Excel-ს ჯერ არ აქვს დინამიური მასივები, მაგრამ ნამდვილად გსურთ მათთან მუშაობა, მაშინ არის შემდეგი პარამეტრები:
- თუ თქვენ გაქვთ Office 365-ის გამოწერა, შეგიძლიათ უბრალოდ დაელოდოთ სანამ ეს განახლება თქვენამდე მიაღწევს. რამდენად სწრაფად მოხდება ეს დამოკიდებულია იმაზე, თუ რამდენად ხშირად მიეწოდება განახლებები თქვენს ოფისს (წელიწადში ერთხელ, ექვს თვეში ერთხელ, თვეში ერთხელ). თუ თქვენ გაქვთ კორპორატიული კომპიუტერი, შეგიძლიათ სთხოვოთ თქვენს ადმინისტრატორს დააყენოს განახლებები უფრო ხშირად ჩამოსატვირთად.
- თქვენ შეგიძლიათ შეუერთდეთ Office Insiders-ის ტესტის მოხალისეებს – მაშინ თქვენ პირველი იქნებით, ვინც მიიღებთ ყველა ახალ ფუნქციას და ფუნქციას (მაგრამ Excel-ში შეცდომების გაზრდის შანსი, რა თქმა უნდა, არსებობს).
- თუ თქვენ არ გაქვთ გამოწერა, მაგრამ გაქვთ Excel-ის დამოუკიდებელი ვერსია, მაშინ მოგიწევთ ლოდინი 2022 წელს Office-ისა და Excel-ის შემდეგი ვერსიის გამოშვებამდე მაინც. ასეთი ვერსიების მომხმარებლები იღებენ მხოლოდ უსაფრთხოების განახლებებს და შეცდომების გამოსწორებას და ყველა ახალი „კარგი“ ახლა მხოლოდ Office 365-ის აბონენტებს გადაეცემათ. სამწუხარო, მაგრამ მართალია 🙂
ნებისმიერ შემთხვევაში, როდესაც დინამიური მასივები გამოჩნდება თქვენს Excel-ში - ამ სტატიის შემდეგ, თქვენ მზად იქნებით ამისთვის 🙂
- რა არის მასივის ფორმულები და როგორ გამოვიყენოთ ისინი Excel-ში
- ფანჯრის (დიაპაზონის) შეჯამება OFFSET ფუნქციის გამოყენებით
- Excel-ში ცხრილის გადატანის 3 გზა