[solved] Mnożenie liczb po spełnieniu warunku < od, > od

[pawel1280]

Dzień dobry,
założyłem konto i piszę tutaj po przeszukaniu tego forum i sformułowanych na różne sposoby pytaniach w Google.

Nie jestem zaawansowanym użytkownikiem arkuszy kalkulacyjnych, rozumiem zasady ich działania i w razie potrzeb doraźnie się uczę.
Mam w tej chwili problem, z którym nie jestem w stanie sobie poradzić.

W jednej kolumnie (załóżmy że A) mam liczby, które są jednym z czynników mnożenia. Należy je pomnożyć przez jedną z liczb, które są zapisane w czterech komórkach ( AA2, AB2, AC2, AD2), a wynik zapisać w kolumnie B.
Działanie powinno wyglądać mniej więcej tak:
Jeżeli liczba jest większa od 0 i mniejsza od 0,99 to drugim czynnikiem jest liczba z komórki AA2; jeżeli liczba jest większa od 1 i mniejsza od 1,99 to drugim czynnikiem jest liczba z komórki AB2 i tak dalej...
Będę wdzięczny za pomoc,
Pawel1280

[Jan_J]

[...]Jeżeli liczba jest większa od 0 i mniejsza od 0,99 to drugim czynnikiem [...]
jeżeli liczba jest większa od 1 i mniejsza od 1,99 to drugim czynnikiem [...]

Co z liczbami takimi, jak 0,993 albo 1,995?

Czy potrafimy wyliczyć wartość numeru grupy drugiego czynnika na podstawie wartości pierwszego czynnika?
Powinno to być coś w rodzaju zaokr(pierwszy_czynnik). Wtedy -- mając numer porządkowy drugiego czynnika, nie wartość dolnego progu strefy, w której on obowiązuje -- pobierzemy go za pomocą funkcji indeks(blok_drugich_czynników; numer_grupy).

Czyli w najprostszym przypadku,
=a1 * indeks(aa$2:ad$2; 1; zaokr(a1))
są tu dwa numery porządkowe w funkcji indeks, bo pierwszy dotyczy wiersza, a drugi kolumny. Gdyby dane zorganizować w pojedynczej kolumnie zamiast w wierszu, byłoby prościej.

W ostatecznym zastosowaniu być może trzeba będzie:
* uwzględnić fakt, że niektóre dane będą prowadzić do numerów innych niż 1,2,3,4;
* uwzględnić fakt, że dane większe od 0,99, ale nie większe niż 1,00, nie powinny być mnożone. Nie wierzę, żeby tak było, ale trzymam się literalnie opisu wymagań;
* dopuścić możliwość, że granice grup nie zmieniają się co 1. Jeżeli są one nadal w regularnych odstępach, to da się lekko zmodyfikować użycie zaokr: np. dla kroku 0,1 będzie to 10*zaokr(wartość; 2) albo zaokr(10:wartość)/10, a dla kroku 0,5 -- zaokr(2*wartość)/2;
* dopuścić możliwość, że poszczególne strefy drugich czynników różnią się szerokością. Wtedy arytmetyka zaokrągleń nie wystarczy, trzeba będzie zastosować wyszukiwanie. Wartości progów winny być zapisane w wektorze, tak by dało się użyć albo funkcji podaj.pozycję, albo zamiast pary podaj.pozycję i indeks użyć wyszukaj.pionowo. Ale będzie to wymagało przeorganizowania zapisu drugich czynników.

[pawel1280]

Dzięki za odpowiedź.
Arkusz, o którym piszę to arkusz z towarami do sklepu. A operacja o jaką mi chodzi, to wyliczenie ceny na podstawie wagi.
Waga jest podawana z dokładnością do maksymalnie dwóch miejsc po przecinku.
Widełki są cztery - >0,00 i <0,99 (mnożenie *400); >1 i <1,99 (mnożenie *300), >2 i <4,99 (mnożenie *200), >5 (mnożenie *100).

Zależy mi na możliwie prostym (jeśli chodzi o zasadę działania) sposobie na osiągnięciu celu.
Biorę pod uwagę to, że w związku z tym prosty sposób będzie wymagał mnóstwa tekstu.

Rozwiązanie tego problemu będzie mi służyło w kilku miejscach, dlatego zależy mi na zrozumieniu zasady jego działania, żeby później to łatwo dostosowywać.

[Jan_J]

Widełki są cztery - >0,00 i <0,99 (mnożenie *400); >1 i <1,99 (mnożenie *300), >2 i <4,99 (mnożenie *200), >5 (mnożenie *100).

Czyli kroki nie są równej długości: 0 + 1 == 1; 1 + 1 == 2; 2 + 3 == 5. Wobec tego obliczanie numeru grupy przez dzielenie odpada. Pozostaje albo zagnieżdżanie warunków w formułach (odradzam ze względu na komplikację), albo wyszukiwanie (w załączeniu).

kwoty.ods

(7.14 KiB) Pobrany 174 razy

