Excel Forum - Porady, Pomoc,  Excel Help, Excel FAQ Strona Główna
 FAQ  RegulaminRegulamin  Szukaj   Użytkownicy   Grupy   Rejestracja   Profil   Twoje wiadomości   Zaloguj 


Poprzedni temat «» Następny temat
ID tematu: 63932 Skopiuj do schowka Wstawienie formuły zależnie od aktualnej daty
Autor Wiadomość
ted11 
Exceloholic


Posty: 153
Wysłany: 25-01-2019, 12:22   Wstawienie formuły zależnie od aktualnej daty

Witam
Proszę o pomoc w rozwiązaniu takiego oto problemu:
W katalogu 2019 mam plik Zestawienie.xlsx z danymi zbiorczymi z poszczególnych miesięcy danego roku. Są one pobierane przy pomocy formuły, która jest łączem do pliku danego miesiąca (01_Styczeń_Arkusz_obliczeń_dobowych.xls)
Kod:
=ILE.NIEPUSTYCH('[01_Styczeń_Arkusz_obliczeń_dobowych.xls]1:31'!$G$16:$P$21)

Pliki w katalogu 2019 na kolejne miesiące (odpowiednio: 02_Luty_Arkusz_obliczeń_dobowych.xls ; 03_Marzec_Arkusz_obliczeń_dobowych.xls itd.)pojawiają się zawsze 1 dnia kolejnego miesiąca.
Problem polega na tym, że każdorazowo przy odświeżaniu danych pliku Zestawienie.xlsx excel zgłasza błąd łączy do plików z miesięcy których jeszcze nie ma w katalogu 2019.
W związku z tym proszę o podpowiedź, czy można np. formatowaniem warunkowym wczytać formułę zależnie od aktualnej daty (np. jeżeli data = lub > 1 luty 2019 wstaw formułę
Kod:
=ILE.NIEPUSTYCH('[02_Luty_Arkusz_obliczeń_dobowych.xls]1:31'!$G$16:$P$21)

W przeciwnym razie wartość komórki 0 lub pusta.
Nie wiem czy jest to w ogóle wykonalne ale może ktoś z expertów znajdzie rozwiązanie.
Będę wdzięczny za pomoc.
Pozdrawiam
ID posta: 361286 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 25-01-2019, 12:49   

Załącz jakieś przykładowe pliki do testowania, tylko skróć im nazwy!!!!
Czy można używać VBA?
ID posta: 361289 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 25-01-2019, 12:53   

Tak, jeżeli nie ma innej opcji to może być VBA ;-) . Postaram się przygotować odpowiednie przykładowe pliki ale dopiero po 15-ej (praca ;-) )
ID posta: 361290 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 25-01-2019, 15:34   

Dołączam pliki z przykładem. W katalogu 2019 jest 6 plików (przykładowe miesiące od styczeń do czerwiec). Plik Zestawienie.xlsx zawiera odwołania do 12 plików (cały rok). Chodzi mi o to żeby w komórkach B2:B13 pojawiały się formuły zależnie od aktualnej daty w komórki E1.
(wg zasad, które podałem w pierwszym opisie)
Raz jeszcze proszę o pomoc :->

2019.rar
pliki z przykładem:
Pobierz Plik ściągnięto 12 raz(y) 50.18 KB

ID posta: 361303 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 25-01-2019, 17:30   

Bez VBA ja tego zrobić nie potrafię. Może ktoś inny wpadnie na dobry pomysł? Nie potrafię zablokować wyświetlania tego okienka z żądaniem wyboru pliku, które jest wyświetlane w przypadku nieistnienia pliku, do którego się odwołujemy. W VBA to okno można zablokować przez DisplayAlerts, ale to działa tylko podczas wykonywania makra.
Przesyłam 2 propozycje. Obie są makrami wykonywanymi przy otwieraniu skoroszytu (Workbook_Open). Wariant bez jedynki w nazwie sprawdza, czy w lokalizacji pliku Zestawienie.xlsm są pliki o podanych nazwach (z numerami 1 do 12), i jeśli są wprowadza odpowiednie formuły do odpowiednich komórek (w kol. B). Jeśli pliku nie ma komórka pozostaje pusta.
Wariant z jedynką nie sprawdza obecności plików tylko blokuje wyświetlanie alertu. Zmieniłem też funkcję ILE.NIEPUSTYCH na ILE.LICZB, żeby w razie braku pliku były wyświetlane zera.
Generalnie makra muszą być "szyte na miarę", to znaczy wszystkie nazwy plików, lokalizacje muszą być wpisane do makra, albo odczytywane z jakiejś komórki w arkuszu, ale to też musi być ustalone.

