Wartość zależna od wybranej komórki

[brex87]

Witam, czy jest możliwość zrobienia w Calcu zależności względem wybranej komórki, która będzie wyświetlała wartości na podstawie konkretnej nazwy kolumny z innych zeszytów utworzonych w arkuszu ?
Chciałbym osiągnąć następujący efekt:

=Jeżeli A1=STYCZEŃ to wartości z zeszytu NAZWA1 z komórek B2:B21 oraz wartości z zeszytu NAZWA2 z komórek B2:B21.

=Jeżeli A1=LUTY to wartości z zeszytu NAZWA1 z komórek C2:C21 oraz wartości z zeszytu NAZWA2 z komórek C2:C21

[Jermor]

Oczywiście, że jest możliwe. Nie będę tego opisywał tutaj. Wyjaśnienia są w zwrotnym pliku.

[brex87]

Dziękuję za odpowiedź.
Zacząłem sprawdzać arkusz i mam kilka pytań.
Nazywanie zakresu danych tyczy się tylko do konkretnej kolumny, a jeśli w grę wchodzą wynikowe wartości, które są różnicą 2 pól z przesuniętych kolumn to rozumiem, że formuła INDEKS niestety tutaj się nie sprawdzi ?
Mimo tego, że zdefinowałem różne nazwy obszarów to nie byłem w stanie wyświetlić informacji z przesuniętych kolumn.

jeżeli STYCZEŃ to w kolumnie B2 = wartości z zeszytu NAZWA1 z kolumny D2.
jeżeli STYCZEŃ to w kolumnie C2 = wartości z zeszytu NAZWA2 z kolumny D2.

jeżeli LUTY to w kolumnie B2 = wartość z zeszytu NAZWA1 z kolumny G2.
jeżeli LUTY to w kolumnie C2 = wartość z zeszytu NAZWA2 z kolumny G2.

[Jermor]

Nazywanie obszaru nie dotyczy tylko kolumny. Zamiast pracowicie we wszystkich formułach wpisywać np. A1:H23 można wpisać nazwę nadaną temu obszarowi. Poczytaj o nich np tu https://Yestok.pl/ooo/y43.php. Funkcja INDEKS ze wskazanego obszaru zwraca zawartość komórki znajdującej się we wskazanym wierszu i wskazanej kolumnie tego obszaru.
W tym twoim nowym arkuszu nazwa "Nazwa1A" określa obszar B2:E5 w arkuszu NAZWA1, a "Nazwa2a" określa obszar B2:M21 w arkuszu NAZWA2.
Posługując się tymi nazwami w funkcji INDEKS możesz użyć co najwyżej 4. wiersza i 4-tej kolumny w pierwszym obszarze, a w drugim 19-stego wiersza i 12-tej kolumny. Podanie indeksów spoza przedziału obszaru podanego jako pierwszy argument generuje błąd.
W tym nowym podejściu, który teraz przedstawiasz, dane dotyczące jednego miesiąca zajmują trzy sąsiadujące kolumny a nie jedną, jak to przedstawiłeś poprzednio. Musisz zatem obliczyć numer kolumny. Nazwane obszary w arkuszach NAZWA1 i NAZWA2 będą teraz miały nie 12 kolumn ale 36. Teraz numer miesiąca nie będzie wskazywał konkretnej kolumny, tylko grupę trzech kolumn. Ponieważ chcesz pobierać zawartość trzeciej kolumny w grupie, to indeks kolumny dla obszaru funkcji INDEKS musi być wyliczony jako MIESIĄC(A1)*3, czyli w styczniu będzie to kolumna 3. w lutym 6. w marcu 9. itd. w grudniu 36. Kolumna do wybrania środkowej wartości w grupie to MIESIĄC(A1)*3-1 a do wybrania pierwszej MIESIĄC(A1)*3-2.

[brex87]

Bardzo dziękuję za odnośnik do lektury, wszystko działa jak należy!!

[brex87]

Mam jeszcze jeden problem, czy jest możliwość zastosowania sumy w zakresie kolumn z warunkami ?
=suma(D$3:D$33) i jeżeli któryś wierszy w kolumnie z zakresu D3:D33 posiada wartość UW lub UOJ lub CH itd to traktuj jako 08:00 (osiem godzin) i sumuj pozostałe wartości w kolumnie?

[Jermor]

Funkcja SUMA w obliczeniach pomija wartości tekstowe. Jeśli więc kody UW, UOK lub CH są jedynymi tekstowymi jakie mogą wystąpić, to formuła może wyglądać tak:

Kod: Zaznacz cały

=SUMA(D3:D33)+CZAS(8;0;0)*(LICZBA.WIERSZY(D3:D33)-ILE.LICZB(D3:D33))

a jeśli wiadomo, że zawsze chodzi wiersze od 3 do 33, to zamiast LICZBA.WIERSZY(D3:D33) można wpisać 31.
Jeśli tylko niektóre kody mają odpowiadać 8-śmiu godzinom, to formułę można napisać trak:

Kod: Zaznacz cały

SUMA(D3:D33)+CZAS(8;0;0)*LICZ.JEŻELI(D3:D33;"UW|CH|UOK")

Każdy ewentualny nowy kod można dopisać po znaku | (pionowa kreska). Dla tej formuły należy upewnić się, że w opcjach Calca zaznaczone jest "Włącz wyrażenia regularne w formułach", gdyż kryterium jest wyrażeniem regularnym.
Dla funkcji LICZ.JEŻELI wielkość liter przy sprawdzaniu kryterium nie odgrywa roli.

[brex87]

