Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Witam,

temat wygląda niby znajomo ale w żadnym z pokrewnych tematów nie mogłem znaleźć odpowiedzi na moje pytanie. Pewnie dlatego, że za każdym razem chodziło o wyszukanie według 2 konkretnych kryteriów. Ja zaś szukam 2 najbardziej przybliżonych w górę wartości.

Np dopasowujemy prostokąty: mamy bazę danych gdzie mamy podany ich rozmiar (wysokość i podstawa) i chcemy poklasyfikować je wg z góry ustalonych rozmiarów (nazwijmy je matrycami)
Baza danych
wys. podst.
111 x 200
150 x 180
70 x 175
63 x 148
198 x 120
75 x 75
itp
matryce:100x100; 150x150; 200x200; 100x150; 100x200

Wyszukujemy najmniejszą matrycę w którą będzie można wpisać każdy z tych prostokątów. np dla prostokąta 150 x 180 będzie to matryca o rozmiarach 200 x 200;
dla 111 x 200 będzie 150 x 200;
dla 63 x 148 będzie 100 x 150;
dla 75 x 75 będzie 100 x 100 itd.

UWAGA Ważne jest:
1. Aby prostokąt z bazy danych można było w całości wpisać w prostokąt z matrycy.
!!!Nie chodzi tutaj aby jak w geometrii wierzchołki prostokątów z bazy były styczne do krawędzi matryc ale by po prostu, prostokąty z bazy "nie wychodziły" nigdzie poza obręb matrycy.!!!
2. Wybrać najmniejszą z podanych matryc.

Siedziałem nad tym dobrych kilka godzin niestety bez efektu. Może ktoś z Was podejmie się tego wyzwania? Dla mnie bez VBA nie wyobrażam sobie aby można było to zrobić. Niestety sam nie znam VBA więc pozostaję z nierozwiązanym problemem.

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

W jednej pętli przeglądasz wszystkie elementy, w 2 wewnętrznej przeglądasz bazę matryc.
Zakładasz warunek sprawdzający czy wysokość i szerokość elementu są mniejsze niż wysokość i szerokość matrycy. Jeżeli tak to sprawdzasz czy powierzchnia nowej matrycy która spełnia kryteria nie jest czasem mniejsza od ostatnio zapisanej, która również spełniała.
Jeżeli tak to nowa zastępuje starą.
Po zakończeniu obu pętli masz zapisaną najmniejszą która spełnia warunek.

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Też bym tak to zrobiła.
Przy okazji zastanawiam się jednak, jak poradziłby sobie z tym Solver?

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Da się bez VBA. Funkcjami jeżeli, oraz sprawdzamy wysokość i szerokość. Jeżeli spełnia kryterium zwracamy pole. Za pomocą funkcji min i podaj.pozycje można zwrócić wynik.

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Witam,

Zakładając, że:
1.
A1:A6 =
111 x 200
150 x 180
70 x 175
63 x 148
198 x 120
75 x 75
2.
H1:H5 =
100x200
150x150
200x200
100x150
100x200
, to po utworzeniu kolumn dodatkowych - nie mylmy z niezbędnymi, rozdzielających wartości długości boków na osobne komórki, tak aby w B i I były boki dłuższe:

1. B1:B7 =

=JEŻELI(LEWY(PODSTAW(A1;" ";"");ZNAJDŹ("x";PODSTAW(A1;" ";""))-1)*1>PRAWY(PODSTAW(A1;" ";"");DŁ(PODSTAW(A1;" ";""))-ZNAJDŹ("x";PODSTAW(A1;" ";"")))*1;LEWY(PODSTAW(A1;" ";"");ZNAJDŹ("x";PODSTAW(A1;" ";""))-1)*1;PRAWY(PODSTAW(A1;" ";"");DŁ(PODSTAW(A1;" ";""))-ZNAJDŹ("x";PODSTAW(A1;" ";"")))*1)

200
180
75
63
198
75

2.
C1:C7 =