2019.zip
Pobierz Plik ściągnięto 11 raz(y) 82.25 KB

ID posta: 361313 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 25-01-2019, 18:59   

Dzięki za te rozwiązania. Wstępnie wariant bez jedynki wydaje się lepszy. W pliku Zestawienie 1.xlsm wkradł się chyba jakiś błąd. Przy braku plików od 7-12 są wartości 0. Natomiast jak usunę pliki np. 2-6 to w komórkach kolumny B od lutego do czerwca pojawia się jakaś dziwna wartość 300 :-/ .
Proszę, rzuć jeszcze okiem na to :->
Spróbuję zaimplementować to do właściwych plików i zobaczę jak się całość zachowa. :-D
ID posta: 361316 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 28-01-2019, 09:48   

Nie przetestowałem tego dokładnie, funkcja ILE.NIEPUSTYCH zlicza błędy w nieistniejących komórkach, a ponieważ to jeszcze jest odwołanie 3-W, to chyba głupieje przy tym. Proszę spróbować zmodyfikowanej wersji, w której testuję pierwszą komórkę zakresu pod kątem błędu.

Zestawienie2.xlsm
Pobierz Plik ściągnięto 4 raz(y) 27.05 KB

ID posta: 361440 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 29-01-2019, 11:08   

W zmodyfikowanej wersji przy dodawaniu kolejnych miesięcy jest ok. Ale z chwilą usunięcia np. dodanych wcześniej plików 07.xlsx i 08.xlsx w właściwych komórkach (ilość razem) pojawia się znowu ta wartość 300. Mam również problem z zmianą nazw plików miesięcy. W przykładzie to odpowiednio 01.xlsx; 02.xlsx...itd. Niestety nie mogę użyć u siebie tak skróconych nazw. Musi to być przynajmniej w takiej formie: 01_Styczeń.xlsx; 02_Luty.xlsx.
Proszę więc o pomoc jak to zmienić w VBA pliku przykład2 :->
ID posta: 361517 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 29-01-2019, 11:26   

Dołącz jeszcze raz te pliki, bo nie wiem co to jest przykład2. Podaj jaką chcesz mieć docelowo formę nazwy pliku. To wszystko można zmienić, tylko do testów długie nazwy są niewygodne. Napisz też, co dokładnie będziesz liczyć na podstawie zawartości plików. Czy chodzi tylko o ILE.NIEPUSTYCH? Bo to wszystko trzeba przetestować w kontekście błędów. Tam są odwołania 3-W, które nie są super dopracowane i mają różne ograniczenia.
ID posta: 361519 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 29-01-2019, 12:39   

Przepraszam za wprowadzenie w błąd :oops: Chodzi o Zestawienie2. Dla pewności dołączam całość w załączniku

2019.zip
Oto przykład:
Pobierz Plik ściągnięto 2 raz(y) 101.31 KB

ID posta: 361521 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 29-01-2019, 13:20   

Zmieniłem te nazwy plików, tak żeby były nazwy miesięcy. Katalog z plikami należy rozpakować z archiwum. Ja nie potrafię odtworzyć tego błędu, o którym wspominasz. Po zmianie lokalizacji plików należy zestawienie otworzyć. Za pierwszym razem może pojawić się monit, że są odwołania do nieznanych lokalizacji. Odpowiedzieć nie, zapisać plik i otworzyć ponownie.

2019 (2).zip
Pobierz Plik ściągnięto 4 raz(y) 108.3 KB

ID posta: 361523 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 30-01-2019, 14:08   

Raz jeszcze dzięki, powoli "przebijam się" przez to. Praktycznie udało mi się zaadoptować wszystko do struktury moich plików i ...DZIAŁA :-D . Natomiast powstała potrzeba zliczenia na takiej samej zasadzie jeszcze jednego zakresu i tu już utknąłem :-/ . W związku z tym raz jeszcze dołączam pliki z przykładem i proszę o pomoc ;-)

2019(3).rar
Oto przykład:
Pobierz Plik ściągnięto 2 raz(y) 84.97 KB

ID posta: 361579 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 30-01-2019, 16:47   

Nie bardzo rozumiem, z czym miałeś problem. Trzeba było powtórzyć jeszcze raz tę samą instrukcję wstawiającą formułę tylko do innej kolumny.

2019.zip
Pobierz Plik ściągnięto 3 raz(y) 86.42 KB

ID posta: 361591 Skopiuj do schowka
 
 
ted11 
Exceloholic


Posty: 153
Wysłany: 31-01-2019, 09:48   

