Excel Forum - Porady, Pomoc, Excel Help, Excel FAQ - Błąd w formule INDEX

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Dzień dobry
Witam wszystkich jako nowy forumowicz będę starał się pisać jak najbardziej zrozumiale.
Załączony plik zawiera tabelę w której na początek potrzebował bym poprawić formułę
listy rozwijanej w kolumnie B i C

Cytat:

=INDEKS($D$2:$D$40;PODAJ.POZYCJĘ(WIERSZ()-WIERSZ($D$1);$C$2:$C$40;0))

zakładka "suma"
aby w kolumnie F nie pokazywało się 0 i błąd tylko puste pola, których nie będzie widać na liście rozwijanej.

Kolejne problemy przedstawię po rozwiązaniu tego.

Grupy test.xlsx

Pobierz Plik ściągnięto 5 raz(y) 20.98 KB

Maciej Gonet · Wysłany: 12-02-2021, 06:40

Witaj na Forum,
Nie podałeś całościowej koncepcji, ale to co chciałeś przy takich danych możesz uzyskać modyfikując Twoją formułę w dwóch miejscach:

Kod:

=JEŻELI.BŁĄD(INDEKS($D$2:$D$40;PODAJ.POZYCJĘ(WIERSZ()-WIERSZ($D$1)+LICZ.PUSTE($D$2:$D$40);$C$2:$C$40;0));"")

A jeśli w listach rozwijanych nie chcesz mieć pustych pól na końcu, to zmień formułę listy na taką:

Kod:

=PRZESUNIĘCIE(suma!$F$2;0;0;ILE.NIEPUSTYCH(suma!$F$2:$F$40)-LICZ.PUSTE(suma!$F$2:$F$40);1)

Grupy test.xlsx

Pobierz Plik ściągnięto 6 raz(y) 20.55 KB

Grupy test 1.xlsx

Pobierz Plik ściągnięto 3 raz(y) 21.16 KB

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Dziękuję za pomoc działa idealnie.

Załączyłem drugi plik możecie zerknąć czy da się zrobić listę rozwijaną z wyszukiwaniem po wpisaniu danych w komórkę B7 dane z kolumny P (docelowo około 1000)
Nie mam Office365 więc SORTUJ I FILTRUJ odpada :(

Druk ZW.xls

Pobierz Plik ściągnięto 4 raz(y) 215.5 KB

Maciej Gonet · Wysłany: 12-02-2021, 17:17

Nie napisałeś dokładnie o jakie wyszukiwanie chodzi, czy od początku, czy po dowolnym fragmencie.
Zobacz czy tak to chciałeś. Zakresy trzeba dopasować.
Właściwa lista generuje się po wpisaniu fragmentu i naciśnięciu strzałki. Wyszukiwanie po dowolnym fragmencie liczby.

Druk ZW.xls

Pobierz Plik ściągnięto 8 raz(y) 223 KB

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Wielkie, dzięki działa idealnie
Tak chodzi o wyszukiwanie od początku,
a czy da się zrobić aby lista się automatycznie rozwijała i zawężała po wpisywaniu ciągu znaków czy to już wyższa jazda :) ?

Maciej Gonet · Wysłany: 13-02-2021, 09:25

Jeśli lista jest oparta na mechanizmie sprawdzania poprawności, to nie da się osiągnąć efektu modyfikacji rozwiniętej listy. Tu tylko jest wybór pomiędzy dopasowaniem od początku słowa i dopasowaniem do dowolnego fragmentu.
Ten efekt, o którym piszesz można uzyskać za pomocą listy utworzonej z użyciem pola combi.
Przykłady są w sieci. Jeden chociażby tu:
'https://www.youtube.com/watch?v=jmBOxprOvqU
Gdyby to była jedna lista, to dałoby się tak zrobić (lub podobnie). Ale u Ciebie jest kilka takich list, a to już nie będzie wyglądać zbyt estetycznie (trudno jest te pola combi wpasować dokładnie w komórki), ale jak chcesz się pobawić, to spróbuj.
Ewentualnie można kombinować z dodatkowym formularzem do wprowadzania danych.

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Tak, oglądałem ten filmik
A możesz wrzucić przykładowy plik z formułą w jednej komórce z listą rozwijaną i zawęzającą wybór p wpisywaniu.