=JEŻELI(LEWY(PODSTAW(A1;" ";"");ZNAJDŹ("x";PODSTAW(A1;" ";""))-1)*1<PRAWY(PODSTAW(A1;" ";"");DŁ(PODSTAW(A1;" ";""))-ZNAJDŹ("x";PODSTAW(A1;" ";"")))*1;LEWY(PODSTAW(A1;" ";"");ZNAJDŹ("x";PODSTAW(A1;" ";""))-1)*1;PRAWY(PODSTAW(A1;" ";"");DŁ(PODSTAW(A1;" ";""))-ZNAJDŹ("x";PODSTAW(A1;" ";"")))*1)

111
150
70
63
120
75

3. I1:I5

=JEŻELI(LEWY(PODSTAW(H1;" ";"");ZNAJDŹ("x";PODSTAW(H1;" ";""))-1)*1>PRAWY(PODSTAW(H1;" ";"");DŁ(PODSTAW(H1;" ";""))-ZNAJDŹ("x";PODSTAW(H1;" ";"")))*1;LEWY(PODSTAW(H1;" ";"");ZNAJDŹ("x";PODSTAW(H1;" ";""))-1)*1;PRAWY(PODSTAW(H1;" ";"");DŁ(PODSTAW(H1;" ";""))-ZNAJDŹ("x";PODSTAW(H1;" ";"")))*1)

200
150
200
150
200

4. J1:J5

=JEŻELI(LEWY(PODSTAW(H1;" ";"");ZNAJDŹ("x";PODSTAW(H1;" ";""))-1)*1<PRAWY(PODSTAW(H1;" ";"");DŁ(PODSTAW(H1;" ";""))-ZNAJDŹ("x";PODSTAW(H1;" ";"")))*1;LEWY(PODSTAW(H1;" ";"");ZNAJDŹ("x";PODSTAW(H1;" ";""))-1)*1;PRAWY(PODSTAW(H1;" ";"");DŁ(PODSTAW(H1;" ";""))-ZNAJDŹ("x";PODSTAW(H1;" ";"")))*1)

100
150
200
100
100

, to w jeżeli w zakresie F1:F6 wykorzystam formułę zatwierdzaną tablicowo (wbijam formułę do F1, Ctrl+Shift+Enter, przeciągam w dół)

=INDEKS($H$1:$H$5;PODAJ.POZYCJĘ(MIN(JEŻELI((MAX(B1:C1)<=$I$1:$I$5)*((PIERWIASTEK(C1^2+B1^2)<=$K$1:$K$5))*($K$1:$K$5-PIERWIASTEK(C1^2+B1^2))<=0;999;(MAX(B1:C1)<=$I$1:$I$5)*((PIERWIASTEK(C1^2+B1^2)<=$K$1:$K$5))*($K$1:$K$5-PIERWIASTEK(C1^2+B1^2))));JEŻELI((MAX(B1:C1)<=$I$1:$I$5)*((PIERWIASTEK(C1^2+B1^2)<=$K$1:$K$5))*($K$1:$K$5-PIERWIASTEK(C1^2+B1^2))<=0;999;(MAX(B1:C1)<=$I$1:$I$5)*((PIERWIASTEK(C1^2+B1^2)<=$K$1:$K$5))*($K$1:$K$5-PIERWIASTEK(C1^2+B1^2)));0))

to dostanę rezultat:

200x200
200x200
100x200
100x150
200x200
100x150

będący wynikiem zadania.

Plik: Pudełka.xlsx .
Pozdrawiam!Tomasz Gryzio edytował(a) ten post dnia 12.01.13 o godzinie 00:33

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Dzięki wszystkim a szczególne dla Tomasza.
Tomasz szacunek!
Okazuje się, że niemożliwe jest całkiem możliwe :D.

Dziękuję i pozdrawiam

Mikołaj

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

@Tomasz

