excel-036 - Zakresy dynamiczne cz.9 - ADR.POŚR i PODAJ.POZYCJĘ

Рет қаралды 32,129

Күн бұрын

Пікірлер: 44

@pmsocho 12 жыл бұрын

Ja to bym zrobił w ten sposób: Zakładamy że dane znajdują się od kolumny G. Formułę wpisujemy w komórce F1. Informację o ile kolumn w prawo chcemy się przesuwać od G umieszczamy w komórce E1. Formuła w F1 mogłaby wyglądać na przykład tak: =ILE.NIEPUSTYCH(PRZESUNIĘCIE(G1;0;E1;1048576;1)) 1048576 to oczywiście liczba wszystkich wierszy z kolumny. Gdybyś chciał aby formuła działała dobrze również w wersjach Excela poniżej 2007 to zamiast 1048576 mógłbyś wpisać: ILE.WIERSZY(G:G)

@999dudus 11 жыл бұрын

Pozwolę sobie zadać pytanie może nie dokładnie pasujące to tematu filmiku ale związane z excelem. Czy da się za pomocą jakiejś fomuły np w "a1" wstawić wartość do komórki np. "M1"?

@BillSzysz1 11 жыл бұрын

Naszło mnie takie pytanie - Czy da się zbudować dynamiczną listę rozwijalną bez pustych wierszy wewnątrz niej (tej listy) wykorzystując tylko formuły Excelowe? Hmm..czy jest to możliwe bez kolumny pomocniczej??

@john232017 9 жыл бұрын

Obejrzałem kilka filmików ale nie znalazłem odpowiedzi na mój problem. Otóż korzystając z dwóch arkuszy na pierwszym tabelka z danymi na drugim próbuje zrobić tabele z listami rozwijanymi zależnymi od siebie coraz wiekszy wybór(kilka kolumn) ale po w wpisaniu źródła pojawia się komunikat ,, nie można odnaleźć zakresu o podanej nazwie" lub przy innych kombinacjach pojawiają się to samo co w pierwszej liście. Jakaś podpowiedź?

@pmsocho 9 жыл бұрын

+john232017 Pobierz plik który udostępniam (link w opisie pod filmem) i zrób wszystko identycznie jak na filmie - musi działać! Potem dopiero spróbuj przełożyć to na Twoje dane.

@john232017 9 жыл бұрын

+pmsocho Wielkie dzięki podziałało :)

@pmsocho 9 жыл бұрын

+john232017 Świetnie!

@stefanburczymucha945 11 жыл бұрын

W moim excelu zatwierdzenie formuły jak na filmiku w 2:30 powoduje wyświetlenie pierwszej wartości z wyliczonej tablicy, a nie błąd #ARG! Czy to zależy od wersji excela? Zresztą zauważyłem, że mój excel często nie przystaje do opisów w pomocy, zwracając inne wyniki niż zakłada to pomoc Microsoftu. Dzięki za kolejny super filmik.

@pmsocho 11 жыл бұрын

A wszystko masz identycznie jak ja na filmie. Dane i formuły są w identycznych komórkach? Ma to znaczenie bo jeśli napisałeś formułę w innej komórce to może ona zwracać wynik z "Implicit intersection" - nie wiem jak to po polsku się nazywa :)

@MrKubzol 12 жыл бұрын

Naprawdę dobra, rzetelna robota. Twoje filmiki są bardzo pomocne i nie przegadane. Powiedz mi, proszę, jak "dodać" daną wartość do "numeru" kolumny. Np. chcę, żeby w mojej komórce wyświetlana była liczba niepustych komórek z całej kolumny G+a, gdzie a jest liczbą widniejącą obok komórki, w której wpisuję formułę. Oczywiście jako "G+3" rozumiem "J". Wiem, że formuła "zaznaczająca" kolumnę np. J to "J:J".

@pmsocho 11 жыл бұрын

Załóżmy że dane oryginalne są w zakresie A1:A100. W komórce D1 można napisać formułę która będzie wybierała z zakresu A1:A100 dane tylko z niepustych komórek i następnie skopiować tę formułę w dół (w kolumnie D). Potem na danych z kolumny D można zbudować rozwijaną listę.

@BillSzysz1 11 жыл бұрын