Przedstawioną metodę da się łatwo uogólnić na dowolną liczbę grup (mniejszą albo większą).

[Jermor]

Wydaje mi się, że pomysł z funkcją indeks jest najlepszy. Trzeba tylko zrobić trzy grupy z mnożnikiem 200.
Utworzyć wiersz w odwróconej kolejności, czyli 100; 200; 200; 200; 300; 400
i wpisać formułę:

Kod: Zaznacz cały

=JEŻELI.BŁĄD(INDEKS($obszar indeksu;1;6-LICZBA.CAŁK(waga));100)

Wyjaśnienie dla Pawel1280.
Funkcja JEŻELI.BŁĄD zwróci wartość 100 gdy INDEKS zwróci błąd. A zrobi to on wtedy gdy waga towaru będzie większa od 6, bo wyliczona zostanie kolumna zerowa (albo nawet ujemna gdy waga osiągnie 7 i więcej). W pozostałych przypadkach zadziała tak jak to opisał Jan_J w pierwszym poście.

Teraz kiedy się ponownie przyjrzałem widzę, że można pominąć pierwszą w obszarze indeksu wartość 100, gdyż JEŻELI.BŁĄD i tak zwraca tę wartość. Zatem obszar indeksu to 200; 200; 200; 300; 400 a w formule zamienić liczbę 6 na 5

[pawel1280]

Bardzo dziękuję za odpowiedź, to jest to, co rozwiązuje mój problem.

Teraz mam prośbę - jak to działa? Zależy mi - oprócz odpowiedzi na moje pytanie - na zrozumieniu rozwiązania. Z Open Office/Libre Office mam ten problem, że nie mogę nigdzie znaleźć podręczników, które pomogłyby w bardziej zaawansowanych rzeczach - tak jak widzę tutaj macierz i indeks (dobrze rozumiem?). Książek do pakietu office jest mnóstwo, a do Base i Calc sensownych, grubych almanachów (coś jak Biblia Heliona) - nie ma. Albo serii "For dummies".
Myślałem o użyciu Base do moich celów, ale poległem ze względu na brak odpowiednich publikacji, dlatego półśrodek w postaci Calc.
Patrzyłem na publikacje linkowane na tym forum i też wg mnie średnio nadają się do tego tematu - chyba że jestem w błędzie, to tym bardziej proszę o pomoc.

[EDIT]
chodzi mi o tę odpowiedź @Jan_J

przez Jan_J » N sty 22, 2017 12:34 am

pawel1280 napisał(a):
Widełki są cztery - >0,00 i <0,99 (mnożenie *400); >1 i <1,99 (mnożenie *300), >2 i <4,99 (mnożenie *200), >5 (mnożenie *100).
Czyli kroki nie są równej długości: 0 + 1 == 1; 1 + 1 == 2; 2 + 3 == 5. Wobec tego obliczanie numeru grupy przez dzielenie odpada. Pozostaje albo zagnieżdżanie warunków w formułach (odradzam ze względu na komplikację), albo wyszukiwanie (w załączeniu).
kwoty.ods
(7.14 KiB) Pobrane 3 razy

Przedstawioną metodę da się łatwo uogólnić na dowolną liczbę grup (mniejszą albo większą).

[Jan_J]

Jak to działa?
odpowiedź na ogólinym poziomie mówi o organizacji obliczeń i nie ogranicza się do zastosowanego narzędzia, czyli w tym przypadku do arkusza kalkulacyjnego.

Mamy wartości liczbowe należące do pewnych grup. Naszym zadaniem jest pomnożenie każdej liczby przez pewną wartość charakterystyczną dla grupy.
Żeby to zrobić dobrze, trzeba
* albo napisać, na ogół dość rozbudowany, układ warunków sprawdzających z którą grupą mamy do czynienia. To jest to co odradzam, w stylu =x*jeżeli(x<p1;w1;jeżeli(x<p2;w2;jeżeli(x<p3;w3;w4))). Układ taki jest trudny do analizy, podatny na błędy, niepodatny na rozbudowę.
* albo umieć wyznaczyć numer grupy, a później skorzystać z funkcji indeks pobierającej wartość z wektora. Zależnie od sposobu określenia grup (zawsze są to przedziały na osi liczbowej, choć mogą mieć różne szerokości), można skorzystać z arytmetyki -- tak naprawdę z obliczania krotności -- albo z wyszukiwania. To są metody, które pojawiły się w naszych postach. Jermor zauważył, że skoro szerokości grup mają wspólny podzielnik (a mają), to można łatwo, przez sprowadzenie ich do grup o stałych szerokościach, zrezygnować z wyszukiwania na korzyść obliczania numeru porządkowego. I tyle.

Co do literatury. Rozwiązywanie zagadnień tego typu możemy rozważać rozmaicie
0. na poziomie ogólnym, bez wskazania narzędzia. Coś jakbyśmy mówili ogólnie o podróżach.
1. na poziomie wybranej klasy narzędzi, w tym przypadku arkuszy kalkulacyjnych. Będziemy mówić dajmy na to o podróżach motocyklowych.
2. na poziomie produktu. Klub użytkowników motocykli BMW wymienia między sobą praktyczną wiedzę o podróżowaniu.