Dziękuję, warto jeszcze dodać, że przy ustalaniu nazw UW|CH|UOK nie mogą mieć one początkowo tych samych znaków np. UO|UOJ gdyż zliczanie wtedy nie działa.

Mam jeszcze jedno pytanie, a mianowicie nie potrafię poradzić sobie z nazwanym obszarem dla konkretnej komórki.
Chciałbym uzyskać odwołanie do konkretnej komórki na podstawie wybranego miesiąca.
W załączeniu przykład.

[Jermor]

Prawdopodobnie odkryłeś błąd, który należałoby zgłosić.
Zwłaszcza, że poprawnie będzie się wszystko liczyło, gdy alternatywne teksty będą wpisane od najdłuższego do najkrótszego słowa.
Jeśli chodzi o pytanie.
W ostatnim wierszu łączysz 3 sąsiadujące komórki i wpisujesz do nich normatywny czas pracy.
Zasada jest następująca, jeśli połączony jest zakres komórek, to wpisana wartość znajduje się tak naprawdę w komórce leżącej w lewym, górnym rogu obszaru. Aby tę wartość pobrać należy wskazać adres tej komórki.
Czyli twoje wartości normatywne są w pierwszej komórce tego obszaru. Spraw aby nazwany obszar obejmował także ten dodany wiersz. W tej połączonej komórce możesz wpisać formułę:

Kod: Zaznacz cały

=INDEKS(Nazwa1A;5;(MIESIĄC($A$1))*3-2)

w pierwszej kolumnie, oraz

Kod: Zaznacz cały

=INDEKS(Nazwa2a;5;(MIESIĄC($A$1)*3-2))

w drugiej.
Przykład na podstawie załączonego pliku. Wartość "miesiąc*3-2" podaje adres pierwszej kolumny, wspomniałem o tym w poprzednim poście.

[Jan_J]

[...] warto jeszcze dodać, że przy ustalaniu nazw UW|CH|UOK nie mogą mieć one początkowo tych samych znaków np. UO|UOJ gdyż zliczanie wtedy nie działa.

Prawdopodobnie odkryłeś błąd, który należałoby zgłosić.

Moim zdaniem błędu nie ma. Jest za to złożony układ opcji wpływających na wynik.

Narzędzia / Opcje /Calc /Oblicz
[x] Kryteria wyszukiwania muszą odnosić się do całych komórek
(*) Włącz wyrażenia regularne w formułach

Albo
[ ] Kryteria wyszukiwania muszą odnosić się do całych komórek
(*) Włącz wyrażenia regularne w formułach
ale wtedy dla uniknięcia nieporozumień musimy w wyrażeniu wyspecyfikować całą zawartość komórki, czyli np.

Kod: Zaznacz cały

LICZ.JEŻELI(D3:D33;"^(UW|CH|UOK)$")

// Narzekanie stetryczałego malkontenta
// dlaczego domyślnie włączone są `symbole wieloznaczne` a nie `wyrażenia regularne`?
// znam odpowiedź...

[brex87]

Witam, udało mi się ustabilizować cały arkusz, łącznie wyszły 24 zeszyty + 1 wynikowy.
Dziękuję za wskazówki i pomoc, prześledziłem jeszcze raz ustawienia i okazało się, że błędu nie było w ustawieniach, a w składni.
Zacząłem próbować pracy w arkuszy google, lecz niektóre funkcje nie działają poprawnie np.

Kod: Zaznacz cały

=SUMA(D$3:D$33)+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UW|CH|UO|UOJ|UNO|UB|DEL")

W calcu zlicza poprawnie, a w arkuszu google nie wyrzuca żadnego błędu, tylko nie uwzględnia wszystkich podanych wartości tekstowych.

Jeśli skróce formułe tylko do

Kod: Zaznacz cały

=SUMA(D$3:D$33)+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UW")

zlicza poprawnie.

Natomiast żeby zachować funkcjonalność z calca muszę powielać ciąg z formuły i dodawać następne warunki.

Kod: Zaznacz cały

+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UW")
+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"CH")
+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UO")
itd.

Czy te 2 arkusze różnią się od siebie na tyle, że trzeba przepisywać całość pod konkretny program z którego się korzysta?

[Jermor]

Z opisu funkcji LICZ.JEŻELI w GoogleDocs wynika, że jeśli kryterium jest ciągiem tekstowym, to można użyć tylko dwóch znaków zastępczych, w terminologii OpenOffice i Microsoftu zwanych symbolami wieloznacznymi, czyli * (gwiazdki) symbolu dowolnego tekstu i ? (znak zapytania) jako odpowiednika pojedynczego dowolnego znaku.
Jeżeli chodzi o formułę

+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UW")
+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"CH")
+CZAS(8;0;0)*LICZ.JEŻELI(D$3:D$33;"UO")

to można ją zapisać
+CZAS(8;0;0)*(LICZ.JEŻELI(D$3:D$33;"UW")+LICZ.JEŻELI(D$3:D$33;"CH")+LICZ.JEŻELI(D$3:D$33;"UO"))
skracając nieco cały zapis.

[brex87]

Dokładnie tak samo zrozumiałem dokumentację, zgłosiłem opinię do supportu, zobaczymy czy uwzględnią moją wskazówkę.

[Jermor]

Tym bardziej, że wszędzie piszą iż stosują wyrażenia regularne zgodne z RE2. Tylko, że jak na razie są dopuszczalne w operacjach Szukaj oraz kilku instrukcjach klasy REGEX czyli REGEXMATCH, REGEXEXTRACT i REGEXREPLACE (w Calcu jest jedna a jej parametry decydują o wyniku).