Excel/Calc: Dynamiczne generowanie kryteriów wyszukiwania funkcji BD.MAKS

Oprogramowanie komputerowe Problemy z oprogramowaniem
#1

Potrzebuję na podstawie określonych kryteriów wyszukać maksymalną wartość z jednego arkusza, aby podstawić ją do obliczeń w drugim arkuszu.
To obliczenie ma być powtarzane dla każdego wiesza drugiego arkusza, jednak kryteria wyszukiwania będą się zmieniać ze względu na wartości w aktualnym arkuszu.

Obie tabele schematycznie wyglądają w ten sposób

nazwa wartosc wynik
nazwa1 100 50
nazwa1 300 150
nazwa2 150 80
nazwa2 200 120

W drugiej tabeli jeśli wartość przy jakiejś pozycji jest pusta, to potrzebuję wyliczyć wynik na podstawie maksymalnej wartości z pierwszej tabeli. Kryterium tutaj będzie nazwa.

Próbowałem to ogarnąć funkcją bd.max, niestety nie można (nie potrafię) zaszyć dla niej kryterii w samej formule, tylko trzeba na sztywno podać zakres w arkuszu, gdzie takie kryteria się znajdują.

Nie wiem czy tego zbytnio nie zagmatwałem chcąc jak najprościej wytłumaczyć… :thinking:

ps.
Od razu oznaczę tutaj magika @Dimatheus, żeby przypadkiem nie przegapił, bo może coś wymyśli :wink:

2 polubienia
#2

Hej,

Czyli w przypadku, gdy w drugim arkuszu pojawia się nazwa2 i brak wartości, formuła ma zwrócić wartość 200, a jeśli nazwa 1 to 300? Dobrze rozumuję?

Pozdrawiam,
Dimatheus

#3

Tak :slight_smile:

edit.
Rozwiązanie problemu jak zwykle okazało się błahe, wystarczyło zjeść lunch, wypić kawę i oświeciło:
MAKS.WARUNKÓW
:wink:

edit2.
Pojawił się za to kolejny problem, ale nie będę zakładał kolejnego tematu, bo pewnie rozwiązanie znowu okaże się równie banalne :wink:
Potrzebuję z tej tabeli wyciągnąć listę unikatowych nazw, ale potrzebuję zrobić to jakąś funkcją, tak aby ta lista zmieniała się dynamicznie jeśli coś dodam lub usunę. Jakieś pomysły?

#4

Hej,

A nie prościej zastosować tabelę przestawną z nazwami w filtrze wierszy? Będzie wyglądać to mniej więcej tak - screeny z wersji MS Office 2007, bo akurat tę wersję mam na starszym komputerze, przy którym teraz działam.

obraz

Co ciekawe, ta funkcja dostępna jest tylko w Office 365. W wersji stacjonarnej na próżno jej szukać - a posiadam w sumie najnowszą wersję MS Office 2016 dla użytkowników domowych i uczniów. Ale oczywiście da się to ominąć stosując dwa różne sposoby.

Pierwszy korzysta ze znanej funkcji WYSZUKAJ.PIONOWO. Korzystamy z własności, że funkcja przeszukuje zakres od początku do końca i gdy natrafi na dopasowaną wartość, zwraca określoną wartość od razu bez dalszych przeszukiwań. Wystarczy więc listę posortować według nazw i według wartości malejąco i gotowe. Można wyszukiwać.

obraz

Można też posłużyć się formułami tablicowymi (https://support.office.com/pl-pl/article/tworzenie-formuły-tablicowej-e43e12e0-afc6-4a12-bc7f-48361075954d). Budujemy w nich warunki głównie przy pomocy formuły jeżeli, a otrzymane dane prezentujemy jako sumy, wartości minimalne, maksymalne i tak dalej. Funkcja będzie wyglądać dla komórki F2 następująco (tak, klamry są ważne, bo oznaczają formułę tablicową):

{=MAX(JEŻELI($A$2:$A$7=E2;$B$2:$B$7;""))}

A na screenie wyglądać będzie następująco. Nie dziwne, że wyniki w obu metodach mamy takie same. :joy: Jeszcze dorzucić warunek, że jeżeli pole jest puste, wtedy ma wyszukać i gotowe.

obraz

Pozdrawiam,
Dimatheus

#5

No ciekawe, bo nawet biedny LibreOffice na którym to próbuję zrobić, posiada tą funkcję.

W każdym razie pobawię się z tymi tabelami przestawnymi, sprawdzę jak to działa i czy dam radę zastosować, bo szczerze to nie miałem jeszcze z nimi kontaktu, więc mi nawet nie przyszły do głowy. Dzięki za poradę :slight_smile:

Pisząc to przypomniałem sobie, że już coś podobnego robiłem jakieś pół roku temu. Muszę tylko odszukać ten arkusz i podpatrzyć co wtedy wymyśliłem bez tabeli przestawnej :laughing:

#6

Hej,

No właśnie dziwne. Na początku nawet myślałem, że ta funkcja to jakiś rodzaj wkrętu. :slight_smile:

Ewentualnie zostaje VBA i skorzystanie z funkcji usuwania duplikatów. Coś na wzór tego.

Sub Makro2()
'
' Makro2 Makro
'

'
    Range("A2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    Range("H1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("H1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    ActiveSheet.Range("$H$1:$H$5").RemoveDuplicates Columns:=1, Header:=xlNo
End Sub

Pozdrawiam,
Dimatheus

2 polubienia