• Zapisz się na newsletter, aby otrzymywać powiadomienia o najnowszysch wpisach.

Wielkość liter taka, jak w zdaniu (formuła)

Czyli robimy Worda z Excela 🙂

Rety, już kolejny wpis o funkcjonalnościach Worda w Excelu. Ostatnio była korespondencja seryjna i indeks górny (np. m2), a dziś o wielkości liter.

Już wielokrotnie napotkałam sytuację, że dostałam dane, gdzie wszystko był napisane małymi literami, a ja chciałam, aby wielkość liter była taka, jak w zdaniu, czyli zamienić wielkość pierwszej litery w zdaniu na wielką, a pozostałe na małe. Sprawa pozornie prosta, wystarczy znaleźć odpowiednią funkcję, ale… nie ma takiej 🙂 W Excelu mamy 3 funkcje umiejące zmienić wielkość liter, ale żadna nie umie zmienić ich w taki właśnie sposób. W Wordzie – nie ma problemu:

Opcje dostępne w Wordzie

Opcje dostępne w Wordzie

Ale nie w Excelu… Na szczęście na spokojnie można poradzić sobie z tą sytuacją. Tylko za pomocą formuły, używającej kilku funkcji tekstowych.

Czytaj dalej

Odwołania strukturalne tabel w formułach: blokowanie kolumn

Czyli jak zmusić Excela, by odwoływał się do wybranej kolumny przy kopiowaniu

Na pewno spotkaliście się z narzędziami tabel w Excelu. Mają one świetne właściwości, choćby takie, że umieją powiększać swoje rozmiary. Dodatkowo jak wpiszemy do nich formuły – nie musimy ich kopiować w dół – tabele robią to za nas. Natomiast odnośnie formuł to robią coś jeszcze, niekoniecznie lubianego przez użytkowników… stosują odwołania strukturalne, czyli odwołania do nazw elementów tabeli, jak np. kolumna, czy nagłówek. Dla prostych operacji to super sprawa, jednak dla trudniejszych może okazać się problemem…

Pierwszy raz na ten problem natknęłam się, gdy tworzyłam plik dla przedszkola moich dzieci, którego zadaniem było rozliczanie opłat za pobyt dzieci po godzinie 13:00. Wszystkie dane trzymałam w tabeli i wyliczałam tam kilka wartości na podstawie danych w jednej kolumnie. Jakież to było dla mnie irytujące, kiedy okazywało się, że podczas kopiowania formuły, Excel za każdym razem zmienia adresy kolumn (czyli zachowuje się tak, jak normalna kopiowana komórka!), podczas gdy ja chciałam, aby zmieniał mi adres tylko jednej (ale w żaden sposób mu tego nie powiedziałam :))! Nie umiałam tego zrobić, więc pisałam każdą formułę oddzielnie. Oh, dear God! BTW: teraz tak sobie o tym myślę i śmieję się z siebie, no bo niby skąd Excel miał wiedzieć, którą kolumnę Cierzniewska chciała zablokować? Chciałam, żeby Excel mi to wyczytał w myślach najwyraźniej 😉 Tego akurat nie umie, ale na szczęście umie „blokować” kolumny tabel w formułach. W tym wpisie zobaczysz jak.

Czytaj dalej

Sortowanie list niestandardowych np. dni tygodnia

W poprzednim wpisie pokazywałam jak tworzyć listy niestandardowe w Excelu, a dziś pokażę jak po nich sortować. Oczywiście możemy też sortować po już istniejących listach np. po dniach tygodnia. Dokładnie to pokażę w dzisiejszym wpisie. Sposób jest prościutki i szybki. Zobaczcie z jakimi danymi mamy do czynienia:

Dane do sortowania

Dane do sortowania

Czytaj dalej

Film: Import liczb do systemu z użyciem funkcji TEKST

 

Film powstał w odpowiedzi na pytanie Piotra, które brzmiało tak:

Otrzymujemy listę ładunkową w formacie jak w załączniku , problem powoduje zapis ciężarów kontenerów (kolumna weight). Program ładunkowy przyjmuje do obliczeń tylko zapis  10 256.000 zamiast 10256 jak jest na listach.

Mimo, że w Excelu sformatujemy komórkę tak, jak nam się podoba, to wartość tej komórki nadal będzie taka, jak Excel tego chce (a my nie!). Na co dzień nie sprawia nam to problemu, jednak gdy chcemy takie dane zaimportować do systemu – często chce on od nas,aby liczby było odpowiednio zapisane. Właśnie tak, jak my sformatujemy komórkę. Problem w tym, że formatowanie komórki nie wystarcza… Tu z pomocą przychodzi nam funkcja TEKST, która umie wpisać do komórki dokładnie taką wartość, jaki nadaliśmy format. To właśnie pokazuję w tym filmie, posiłkując się dodatkowo funkcją PODSTAW.

Oto film, w którym pokazuję rozwiązanie problemu:

Czytaj dalej

Wklej specjalnie wartości – skrót klawiszowy???

Ile razy dziennie wklejacie specjalnie wartości w Excelu? Pewnie co najmniej kilkanaście 🙂 Może łatwiej byłoby skrótem klawiszowym? A znacie skrót klawiszowy, który by to robił? Na pewno by się przydał, jednak twórcy Excela go nie przewidzieli… Trzeba więc sobie poradzić w inny sposób.

Miałam ten problem jak jeszcze pracowałam na Excelu 2003, gdzie nie było cudownych podpowiedzi przy wklejaniu tak, jak jest teraz. Wkleić specjalnie wartości potrzebowałam bardzo często i brak tego skrótu mi niesamowicie przeszkadzał. Wkurzyłam się więc i stworzyłam makro, które robi jedną prostą rzecz: wkleja specjalnie wartości do zaznaczonej komórki. Tyle. Rozwiązanie okazało się dla mnie zbawienne i nie umiem bez niego żyć. Jestem od niego wręcz uzależniona 🙂

W tym wpisie pokażę wam jak taki skrót zrobić i, co najważniejsze, co zrobić, aby był on dostępny w każdym otwartym przez nas pliku Excela. W tym celu stworzę dodatek do Excela i pokażę jak go zainstalować.

Czytaj dalej

Mediana w tabeli przestawnej?

Ostatnio jedna z uczestniczek prowadzonego przeze mnie szkolenia Excel w dziale HR zadała mi pytanie, którego jeszcze nikt wcześniej mi nie zadał. Miałam więc bardzo dużą motywację, aby szybko jej odpowiedzieć. 😉 Pytanie brzmiało: Jak obliczyć medianę w tabeli przestawnej? Chodziło konkretnie o ustalenie przeciętnego wynagrodzenia na danym stanowisku w danym regionie firmy w Polsce.

Tabela przestawna oferuje nam wiele funkcji agregujących, taki jak oczywiście suma czy średnia, ale też maksimum czy odchylenie standardowe. Jest nawet wariancja, natomiast nie ma mediany. Szkoda – to by załatwiło sprawę 😉 Pola obliczeniowe też nie na wiele się zdadzą, ponieważ operują na zagregowanych danych, a my chcemy na pojedynczych wynagrodzeniach. Pozostaje więc tylko zabawa z danymi źródłowymi. Tak też zrobiłam.

Czyli z takich danych:

Mediana w tabeli przestawnej - dane źródłowe

Dane źródłowe (fragment)

Chcę takie:

Mediana w tabeli przestawnej - wynik

Wynik

Czytaj dalej

Średnia niezerowych wartości z wybranych kolumn

Jakiś czas temu napisała do mnie Dorota z bardzo ciekawym problemem. Zobaczcie:

Mam dość duży zakres danych, z których interesują mnie wartości znajdujące się w co którejś kolumnie, a jest tych kolumn kilkadziesiąt (tych, które mnie interesują). Potrzebuję z tych danych zrobić średnią, z pominięciem zer. Średnia.jeżeli nie działa, ponieważ ma za dużo atrybutów (te wszystkie komórki, wpisane oddzielnie). Masz jakiś pomysł, jak można by to zrobić?
Dodam, że zależy mi na utrzymaniu tych danych w takim układzie, w jakim są, więc wyciąganie ich do oddzielnego arkusza, po to, żeby skorzystać z średniej jeżeli, odpada. No chyba, że się poddam. 😉

Czyli Dorota ma ogromniastą tabelę z „milionem” kolumn i z niektórych z nich chce wyciągnąć średnią. Jeśli w jakiejś z wybranych kolumn jest zero – ma nie zostać uwzględnione w liczeniu średniej. Czyli tak na prawdę mamy dwa warunki do spełnienia:
1 – liczby niezerowe,
2 – odpowiednia kolumna.

Formatka wygląda tak (oczywiście w moim przykładzie jest znacznie mniej danych, a przede wszystkim – kolumn ;)):

Średnia wartości nieujemnych z wybranych kolumn - formatka

Formatka

Czytaj dalej

Funkcja PRZESUNIĘCIE pomaga liczyć podsumy

Ostatnio napisał do mnie Radek z ciekawym pytaniem. Miał on bardzo dużą, dość specyficznie zbudowaną tabelę, w której chciał zastosować podsumowania części danych (coś á la sumy częściowe). Na koniec chciał mieć sumę wszystkiego. Radek się zastanawiał, czy da się to zrobić za pomocą jednej formuły. Przyznam, że rozwiązanie do oczywistych nie należy i jest bardzo ciekawe, bo zawiera funkcję PRZESUNIĘCIE…

Formatka wygląda tak:

Przesunięcie formatka

Formatka

W żółtej komórce F3 ma się znaleźć suma wszystkich wartości, a te wartości mają być takie:

  1. Tam, gdzie są szare pola, a w kolumnie B jednostką jest „ryczałt”, należy podsumować wszystkie dane pod tym ryczałtem (aż do następnego ryczałtu),
  2. Tam, gdzie jednostka jest inna niż „ryczałt”, należy pomnożyć wartości z kolumn C i D.

W kolumnie E są formuły wstawione ręcznie w odpowiednie komórki, które sumują tam, gdzie jest „ryczałt”, a w pozostałe komórki wstawione jest proste mnożenie (plik z rozwiązaniem znajdziesz na końcu artykułu). Sam przyznasz, że jest to mrówcza praca, a w całym ćwiczeniu chodzi o to, aby użyć jednej formuły. Do dzieła! 🙂

Czytaj dalej

Pobieranie danych z arkusza wskazanego na liście rozwijanej

Dziś miało być o tabelach przestawnych, ale Arek zapytał mnie o to, jak napisać formułę, która będzie pobierała dane z arkusza wskazanego w jakiejś komórce. Temat ten chodził za mną już od dawna, więc postanowiłam przełożyć wpis o tabelach przestawnych (które lubię używać, ale nie lubię o nich mówić i pisać ;)) i zająć się tym właśnie przypadkiem. Dorzuciłam też coś od siebie, czyli wybór arkusza z listy rozwijanej – zawsze to jakiś dodatkowy bajerek 🙂

A więc dziś mamy podaną sprzedaż (ilość i wartość) handlowców w styczniu i lutym, a chcemy mieć raport, w którym zdecydujemy, z którego miesiąca dane chcemy oglądać i dodatkowo – jaka jest średnia cena sprzedanych przez handlowców produktów. W Excelu mamy więc 2 arkusze z danymi: sty i lut, oraz arkusz Raport, w którym chcemy wyświetlić dane z odpowiedniego arkusza dla wybranych osób. Tak wygląda ta sytuacja:

Struktura danych

Struktura danych

Do dzieła!

Czytaj dalej