GoldenLine
Zaloguj się
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Witam!
Utknąłem nad tworzeniem raportu...
Krótko opisze problem:
W pliku użytkownika, znajdują się wiersze z danymi - odpowiednio kolumny z datami i czasami. wpisy jeden pod drugim, z wieloma wystąpieniami tych samych dat, czasy są powiedzmy przypadkowe.

W pliku kierownika chcę stworzyć formułę, która zsumuje czas w przypadaku, gdy w kolumnie dat wartości będą mieścić się w zakresie pomiędzy>= B1 oraz <= B2, w pliku kierownika, które dla przykładu mają wartości 2016-10-24 oraz 2016-10-25. Następna formuła będzie musiała przefiltrować również kolejną kolumnę pliku użytkownika, np "rodzaj"
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

funkcja SUMA.WARUNKÓW (SUMIFS)
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Bartłomiej D.:
funkcja SUMA.WARUNKÓW (SUMIFS)

Niestety, ta funkcja oraz kilka innych ma problem z wyciągnięciem danych z innego pliku (który nie jest otwarty). Próbowałem coś takiego:

=SUMA.WARUNKÓW([CALL9.xlsb]TABELA!POŁ;[CALL9.xlsb]TABELA!DZIEŃ;">="&B1;[CALL9.xlsb]TABELA!DZIEŃ;"<="&B2)

Może robie cośtu nie tak... Tu ponazywałęm kolumny: POŁ, DZIEŃ. Podobna formuła wewnątrz tego samego pliku oczywiście działaTen post został edytowany przez Autora dnia 25.10.16 o godzinie 10:33
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

podaj pełną ścieżkę do pliku z apostrofami ->
=SUMA.WARUNKÓW('C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_sumowanej;'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_kryterium_1;"coś_tam";'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_data;">=2016-10-24";'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_data;"<=2016-10-25")
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Bartłomiej D.:
podaj pełną ścieżkę do pliku z apostrofami ->
=SUMA.WARUNKÓW('C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_sumowanej;'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_kryterium_1;"coś_tam";'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_data;">=2016-10-24";'C:\Folder\Podfolder_1\Podfolder2\nazwa_pliku.xlsb'!nazwa_kolumny_data;"<=2016-10-25")

Ścieżkę wyciąłem na potrzeby tego wątku, gdyż prowadzą do pliku na OneDrivie, były poprawne

Dla pewności podmieniłem dane do Twojej formuły - niestety funkcja zwraca #ARGTen post został edytowany przez Autora dnia 25.10.16 o godzinie 11:10
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Jak się bawisz chmurą, to zasługujesz na wszystko co Cię spotyka :)

Nie korzystałem z OneDrive, więc nie wiem gdzie może być błąd.
Możesz zastąpić odwołanie do pliku funkcją ADR_POŚR - może pomoże...
Albo to...
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

No cóż, tego nie przeskoczę - musi być OneDrive. Przeczytałem artykuł i podmieniłem ścieżki absolutne do folderu lokalnego - niestety wciąż #ARG
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

A jak użyjesz adresów zakresów zamiast ich nazw (czyli "POŁ", "DZIEŃ" itp.)?
Zgodnie z tym nie mozna zdefiniować nazwanych zakresów w OneDrive. Nie mam jak sprawdzić choć wydaje mi się to dziwne...Ten post został edytowany przez Autora dnia 25.10.16 o godzinie 12:10
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Próbowałem oczywiście A2:A100 itp. Nic to nie daje. W excel online, owszem, nie można definiować nowych nazw, ale te stworzone w 'normalnym' excelu działają prawidłowo - korzystałem już wcześniej z takiego rozwiązania.
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Spróbuję wyjaśnić jeszcze raz:

Potrzebuję zsumować kolumny po spełnieniu trzech warunków w dwóch innych. Odwołanie ma działać do pliku zewnętrznego. Formuła SUMA.WARUNKÓW doskonale sprawdza się wewnątrz pliku lub w odwołaniu do innego OTWARTEGO skoroszytu. Niestety tutaj formuła ma pobierać dane z nieotwartego skoroszytu.

Formuła, którą stworzyłem wcześniej nie sprawdza się i proszę o pomoc w znalezieniu alternatywnego rozwiązania.

SUMA.WARUNKÓW('ŚCIEŻKA[PLIK.xlsb]Arkusz1'!POŁ;'ŚCIEŻKA[PLIK.xlsb]Arkusz1'!DZIEŃ;">="&B1;'ŚCIEŻKA[PLIK.xlsb]Arkusz1'!DZIEŃ;"<="&B2;'ŚCIEŻKA[PLIK.xlsb]Arkusz1'!RODZAJ;="INNE")