Dodając nową matrycę o rozmiarze 150x200 powinno zmienić wybór dwóch pierwszych pozycji, a nie zmienia.Kamil Miąskiewicz edytował(a) ten post dnia 04.02.13 o godzinie 18:09

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Udało mi się uzyskać rozwiązanie, trochę krótsze i na pierwszy rzut oka bez błędów.

Kolumny A oraz H dane wejściowe jak u Tomasza

Kolumny B oraz I krócej:

=JEŻELI(USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A1;ZNAJDŹ("x";A1)-1))*1>USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(A1;DŁ(A1)-ZNAJDŹ("x";A1)))*1;USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A1;ZNAJDŹ("x";A1)-1))*1;USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(A1;DŁ(A1)-ZNAJDŹ("x";A1)))*1)*1

Kolumny C oraz J również krócej:

=JEŻELI(USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(A1;DŁ(A1)-ZNAJDŹ("x";A1)))*1<USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A1;ZNAJDŹ("x";A1)-1))*1;USUŃ.ZBĘDNE.ODSTĘPY(PRAWY(A1;DŁ(A1)-ZNAJDŹ("x";A1)))*1;USUŃ.ZBĘDNE.ODSTĘPY(LEWY(A1;ZNAJDŹ("x";A1)-1))*1)*1

Kolumna K to powierzchnia matrycy (IxJ)

Kolumna D to funkcja tablicowa podająca najmniejszą powierzchnię dla spełnionych obu wymiarów, jednak z priorytetem na wymiar pierwszy (kolumna B)

=MIN(JEŻELI(B1<=I:I;JEŻELI(C1<=J:J;K:K;"");""))

Kolumna E to funkcja tablicowa podająca najmniejszą powierzchnię dla spełnionych obu wymiarów, jednak z priorytetem na wymiar drugi (kolumna C)

=MIN(JEŻELI(C1<=J:J;JEŻELI(B1<=I:I;K:K;"")))

Kolumna F to już INDEKS i PODAJ POZYCJĘ z wyborem najbardziej optymalnej matrycy (czyli najmniejszej..;)

=INDEKS(H:H;PODAJ.POZYCJĘ(MIN(D1:E1);K:K;0);1)

Z powodzeniem można zapisać ostatnie trzy funkcje w jedną, rozbiłem dla przejrzystości..:)Kamil Miąskiewicz edytował(a) ten post dnia 04.02.13 o godzinie 18:48

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Kamil Miąskiewicz:
@Tomasz

Dodając nową matrycę o rozmiarze 150x200 powinno zmienić wybór dwóch pierwszych pozycji, a nie zmienia.

@Kamil
Gdy zostaną zmienione adresy w formule na zawierające dodaną matrycę, to: owszem, zmienia.

Nie widzę w warunkach zadania co rozwiązanie powinno, a czego nie powinno oprócz dopasowywania matryc do prostokątów, a więc bez nadgorliwości wybieram najmniej zasobożerną znaną mi drogę - osobiście na dłuższą niż testowa metę nie widzę możliwości wykorzystania większości formuł tablicowych na całych kolumnach:
dla każdego wprowadzenia/usunięcia wartości do komórek wykorzystywanych w owych formułach,
dla każdej formuły powiązanej,
dla każdego porównania,
dla każdej operacji matematycznej
...
... -
- następuje przeliczenie x * 2^20 przypadków - następują niedopuszczalne opóźnienia wynikające z przeliczenia formuł, a arkusz, w którym znajduje się takie rozwiązanie staje się arkuszem służącym tylko i wyłącznie do tego jednego rozwiązania.

Przeliczenie formuły z jednej komórki zaproponowanego rozwiązania z kolumny E trwa 1,32501 sek.
(po zagnieżdżeniu komórek z D,E do jednej formuły w F formuła przelicza się w 3,18596 sek.),
przeliczenie arkusza dla czterech matryc i czterech prostokątów (w sumie 28 formuł, z czego 8 tablicowych) trwa 12,96669 sek.,
czy możemy się zgodzić na skrócenie formuł o 527 znaków = 34% tak wysokim kosztem wydajności = 151 612% (dłuższy czas przeliczenia (moja formuła przelicza się w 0,0021 sek.))?