Alternatywa z wykorzystaniem adresowania W1K1: =ILE.NIEPUSTYCH(ADR.POŚR("K"&NR.KOLUMNY(G1)+E1;0)) Formułę wpisujemy do F1 a w E1 wpisujemy ilość kolumn o jaką ma sie przesunąc liczenie niepustych zaczynając od kolumny G. Pozdrawiam :)

@lukaszucek 9 жыл бұрын

A czy jest mozliwosc stworzenia listy wyboru w ktorej rowniez mozna wprowadzac dane recznie?

@pmsocho 9 жыл бұрын

Łukasz Kucek Jeśli miałoby to działać tak, że lista zawiera tylko najczęściej używane elementy ale oprócz nich chciałbyś mieć możliwość wprowadzenia czegoś co nie jest na liście to TAK. Wystarczy w okienku "Poprawność danych", w karcie "Alert o błędzie" odznaczyć opcję "Pokazuj alerty po wprowadzeniu niepoprawnych danych". Ale miej na uwadze, że wartości wpisane ręcznie, nie pojawią się na rozwijanej liście.

@lukaszucek 9 жыл бұрын

pmsocho Nie wierze że to było takie proste w życiu bym tam nie zagladnał xD DziękiA jeszcze bym miał pytanie z innej beczki. Czy masz jakiś filmik(bo nie mogę znaleść) w którym w komórkę wpisuje skrócony kod artykułu i zatwierdzając enterem wyskakuje mi pełna nazwa produktu. np mam w bazie danych wypisane artykuły: pomidor21, pomidor 35, ogórek34, cebula22, jabłko45 itp. I wpisuje w komórkę unikalny kod przypisany do kazdego z nich np dla pomidora P21 i po enterze mi wyskakuje pełna nazwa czyli pomidor 21. Czy to jest problem dla excela czy może bardziej accessa. Jeżeli masz filmik z takim problemem to bardzo bym prosił o link. Jeszcze raz dzięki za pomoc:)

@pmsocho 9 жыл бұрын

Łukasz Kucek Niestety nie ma w Excelu wbudowanego mechanizmu który działałby tak jak chcesz - trzeba by było napisać makro które monitorowałoby to co jest wpisywane w komórce i po zaakceptowaniu przeszukiwałoby listę z której dopasowywane byłyby pełne nazwy. Możesz także (ale będzie to pracochłonne) dodać do autokorekty wpisy skrócone i pełne - wówczas po wpisaniu "zzz" Excel automatycznie zamieniałby to na np. "Zupa pomidorowa". Musiałbyś pewnie sporo takich wpisów dodać ręcznie - chyba że da się dobrać do autokorekty przez VBA - ale tego nie jestem pewien :)

@lukaszucek 9 жыл бұрын

Pomysł autokorekt to własnie to czego szukam. Tylko zrezygnuje z niego. Nie będę 700 typów ręcznie robię xd więc kieruje się w stronę makro. Dzięki za pomoc !

@pmsocho 9 жыл бұрын

Łukasz Kucek :)

@BillSzysz1 11 жыл бұрын

Widzę że budujesz zakresy dynamiczne w oparciu o funkcje INDEX, PODAJ.POZYCJE,PRZESUNIECIE (i bardzo fajnie to wychodzi) więc założyłem sobie ze spróbuję zbudować taki zakres (dynamiczny) bez tych funkcji.Dla powyższego filmiku wyszło mi coś takiego: =ADR.POŚR("$B$7:$B$"&MAX(NIE(CZY.PUSTA($B$7:$B$20))*WIERSZ($B$7:$B$20))) Dane w zakresie mogą być mieszane (text, liczby) jednak same spacje w komórce lub formuły zwracające ciąg pusty zaburzają jej działanie. Kurs jest na 6 (celująco) ;)))

@BillSzysz1 11 жыл бұрын

Oczywiście że można z wykorzystaniem kolumny pomocniczej ale czy da się taką listę zbudować bez niej? W sumie zagadnienie czysto akademickie bo nie mam nic przeciw kolumnom ( wierszom) pomocniczym a nawet jestem mocno za ;))) Pozdrawiam

@tomasznoszczyk9481 4 жыл бұрын

Panie Piotrze, a czy jest jakaś funkcja odwrotna do ADR.POŚR? Potrzebuję aby excel przeszukał całą kolumnę, znalazł konkretną wartość (wskazaną przeze mnie) i zwrócił mi adres komórki w której znajduje się ta wartość np. C10. Pozdrawiam

@pmsocho 4 жыл бұрын