POŁ, DZIEŃ oraz RODZAJ, to zdefiniowane zakresy kolumn w PLIK'u, , a komórki B1 oraz B2 są w pliku docelowym, gdzie są... daty do porównania.
Z góry dziękuję za wszelką pomoc :)Ten post został edytowany przez Autora dnia 25.10.16 o godzinie 19:46
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Problemem nie jest SUMA.WARUNKÓW - działa ona doskonale w przypadku zamkniętego skoroszytu, który jest dla niej źródłem danych.
Problemem jest OneDrive z którego korzystasz - jak wrzuciłem wreszcie jakieś swoje pliki w to ustrojstwo, to otrzymałem komunikat:
"Łącza zostały wyłączone. Łącza do skoroszytów zewnętrznych nie są obsługiwane i zostały wyłączone.", co jak dla mnie oznacza, że jeśli chcesz się bawić w łączenie plików, to musisz to zrobić poza OneDrive. I tyle...

Znalazłem jakieś pogawędki na ten temat ale nie wczytywałem się zbytnio, więc nie wiem czy to coś pomoże w Twoim przypadku:
1. http://answers.microsoft.com/en-us/msoffice/forum/msof... (wypowiedź litlxchopstikz z 2016-05-06)
2. http://stackoverflow.com/questions/27263188/linking-tw...

BTW - da się podejrzeć Twoje pliki? Bo na razie nie umiem nawet wstawić w jednym pliku odwołania do drugiego otwartego pliku typu "=[plik.xlsx]Arkusz1!$A$1"Ten post został edytowany przez Autora dnia 25.10.16 o godzinie 20:24
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Dobra - odwołuję twierdzenie że SUMA.WARUNKÓW działa na zamkniętym skoroszycie.
Działa ale do czasu ponownego otworzenia pliku :) Hm...

Natomiast wygląda na to, że SUMA.ILOCZYNÓW (SUMPRODUCT) robi robotę - w każdym razie gdy:
1. oba pliki są na tym samym kompie i plik źródłowy (do którego odnosi się funkcja) jest zamknięty
2. gdy plik źródłowy jest na innym kompie (dysku sieciowym) i jest zamknięty

=SUMPRODUCT((źródło.xlsx!typ="jabłka")*1;(źródło.xlsx!data>=G1)*1;źródło.xlsx!ile)

gdzie:
źródło.xlsx - plik źródłowy z 3 nazwanymi zakresami: typ, data, ile
G1 - komórka z datą do filtrowania wyników

Ale jak to na OneDrive przerzucić, to nadal nie wiem :)Ten post został edytowany przez Autora dnia 25.10.16 o godzinie 21:05
Link do wypowiedziLink
Paweł Nowak

Paweł Nowak

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Dziękuję za zainteresowanie!
Szukałem w międzyczasie dalej i wreszcie pomogło:
=suma.iloczynów((war1)*(war2)*zakres_do_sumowania)


Teraz szukam jeszcze sposobu, aby zakresy kolumn zmieniały się dynamicznie, gdyż w pliku tym co chwila będą pojawiać się nowe wiersze, a nie chcę wpisywać tak 65k - myśle, że mało to optymalne

Pozdrawiam
Link do wypowiedziLink
Bartłomiej Dąbrowski

Bartłomiej Dąbrowski
analiza/przetwarzani
e danych

Temat: SUMA Z INNEGO PLIKU, TRZY WARUNKI

Mam wrażenie, że Excel dłużej będzie przeliczał dynamiczne zakresy (niezależnie od metody ich określenia) niż obliczał sumę iloczynów na pełnych kolumnach. Według mnie szkoda zachodu.

Co do metod to przetestowałem dwie - nie działają :)
1.
Funkcja PRZESUNIĘCIE (OFFSET), tradycyjnie używana do pracy z zakresami, nie zadziała w odniesieniu do zamkniętego skoroszytu. Nie zadziała także gdy przy jej pomocy zdefiniujemy dynamiczny zakres bezpośrednio w pliku źródłowym i będziemy się do niego próbowali odwoływać w pliku wynikowym.

2.
Konwersja danych w pliku źródłowym z zakresu na tabelę i utworzenie na bazie tej tabeli nazwanych zakresów. Po dodaniu nowych wierszy zakresy automatycznie się powiększają.
Niestety odwoływanie się do tych zakresów przy zamkniętym skoroszycie nie działa.
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy





Wyślij zaproszenie do
Anuluj