Zakładając, że w warunkach zadania określone zostałoby, że zakresy mają być dynamiczne zaproponowałbym rozwiązanie oparte o zakresy budowane dynamicznie.

Kamil Miąskiewicz:
Udało mi się uzyskać rozwiązanie, trochę krótsze i na pierwszy rzut oka bez błędów.

Proszę wprowadzić do swojego rozwiązania następującą listę matryc:
180x200
240x150
100x360
300x120

oraz następującą listę prostokątów:
179 x 199
149 x 239
359 x 99
299 x 119

oczekiwany wynik:
180x200
240x150
100x360
300x120

otrzymany wynik:
180x200
180x200
180x200
180x200

Pozdrawiam!

Temat: Klasyfikacja wg 2 kryteriów w ZAOKRĄGLENIU W GÓRĘ.

Tomasz Gryzio:

Kamil Miąskiewicz:
@Tomasz

Dodając nową matrycę o rozmiarze 150x200 powinno zmienić wybór dwóch pierwszych pozycji, a nie zmienia.

@Kamil
Gdy zostaną zmienione adresy w formule na zawierające dodaną matrycę, to: owszem, zmienia.

Nie widzę w warunkach zadania co rozwiązanie powinno, a czego nie powinno oprócz dopasowywania matryc do prostokątów, a więc bez nadgorliwości wybieram najmniej zasobożerną znaną mi drogę - osobiście na dłuższą niż testowa metę nie widzę możliwości wykorzystania większości formuł tablicowych na całych kolumnach:
dla każdego wprowadzenia/usunięcia wartości do komórek wykorzystywanych w owych formułach,
dla każdej formuły powiązanej,
dla każdego porównania,
dla każdej operacji matematycznej
...
... -
- następuje przeliczenie x * 2^20 przypadków - następują niedopuszczalne opóźnienia wynikające z przeliczenia formuł, a arkusz, w którym znajduje się takie rozwiązanie staje się arkuszem służącym tylko i wyłącznie do tego jednego rozwiązania.

Przeliczenie formuły z jednej komórki zaproponowanego rozwiązania z kolumny E trwa 1,32501 sek.
(po zagnieżdżeniu komórek z D,E do jednej formuły w F formuła przelicza się w 3,18596 sek.),
przeliczenie arkusza dla czterech matryc i czterech prostokątów (w sumie 28 formuł, z czego 8 tablicowych) trwa 12,96669 sek.,
czy możemy się zgodzić na skrócenie formuł o 527 znaków = 34% tak wysokim kosztem wydajności = 151 612% (dłuższy czas przeliczenia (moja formuła przelicza się w 0,0021 sek.))?

Zakładając, że w warunkach zadania określone zostałoby, że zakresy mają być dynamiczne zaproponowałbym rozwiązanie oparte o zakresy budowane dynamicznie.

Kamil Miąskiewicz:
Udało mi się uzyskać rozwiązanie, trochę krótsze i na pierwszy rzut oka bez błędów.

Proszę wprowadzić do swojego rozwiązania następującą listę matryc:
180x200
240x150
100x360
300x120

oraz następującą listę prostokątów:
179 x 199
149 x 239
359 x 99
299 x 119

oczekiwany wynik:
180x200
240x150
100x360
300x120

otrzymany wynik:
180x200
180x200
180x200
180x200

Pozdrawiam!

Boskie..!:) Postaram się odpisać.. za jakiś czas..;P

Zamieniam kolumnę D

=MIN(JEŻELI(B1<=I:I;JEŻELI(C1<=J:J;J:J;"")))

kolumnę F na:

=INDEKS(H:H;PODAJ.POZYCJĘ(D1&E1;J:J&K:K;0);1)

Kamil Miąskiewicz edytował(a) ten post dnia 04.02.13 o godzinie 23:33