Cześć Tomku, Zapewne przydadzą się funkcje: ADRES, NR.KOLUMNY, PODAJ.POZYCJĘ :)

@pmsocho 11 жыл бұрын

Nie znam sposobu na osiągnięcie takiego efektu bez VBA. Nie bez użycia formuły w M1.

@pumex030 4 жыл бұрын

Z ciekawości podpinam się do pytania sprzed paru lat. Czy istnieje obecnie możliwość wykonania takiej listy z pomijaniem pustych komórek? Pewnie w VBA by szło, ale bardziej chodzi o formułę. Wymyśliłem coś takiego - "wypluwa" to dobre wartości, usuwa puste i reaguje zarówno na teksty, jak i wartości. =PRZESUNIĘCIE($B$7;MIN.K(JEŻELI(($B$7:$B$25"")*(WIERSZ($B$7:$B$25)-WIERSZ($B$7)+1)=0;"";($B$7:$B$25"")*(WIERSZ($B$7:$B$25)-WIERSZ($B$7)+1));WIERSZ(ADR.POŚR("A1"&":"&"A"&ILE.WIERSZY($B$7:$B$25)-LICZ.PUSTE($B$7:$B$25))))-1;0;1;1) Problem w tym, że nie chce to działać zarówno w liście rozwijanej, jak i po nadaniu nazwy - w obu przypadkach daje tylko pierwszy wynik i nic poza tym. Masz może pomysł co w tym przypadku może być problemem?

@pumex030 4 жыл бұрын

W sumie widzę, że problemem jest odwołanie tablicowe do wiersza funkcji przesunięcie, bo na prostych danych zachowuje się podobnie. Trochę to dziwne, bo wynik po podświetleniu F9 wygląda w pełni normalnie, a mimo to formuła nie chce poprawnie działać.

@pumex030 4 жыл бұрын

Problemem jest pewnie dereferencja, ale nawet pomimo testowego zrobienia z danych samych wartości i wrzucenia w funkcję N nadal w liście rozwijanej nie chce działać, po wyskakuje komunikat, że jest błąd. Ok, to powracam do pierwotnego pytania - czy istnieje obecnie jakiś sposób rozwiązania tego problemu formułą?

@pmsocho 4 жыл бұрын

Niestety tak to działa. Też kiedyś walczyłem, kombinowałem i poległem. Jeśli znajdziesz jakieś działające rozwiązanie - koniecznie daj znać!

@pumex030 4 жыл бұрын

@@pmsocho Próbowałem jeszcze na wiele różnych sposobów, ale nawet przy największych uproszczeniach, żadna z metod nie chciała działać. Chyba jednak zadanie to jest nie wykonalne na ten moment w Excelu (bez kolumny pomocniczej)... Mimo wszystko jakbym kiedyś trafił na rozwiązanie, to na pewno dam znać, :)

@pmsocho 4 жыл бұрын

@@pumex030 👍

@einsteinjakub 11 жыл бұрын

Czy jest możliwość wykorzystania adresu pośrdniego w polu kombi zamiast listach rozwijanych? Mam do przygotownia listy zawężające województwo/powiat/gmina. Listy rozwijane są do zrobienia bez probleu ale zdecydowanie lepiej wygląda i bardziej funkcjonalne jest pole kombi formularza,

@pmsocho 11 жыл бұрын

Z tego co kojarzę to można to oczywiście zrobić tak jak piszesz - pogoogluj, pokombinuj i jak przetestujesz to daj znać czy działa :)

@kovval1000 8 жыл бұрын

Potrzebuję pomocy, szukam funkcji, która z pozycji wiersza i nazwy kolumny wybierze mi odpowiednią wartość wewnątrz tabeli. np. z listy rozwijanej tabela 4 wiersz 7 kolumna b. Na podstawie takich rozwijanych list chciałbym dostać zwróconą wartość. Wydaje mi się, że jest to możliwe do dokonania w excelu ale nie wiem dokładnie jak. Proszę o pomoc.

@pmsocho 8 жыл бұрын

Funkcja INDEX jest stworzona dokładnie do tego co opisujesz!

@kovval1000 8 жыл бұрын

