Excel: określ kolumnę o określonej wartości

Mam plik programu Excel z tabelą o następującym formacie, jest około 50 kolumn

A B C D E id
1 0 0 0 0  1
0 1 0 0 0  2
0 0 0 0 1  3

Gwarantuje się, że jeden wiersz będzie miał dokładnie 1 wartość, czyli „1”, a pozostałe będą równe 0

Jak mogę przekształcić powyższe w format:

val  id
 A    1
 B    2
 E    3

microsoft-excel

— Akash
źródło

Czy ostatnia wartość nie powinna być E, a nie D?

— XOR LX

Poprawiono literówkę @ XORLX ..

— Akash

Zakładając, że stół, jaki podajesz, znajduje się w A1: F4 (z nagłówkami w wierszu 1), najpierw wprowadź tę formułę w H1:

= SUMA (A2: E4)

Następnie wprowadź tę formułę ** **** w I1:

= JEŚLI (WIERSZE ($ 1: 1) & gt; $ H $ 1, „”, INDEKS ($ A $ 1: $ E $ 1, MAŁE (JEŻELI ($ A $ 2: $ E $ 4, COLUMN ($ A $ 2: $ E $ 4) -MIN (COLUMN ($ A $ 2: $ E $ 4)) + 1), ROWS ($ 1: 1))))

Skopiuj tę formułę (choć nie tą w H1 - to jednorazowe), aż zaczniesz uzyskiwać puste wyniki.

Początkowa klauzula IF jest tak zaprojektowana, że w wierszach, do których formuła jest kopiowana poza oczekiwaną liczbę zwrotów (podaną w formule w H1), zwracane jest puste miejsce. Działa to, ponieważ ROWS ($ 1: 1) (co jest równe 1), po skopiowaniu staje się kolejno ROWS ($ 1: 2) (= 2), ROWS ($ 1: 3) (= 3) itd. Itd. .

Konstrukcja IF w funkcji SMALL generuje tablicę wartości składającą się z względnych numerów kolumn dla wszystkich przypadków, w których gdzieś w tej kolumnie znajduje się 1. Funkcja SMALL, która ma parametr k ROWS ($ 1: 1) jak wyżej, zwraca, w kolejnych wierszach, pierwszą taką wartość, potem drugą taką wartość itd. Itd.

Te indeksy kolumn są następnie przekazywane do INDEX, aby dać odpowiedni wpis z nagłówków.

Następnie wprowadź tę formułę (bez tablicy) w J1:

= JEŻELI (I1 = "", "", INDEKS ($ F $ 2: $ F $ 4, MECZ (1, INDEKS ($ A $ 2: $ E $ 4,, MECZ (I1, $ A 1: $ E 1,0 USD) ), 0)))

Skopiuj zgodnie z wymaganiami.

pozdrowienia

** Formuły tablicowe nie są wprowadzane w taki sam sposób jak formuły „standardowe”. Zamiast naciskać tylko ENTER, najpierw przytrzymaj CTRL i SHIFT, a następnie naciśnij ENTER. Jeśli zrobiłeś to poprawnie, zauważysz, że Excel umieszcza nawiasy klamrowe {} wokół formuły (ale nie próbuj ich ręcznie wstawiać).

— XOR LX
źródło

Czy byłoby możliwe wstawienie wiersza opisującego, co robią te formuły? Czy też są zbyt złożone, aby można je było opisać 1-2 liniami?

— Akash

Oczywiście. Wkrótce dodam kilka linii wyjaśnień.

— XOR LX

Właściwie, zanim to zrobię, zdaję sobie sprawę, że mogłem uczynić to niepotrzebnie skomplikowanym. Czy wzór w H1 można zastąpić po prostu: = SUMA (A2: E4)?

— XOR LX

To daje poprawny wynik

— Akash

Dobrze. Zamiast tego umieściłem tę formułę w poście. Jakiego rodzaju wyjaśnienia chcesz? Szczegółowy podział (który może być dość długi)? Lub po prostu sedno tego, co robi każda część?

— XOR LX