დალაგება ფორმულის მიხედვით

თუ სიის დახარისხება გჭირდებათ, მაშინ თქვენს სამსახურში ბევრი გზაა, რომელთაგან ყველაზე მარტივია დალაგების ღილაკები ჩანართზე ან მენიუში. თარიღი (მონაცემები - დალაგება). თუმცა არის სიტუაციები, როდესაც სიის დახარისხება საჭიროა ავტომატურად, ანუ ფორმულები. ეს შეიძლება იყოს საჭირო, მაგალითად, ჩამოსაშლელი სიისთვის მონაცემების გენერირებისას, სქემებისთვის მონაცემების გამოთვლისას და ა.შ. როგორ დავახარისხოთ სია ფორმულით პირდაპირ?

მეთოდი 1. რიცხვითი მონაცემები

თუ სია შეიცავს მხოლოდ ციფრულ ინფორმაციას, მაშინ მისი დახარისხება მარტივად შეიძლება გაკეთდეს ფუნქციების გამოყენებით სულ მცირე (ᲞᲐᲢᲐᲠᲐ) и LINE (ROW):

ფუნქცია სულ მცირე (ᲞᲐᲢᲐᲠᲐ) ამოიღებს მასივიდან (სვეტა A) ზედიზედ n-ე ყველაზე პატარა ელემენტს. იმათ. SMALL(A:A;1) არის ყველაზე პატარა რიცხვი სვეტში, SMALL(A:A;2) არის მეორე უმცირესი და ა.შ.

ფუნქცია LINE (ROW) აბრუნებს მწკრივის ნომერს მითითებული უჯრედისთვის, ე.ი. ROW(A1)=1, ROW(A2)=2 და ა.შ. ამ შემთხვევაში, ის გამოიყენება უბრალოდ, როგორც n=1,2,3 რიცხვების თანმიმდევრობის გენერატორი... ჩვენი დალაგებული სია. იმავე წარმატებით, შესაძლებელი გახდა დამატებითი სვეტის შექმნა, მისი ხელით შევსება რიცხვითი თანმიმდევრობით 1,2,3… და ROW ფუნქციის ნაცვლად მასზე მითითება.

მეთოდი 2. ტექსტური სია და რეგულარული ფორმულები

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

პირველ რიგში, მოდით დავამატოთ სერვისის სვეტი ფორმულით, სადაც გამოითვლება თითოეული სახელის სერიული ნომერი მომავალ დახარისხებულ სიაში ფუნქციის გამოყენებით. COUNTIF (COUNTIF):

ინგლისურ ვერსიაში იქნება:

=COUNTIF(A:A,»<"&A1)+COUNTIF($A$1:A1,"="&A1)

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

ახლა მიღებული ნომრები თანმიმდევრულად უნდა დალაგდეს ზრდადი თანმიმდევრობით. ამისათვის შეგიძლიათ გამოიყენოთ ფუნქცია სულ მცირე (ᲞᲐᲢᲐᲠᲐ) პირველი გზიდან:

საბოლოოდ, რჩება მხოლოდ სახელების ამოღება სიიდან მათი ნომრებით. ამისათვის შეგიძლიათ გამოიყენოთ შემდეგი ფორმულა:

ფუნქცია უფრო გამოვლენილი (მატჩი) ეძებს B სვეტში სასურველ სერიულ ნომერს (1, 2, 3 და ა.შ.) და, ფაქტობრივად, აბრუნებს იმ ხაზის ნომერს, სადაც ეს ნომერი მდებარეობს. ფუნქცია ინდექსი (INDEX) ამოიღებს A სვეტიდან სახელს ამ ხაზის ნომერზე.

მეთოდი 3: მასივის ფორმულა

ეს მეთოდი, ფაქტობრივად, იგივე განლაგების ალგორითმია, როგორც მეთოდ-2-ში, მაგრამ განხორციელებული მასივის ფორმულით. ფორმულის გასამარტივებლად, C1:C10 უჯრედების დიაპაზონს მიენიჭა სახელი სია (აირჩიეთ უჯრედები, დააჭირეთ Ctrl + F3 და ღილაკი შექმნა):

E1 უჯრედში დააკოპირეთ ჩვენი ფორმულა:

=INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0))

ან ინგლისურ ვერსიაში:

=INDEX(სია, MATCH(SMALL(COUNTIF(სია, «<"&სია), ROW(1:1)), COUNTIF(სია, "<"& სია), 0))

და დააყენებს Ctrl + Shift + Enterმასივის ფორმულის სახით შეყვანა. შემდეგ მიღებული ფორმულა შეიძლება დაკოპირდეს სიის მთელ სიგრძეზე.

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

პირველ რიგში, სიის დიაპაზონი დინამიურად უნდა დაყენდეს. ამისათვის, შექმნისას, თქვენ უნდა მიუთითოთ არა ფიქსირებული დიაპაზონი C3:C10, არამედ სპეციალური ფორმულა, რომელიც ეხება ყველა არსებულ მნიშვნელობას, მიუხედავად მათი რაოდენობისა. დააწკაპუნეთ Alt + F3 ან გახსენით ჩანართი ფორმულები - სახელების მენეჯერი (ფორმულები - სახელის მენეჯერი)შექმენით ახალი სახელი და ველში ბმული (მითითება) შეიყვანეთ შემდეგი ფორმულა (ვვარაუდობ, რომ დასალაგებელი მონაცემების დიაპაზონი იწყება C1 უჯრედიდან):

=СМЕЩ(C1;0;0;СЧЁТЗ(C1:C1000);1)

=OFFSET(C1,0,0,SCHÖTZ(C1:C1000),1)

მეორეც, ზემოაღნიშნული მასივის ფორმულა უნდა დაიწიოს ზღვრით - მომავალში შეყვანილი დამატებითი მონაცემების მოლოდინით. ამ შემთხვევაში, მასივის ფორმულა დაიწყებს შეცდომის მიცემას #NUMBER უჯრედებზე, რომლებიც ჯერ კიდევ არ არის შევსებული. მის დასაჭერად შეგიძლიათ გამოიყენოთ ფუნქცია Iferror, რომელსაც უნდა დაემატოს ჩვენი მასივის ფორმულა „ირგვლივ“:

=IFERROR(INDEX(List; MATCH(SMALL(COUNTIF(List; "<"&List); ROW(1:1)); COUNTIF(List; "<"&List); 0));»»)

=IFERROR(NDEX( სია, MATCH(SMALL(COUNTIF( სია, «<"&სია), ROW(1:1)), COUNTIF( სია, "<"& სია), 0));"")

ის იჭერს #NUMBER შეცდომას და გამოაქვს ბათილად (ცარიელი ბრჭყალები).

:

• დიაპაზონის დახარისხება ფერის მიხედვით
• რა არის მასივის ფორმულები და რატომ არის ისინი საჭირო
• SORT დახარისხება და დინამიური მასივები ახალ Office 365-ში