Poziom 0 wymaga przygotowania mentalnego, niezbyt trudnego, ale człowieka, który gorączkowo poszukuje rozwiązania w niedoczasie, nie zawsze na nie stać. Bywa cennym uzupełnieniem.
Poziomy 1 i 2 są nagminnie mylone. Większość uwag dot. Excela, Calca, Gnumerica, Corela Quattro jest -- mówię o używaniu formuł -- do prawie natychmiastowego przeniesienia z jednej platformy na inne. Dotyczy to także funkcji wyszukiwania, jak podaj.pozycję i wyszukaj*, i adresowych, jak indeks. Mimo że nie ma tu formalnego standardu zbioru funkcji, a wiodący producenci wciąż dodają nowe funkcje, częściowo dublujące dostępne rozwiązania, to sposób działania formuł jest w nich równoważny.

[pawel1280]

@Jan_J

Bardzo dziękuję za pomoc i odpowiedź.
Najpierw z innej beczki - nie spodziewałem się tego, nauczony przykrymi doświadczeniami z Elektrody i polskich for, które odsyłają do szukania w Google.
Dziękuję za czas jaki poświęciłeś na napisanie tego, co napisałeś i doceniam rzeczowość i dokładność. Na takiej odpowiedzi mi zależało - rzeczowa, konkretna, a jeszcze lepiej, że w punktach.
Poza tym dostałem narzędzie (czyli wiedzę), którą wykorzystam później.

Twoja propozycja to właśnie to, czego szukałem, nie myślałem, że macierze są tak prostym rozwiązaniem. Dziękuję, że o tym wspomniałeś. Nie mogłem napisać odpowiedzi do teraz. Przepraszam za to, bo wiem, że mogło to być odebrane jakbym dostał odpowiedź - i zniknął. A tak nie było.

Offtop nr1
Co do wiedzy - zachęcony tym,co napisałeś poszukam czegoś związanego z Excelem, co pomoże mi rozwiązać parę późniejszych problemów i przy okazji nauczyć się czegoś nowego. Jeśli mógłbyś polecić książki jakie są wg Ciebie najlepsze (sam Excel). Domyślam się, że lepiej żeby nie były związane z najnowszym pakietem Office. Pytam o to jaka książka (a może tutoriale/kurs z jakiejś strony?), bo to nie moja działka zupełnie i dałbym się pewnie naciąć na opis na okładce. Wiem to po mojej branży - DTP. Są książki przez laików uważane za encyklopedyczną wiedzę, ale praktycy widzą, że uczą złych nawyków i są dość... pretensjonalne.

Zupełny offtop nr2 - mimo mojej miłości do open source (i poparcia dla idei przede wszystkim), tego że staram się tam gdzie mogę wykorzystywać
zamienniki płatnych (albo zamkniętoźródłowych) rozwiązań w momencie, kiedy korzystam z nich do pracy - to zaczynają się problemy dyskwalifikujące je w poważnych zastosowaniach.
Wierzyłem w używanie Linuksa + wine do pracy z Photoshopem i programami Adobe. Ale po wielu próbach poddałem się. I tak z wieloma programami. Póki co zostaję przy Libre Office,
ale to sprawia mi wiele problemów. Choćby Base - który chciałbym wdrożyć w firmie do obsługi stanu magazynowego (bo w Calc nie da się przechowywać zdjęć w komórkach, co nie jest dla mnie problemem samym w sobie bo chodzi o to,że ten program nie ma służyć do tego, ale do Calc znajdzie się więcej poradników) - nie ma żadnej sensownej książki o nim.
Będę szukał tutaj pomocy, ale chodzi mi o takie ogólne gorzkie żale. Wierzę, że znajdę tu ktoś mi pomoże, ale Pani Grażynka z księgowości nie będzie miała tyle czasu i samo-zapału - i wymusi w pracy używanie MSO. I dlatego póki nie zmieni się podejście twórców (czyli de facto społeczności), to open source pozostanie niszą. Wiem, że da się to zmienić - choćby przykład Blendera, który coraz bardziej się uczłowiecza i staje się przyzwoitym, konkurencyjnym oprogramowaniem do modelowania 3d, ale Gimp? Inkscape? Stoją z tyłu i powoli tracą jakiekolwiek szanse na swoją niszę - choćby przez Affinity Designer i Photo. Boję się, że OO + LO i inne klony, bez łatwo dostępnej przystępnej wiedzy i poradników też mogą skończyć, zaspać niespodziewaną rewolucję, zniknąć. Bo komuś coś się nie spodoba, np nastąpi kolejny rozłam społeczności, Libre Office się podzieli i pojawi się wewnętrzna konkurencja - z dwoma wersjami równocześnie. Oczywiście obydwoma równie ważnymi i tymi lepszymi.
Koniec offtopu.