В тази статия ще научим как да индексираме и сортираме стойности, използвайки помощната колона в Excel.
Сценарий:
Понякога се работи с необичайни данни. Трябва да вземем по -кратката версия като необходими данни от данните в таблицата въз основа на помощната колона. Тук първо ще разберем как да получим помощната колона и след това ще получим сортираните малки необходими данни въз основа на данните от таблицата.
Как да индексирате стойности в диапазон?
За тази статия ще трябва да използваме функцията COUNTIF. Сега ще направим формула от функцията. Тук дадени смесени текст и числа в диапазон. Трябва първо да ги индексираме във възходящ ред по число, след това по азбучен ред.
Обща формула:-
| = COUNTIF (списък, "<=" & f_value) + (COUNT (списък) * ISTEXT (f_value)) |
списък: списък с числа и текст
f_value: първа стойност на диапазона
Обяснение:
- Функцията COUNTIF брои броя на стойностите в върнатия списък
- Функцията COUNT получава броя на числата в диапазона.
- Функцията ISTEXT проверява дали стойността в избраната клетка е текст или не. Връща TRUE за текст и FALSE за числа.
Пример:
Всичко това може да е объркващо за разбиране. Нека разберем как да получим помощната колона, като използваме обяснената формула. Тук помощната колона трябва да се основава на сортиращите стойности по ред (първо числа и след това по азбучен ред). За това ще използваме формулата, за да получим колоната Index или rank като помощна колона за данните за разходите, показани по -долу.
Тук, за да намерите стойностите в диапазона. За целите на изчислението използваме посочения диапазон за фиксирания масив D5: D12 като разход.
Използвайте формулата:
| = COUNTIF (разход, "<=" & D5) + (COUNT (разход) * ISTEXT (D5)) |
Формулата изглежда така, както е показано на горната снимка. Стойността и масивът са дадени като именен диапазон и препратка към клетка във формулата.
Както можете да видите, индексът на първата клетка в диапазона е 5, което означава, че ако поставим разходите в ред за сортиране, Електричество ще бъдат поставени на 5 -та позиция. Сега копирайте формулата в други клетки, като използвате опцията за плъзгане надолу или като използвате клавишната комбинация Ctrl + D, както е показано по -долу, за да получите индекса или ранга за останалите стойности.
Както можете да видите, сега имаме списък с индекс или ранг, който е начинът на сортиране на разходите. Сега ще използваме тази колона за ранг като помощна колона, за да получим по -късата версия на таблицата със същия ред в новия списък или таблица.
Как да сортирате стойности с помощта на помощна колона в Excel?
Номерът за сортиране в Excel с помощта на помощната колона трябва да има индексни номера и необходим диапазон за сортиране. Помощна колона може да бъде всяка необходима поръчка. Например, ако трябва да получите списъка в необходимия случаен ред, просто поставете ранга, съответстващ на стойността, в колоната с индекс или ранг, която ще работи като помощна колона.
- INDEX функция
- MATCH функция
- ROWS функция
Сега ще направим формула, използвайки горните функции. Функцията MATCH ще върне индекса на най -ниското съвпадение от диапазона. Функцията INDEX приема индекса на ред като аргумент и връща съответните му необходими резултати. Тази функция намира
Обща формула:
| = INDEX (return_array, MATCH (ROWS (relative_reference), Index, 0)) |
return_array: масив, от който се връща стойност
Относителна справка: генерира възходящ ред във формулата. Може да се използва с всеки масив
Index: Индексен масив на таблицата
0: точно съвпадение
Обяснение:
- ROWS (relative_reference) връща стойност според дължината на разширената формула. Ако се приложи в първата клетка, тя ще бъде една, след това втора и ще продължи, докато се разшири.
- Функцията MATCH съвпада с индекса със стойност на ред, за да ги получи във възходящ ред с помощта на функцията ROWS.
- Функцията INDEX връща съвпадащия индекс със съответната стойност.
Пример:
Всичко това може да е объркващо за разбиране. Така че, нека разберем тази формула, като я изпълним в примера, показан по -долу. Тук класирахме данните в произволен ред, за да получим първите три стойности въз основа на колоната Index. Използвайте формулата, за да получите първата стойност на необходимата кратка таблица.
Използвайте формулата:
| = ИНДЕКС (B5: B13, MATCH (ROWS (D5: D5), D5: D13,0)) |
Формулата изглежда така, както е показано на горната снимка. Масивът със стойности се дава като именен диапазон и препратка към клетка.
Както можете да видите, първата стойност е числото от диапазона, което се оказва "Milk", съответният индекс е 1. Сега копирайте формулата в други клетки, като използвате опцията за плъзгане надолу или с клавиша за бърз достъп Ctrl + D, както е показано по -долу, за да получите останалите стойности.
Както можете да видите, стойностите се индексират във възходящ ред. Получихме всички стойности, тъй като това също доказва, че формулата работи добре. Можем да извлечем числовите стойности, използвайки формулата същата формула.
Както можете да видите, имаме по -късите и сортирани стойности от таблицата, използвайки помощната колона, която е индексирана съответно.
Ето някои наблюдателни бележки, показани по -долу.
Бележки:
- Формулата работи само с числа и текст.
- Формулата пренебрегва текстовата стойност, докато сравнява числата и игнорира числата, когато съвпадат текстовите стойности.
- Помощната колона може да бъде всякакъв вид необходим ред на колоната.
Надявам се, че тази статия за това как да индексирате и сортирате стойности с помощна колона в Excel е обяснима. Разгледайте още статии за формули за търсене в Excel тук. Ако ви харесаха нашите блогове, споделете го с приятелите си във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта
Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.
SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.
Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.
2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.
Как да сумирате няколко колони с условие: Връща сумата на стойностите в множество колони, имащи условие в excel
Как да използвате заместващи символи в Excel : Пребройте клетки, съвпадащи с фрази, използвайки заместващи знаци в Excel
Популярни статии
50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.
Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.
Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.
Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.