Cytat:

Gdyby to była jedna lista, to dałoby się tak zrobić (lub podobnie).

Maciej Gonet · Wysłany: 13-02-2021, 15:18

Nie traktuj forum jako firmy usługowej.
W sieci są pliki z przykładami. Trzeba tylko poszukać.
Załączam plik pobrany z sieci:
'https://exceliadam.pl/download/Lista rozwijana z opcję wyszukiwania.xlsm
Strona z filmem instruktażowym:
'https://exceliadam.pl/lista-rozwijana-opcja-wyszukiwania

Lista rozwijana z opcją wyszukiwania_ExceliAdam.xlsm

Pobierz Plik ściągnięto 3 raz(y) 56.44 KB

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Przepraszam, nie traktuję, widzisz w między czasie zrobiłem taką listę.
Chcę korzystać z wiedzy uprzejmych kolegów z forum, którzy wiedzą więcej niż ja.
Do rzeczy.
Zrobiłem w między czasie taką listę i proszę o sprawdzenie poprawności.
Pytanie mam takie:
czy to pole Combo można ukryć a wpisywać w B7 i czy będzie działać tak jak w polu Combo ?
czy da się to sklonować na komórki w dół ?
plik w załączniku.

Druk ZW.xls

Pobierz Plik ściągnięto 5 raz(y) 479.5 KB

Maciej Gonet · Wysłany: 13-02-2021, 17:06

Nie uważam się za specjalistę od kontrolek, ale myślę, że gdyby było można, to ktoś by takie rozwiązanie pokazał. Pytanie jaki sens miałaby ukryta kontrolka?
Można umieścić kontrolkę na tle komórki i wyświetlać ją gdy komórka zostanie wybrana, a chować gdy będzie wybrana inna komórka.
Ale graficznie kontrolka zawsze będzie różnić się od komórki, a poza tym mogą wystąpić różne zakłócenia np. po zmianie komórki aktywnej może pozostać widoczna lista z poprzedniej komórki.
W załączonym pliku pokazałem jak to można zrobić dla jednej komórki.
Dla kilku komórek trzeba by zdefiniować osobne ComboBoxy albo programowo zmieniać (wyliczać) jego położenie.

Druk ZW_1.xls

Pobierz Plik ściągnięto 5 raz(y) 481.5 KB

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Dziękuję Panie Macieju za pomoc.
Jeżeli ktoś by miał inne pomysły to zapraszam do komentowania.
Pozdrawiam

krawiec · Świeżak Wersja: Win Office 2016 Posty: 7

Maciej Gonet · Wysłany: 15-02-2021, 23:12

W takiej postaci jak była poprzednio, ta formuła nie uciągnie 5000 komórek na liście. To tak jakbyś do lokomotywy doczepił 5000 wagonów zamiast 50.
Tamta formuła była napisana tak, żeby można było łatwo zmieniać liczbę pozycji, ale przy kilkudziesięciu pozycjach, a nie przy 5000.
Tam każda formuła była niezależna, ale też każda musiała być osobno przeliczona, więc tego przeliczania było bardzo dużo.
Teraz zmieniłem to na jedną formułę tablicową, czyli w razie potrzeby zmiany trzeba cały zakres zaznaczyć, wyedytować i zatwierdzić całość Ctrl+Shift+Enter.

W tej chwili formuła jest w wersji dopasowania wpisu od początku. Gdybyś potrzebował wrócić do poprzedniej wersji, to zmienia się tylko koniec formuły - zamiast:

Kod:

WIERSZ(1:1)

jest

Kod:

WIERSZ($A$1:$A$5192)

i formuła zatwierdzana od razu w całym zakresie.
Teksty i liczby mogą być na liście bez różnicy.

Druk ZW_1.xlsx

Pobierz Plik ściągnięto 1 raz(y) 102.55 KB