Dzięki. Popełniałem drobny błąd w składni kodu (brak dostatecznej wiedzy i doświadczenia :-/ ) i dlatego poległem. Natomiast nadal mam problem z tymi błędnymi wartościami 300 w komórkach odnoszących się do brakujących plików (np usuniętych)
W dołączonym przykładzie mam teraz wszystkie 12 plików z danymi poszczególnych miesięcy (dla pełnego zobrazowania stanu rzeczywistego). W pliku Zestawienie2.xlsm wszystko jest zliczone poprawnie, a plik zapisany. Jeżeli usunę np. pliki dwóch ostatnich m-cy np.(11_listopad.xlsx i 12_grudzień.xlsx) następnie otworzę plik Zestawienie2.xlsm i zgodnie z komunikatem odświeżę dane to w komórkach zliczających dane z listopada i grudnia pojawia się wartość 300. Po ponownym dodaniu tych plików i odświeżeniu wszystko jest ok. Problem dotyczy dowolnej liczby usuniętych plików m-cy. Byłbym wdzięczny raz jeszcze za próbę rozwiązania tego problemu.
Mam jeszcze jedno pytanie związane z lepszym zrozumieniem składni kodu VBA. Mianowicie co w poniższym fragmencie kodu oznacza parametr: 30 * i
Kod:
npl = mypath & "\[" & Format(i, "00") & "_" & Format(30 * i, "mmmm") & ".xlsx]"

Ja byłem zmuszony zachować pierwotną strukturę nazw i zrobiłem to tak:
Kod:
npl = mypath & "\[" & Format(i, "00") & "_" & Format(30 * i, "mmmm") & "_Arkusz_obliczeń_dobowych.xls]"


2019(4).rar
Oto przykład:
Pobierz Plik ściągnięto 2 raz(y) 130.51 KB

ID posta: 361637 Skopiuj do schowka
 
 
Maciej Gonet
Excel Expert


Pomógł: 1211 razy
Posty: 4149
Wysłany: 31-01-2019, 11:27   

Potwierdzam występowanie tego "problemu 300". Domyślam się, że jest on związany z tym, że Excel standardowo zapamiętuje poprzednie wartości łączy i mimo, że pliku już nie ma, jeśli zablokujemy mu możliwość odświeżenia danych, to bierze to, co ma zapisane. Wydaje mi się, że problem rozwiąże polecenie:
Kod:
Me.SaveLinkValues = False
które dopisałem w module skoroszytu w procedurze Workbook_Open. Wyłącza ono zapisywanie wartości łączy zewn. wraz z plikiem. Proszę to jeszcze przetestować.
Co do znaczenia frazy
Kod:
Format(30 * i, "mmmm")
to służy ona do uzyskania nazwy miesiąca o numerze "i" w języku lokalnym. Samo 30*i jest numerem seryjnym daty (z roku 1900). To, że to działa w lutym wynika stąd, że projektując Excela przyjęto, że rok 1900 był rokiem przestępnym (chociaż nie był, zgodnie z kalendarzem gregoriańskim). A błąd ten świadomie skopiowano z programu Lotus 1-2-3, który był poprzednikiem Excela, a chodziło o zachowanie zgodności. W VBA błąd ten skorygowano przenosząc początek kalendarza (dzień nr 1) na 31-12-1899. I dzięki temu ten sposób na miesiące działa.

Zestawienie2.xlsm
Pobierz Plik ściągnięto 5 raz(y) 26.2 KB

ID posta: 361647 Skopiuj do schowka
 
 
Wyświetl posty z ostatnich:   
Odpowiedz do tematu
Nie możesz pisać nowych tematów
Nie możesz odpowiadać w tematach
Nie możesz zmieniać swoich postów
Nie możesz usuwać swoich postów
Nie możesz głosować w ankietach
Nie możesz załączać plików na tym forum
Możesz ściągać załączniki na tym forum
Dodaj temat do Ulubionych
Wersja do druku

Skocz do:  

Powered by phpBB modified by Przemo © 2003 phpBB Group
Theme xandgreen created by spleen& Programosy modified v0.3 by warna
Opieka techniczna www.marketingNET.pl

Archiwum

Strona używa plików cookies.

Kliknij tutaj, żeby dowiedzieć się jaki jest cel używania cookies oraz jak zmienić ustawienia cookie w przeglądarce.
Korzystając ze strony użytkownik wyraża zgodę na używanie plików cookies, zgodnie z bieżącymi ustawieniami przeglądarki.
Sprawdź, w jaki sposób przetwarzamy dane osobowe