Dzięki, porobiłem już trochę funkcji, ale utknąłem w pewnym momencie. moja funkcja wygląda mniej więcej tak: =JEŻELI(I3=A2;INDEKS(C4:H19;J3;L3); JEŻELI(I3=A3;INDEKS(C20:H35;J3;L3); JEŻELI(I3=A4;INDEX(C36:H51;J3;L3); JEŻELI(I3=A5;INDEX(C52:H67;J3;L3); JEŻELI(I3=A6;INDEX(C68:H83;J3;L3); JEŻELI(I3=A7;INDEX(C84:H99;J3;L3); JEŻELI(I3=A8;INDEX(C100:H115;J3;L3); JEŻELI(I3=A9;INDEX(C116:H131;J3;L3); JEŻELI(I3=A10;INDEX(C132:H147;J3;L3);0))))))))) Dostaje jednak odpowiedź, że nie mogę wprowadzić podanej formuły, ponieważ użyto na niej więcej poziomów zagnieżdżenia. Fama kombinacja wydaje mi się też niezbyt optymalna jednak na chwile obecną nie mam innych pomysłów. Dało by się to czymś zastąpić? jakimś odpowiednikiem case z c++?

@pmsocho 8 жыл бұрын

Hmm, wydaje mi się że trochę przekombinowałeś. Żeby nie bawić się w kotka i myszkę prześlij przykładowe dane na maila do mnie i opisz dokładnie do czego zmierzasz, co, skąd, dokąd itp. to postaram się pomóc. Możesz wpisać ręcznie oczekiwane wyniki w odpowiednich komórkach żebym wiedział do czego zmierzamy. Kontakt aktualny do mnie na stronie www.pmsocho.com

@andrzejpiotrk7681 7 жыл бұрын

Też ostatnio to robiłem. W pewnym przypadku pasowała mi funkcja przesunięcie, ale indeks + podaj pozycję są absolutnie najbardziej skuteczne.

@krzysztoferydan1130 7 жыл бұрын

Dla większości użytkowników Excela istotne jest najczęściej to, że formuła działa, ale dla niektórych ważne jest również DLACZEGO? Myślę, że jest to równie ważne jak to, dlaczego formuła nie działa. Zaliczam się do drugiej kategorii użytkowników i byłbym zainteresowany zrozumieniem również mniej oczywistych kwestii niż ta, dlaczego zakres musi być przesunięty o liczbę pustych komórek położonych przed analizowaną tablicą. Odczuwam dyskomfort z tej przyczyny, że nie mogę pojąć roli jaką w przykładzie spełnia znak globalny [ * ]. Dla typu porównania: - 1, funkcja PODAJ.POZYCJĘ znajduje najmniejszą wartość, która jest większa lub równa szukanej wartości tj. *. Wartości w przeszukiwanej tablicy muszą być porządkowane w kolejności malejącej. Nasza tablica nie jest uporządkowana (a jest to warunek dla typu porównania - 1 {porządek malejący}), a ponadto są w niej „dziury”, dlaczego więc rezultatem jest ozycja ostatniej niepustej komórki? Czy możesz wyjaśnić ten problem lub chociaż wskazać, gdzie mógłbym poszukiwać odpowiedzi? Jak dotąd nie znalazłem rozwiązań w Internecie.

@pmsocho 7 жыл бұрын

Żeby wyszukiwanie z dopasowaniem do mniejszej wartości działało POPRAWNIE i zgodnie z oczekiwaniami lista powinna być posortowana. W przykładzie lista nie jest posortowana bo celem nie jest znalezienie wartości mniejszej lub równej wyszukiwanej tylko wartości ostatniej. Koniecznie zobacz ten materiał: kzbin.info/www/bejne/sIndcmBnfJiqiK8

@pmsocho 11 жыл бұрын

Nie widziałem nigdzie takiego rozwiązania. Nie wiem niestety czy jest to w ogóle wykonalne. Próbowałem kiedyś zrobić coś podobnego ale poległem :(

@orzesze 10 жыл бұрын

Może będzie ktoś w stanie pomóc odnośnie samej funkcji adr.pośr....? Przykładowo, mam pliki F,G,H składający się z 10 arkuszy. Chcę w pliku Y, w jednym arkuszu, umieścić dane z plików F,G,H z 1,2,3,...10 arkusza z komórki np. A5. Udało mi się to zrobić za pomocą funkcji adr.pośr, przy czym w pliku Y formuła ta działa tylko, gdy jest włączony plik F,G i H. Czy da się jakoś to obejść, by formuła w pliku Y działała bez włączania pliku F,G i H? W razie czego mogę wysłać przykład.

@pmsocho 10 жыл бұрын

Znany problem. Nie obejdziesz tego łatwo :(