Познавате ли изящния трик за създаване на препратка към заглавие на колона в последната непразна клетка на ред? Най -доброто: Нямате нужда от помощни редове или колони. Толкова е лесно:
Комбинирайте четирите функции IFERROR (), INDEX (), MAX () и IF ()
Управляващият директор по продажбите ще ви изпрати списък на сключените месечно договори за продукти, които се прекратяват ①. Трябва да използвате формула в колона N, за да посочите последния месец от продажбите за всеки продукт - без никакви спомагателни редове или колони. Ако не са сключени повече договори, въведете празна клетка в колоната N.
Този пример, банален на пръв поглед, се оказва здрава гайка, без да се използват помощни линии или колони. като Excel за практика-Читателите разбиват гайката! Имаме следната формула на масива в клетката за решаване на проблеми N2 създаден ②:
{= IFERROR (INDEX ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; COLUMN (B2: M2) -1; -1))); "")}}
Погледнете как работи стъпка по стъпка
Започнете с условието IF (), вградено във формулата на масива: {= IFERROR (ИНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", КОЛОНА (B2: M2) -1, -1)));"")}
Условието IF () създава фиктивен спомагателен ред във формулата на масива и проверява за клетките В2 до М2независимо дали са празни или не. Ако една клетка е празна, стойността -1 се връща в противен случай чрез функцията COLUMN (), съответният номер на колоната минус стойността 1.
Изваждането на 1 се изисква във формулата, тъй като първата колона в таблицата не съдържа името на месеца, а името на продукта. По -долу ще научите как да използвате функцията INDEX () за показване на съответното име на месец, което - ако не сте извадили 1 - поради допълнителната използвана колона А. би било неправилно от точно една колона.
Ако всички клетки в диапазона В2: М2 са празни, стойността -1 (без продажби на продукти) създава грешка, която използваме за представяне на празна клетка. Активната спомагателна линия може да се види на фиг. ③ в ред 3.
В следващата стъпка отчитате най -голямата стойност с функцията MAX (), в която условието IF () е вложено. Това е стойността 12 в ред 3 (колона 13 минус 1; вижте спомагателния ред на фигура ③):
{= IFERROR (ИНДЕКС ($ B $ 1: $ M $ 1;MAX (IF (B2: M2 "", КОЛОНА (B2: M2) -1, -1)));"")}
Предавате тази MAX стойност на функцията INDEX (). След това съответният календарен месец се отчита в ред 1. Областта за данни на функцията INDEX () е областта $ B $ 1: $ M $ 1. Предадената MAX стойност - в примера 12 - означава, че дванадесетата стойност в списъка, т.е. Дек за месец декември:
{= ГРЕШКА(ИНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 ""; КОЛОНА (B2: M2) -1; -1)));"")}
Ако всички клетки в месечния диапазон на таблицата са празни, тогава най -голямата стойност е -1 (вижте условието IF () в началото). Ако стойността -1 се предаде на функцията INDEX (), това неизбежно води до стойност на грешка, тъй като списъчната област на функцията INDEX () съдържа само дванадесет записа и следователно не може да намери записа -1. Улавяте тази стойност на грешка с функцията IFERROR () и вместо това връщате празен низ. В примера това е така за продукт С в ред 4, тъй като не може да се сключи повече договор за този продукт:
{=ГРЕШКА(ИНДЕКС ($ B $ 1: $ M $ 1; MAX (IF (B2: M2 "", КОЛОНА (B2: M2) -1, -1)));"")}
Тъй като това е формула за масив, завършете въвеждането на формулата с клавишната комбинация Ctrl + Shift + Enter.
