Jak oznaczyć / oznaczyć elementy w arkuszu kalkulacyjnym


14

Jestem początkującą w arkuszach kalkulacyjnych, więc mam nadzieję, że wybaczysz mi, jeśli zadam super oczywiste pytanie.

Czy można używać tagów w arkuszu kalkulacyjnym Excel / Google? Tworzę arkusz kalkulacyjny, aby rejestrować wszystkie artykuły i książki, które czytam. Powiedzmy, że czytam „Ja, Klaudiusz”. Chcę nadać im następujące tagi: historia, fikcja, biografia, niepełnosprawność, polityka, dramat. Następnie, jeśli chcę wyświetlić wszystkie artykuły / książki oznaczone „polityką”, mogę wyszukać / wyświetlić / przestawić za pomocą tego tagu.

Być może najlepiej tagi powinny znajdować się w jednej komórce, a każde słowo należy oddzielić przecinkiem. Gdyby każde słowo znajdowało się we własnej komórce, myślę, że spowodowałoby to bałagan w tabeli.

Jestem również otwarty na inne rozwiązania w zakresie etykietowania.

Dzięki!


Nigdy nie widziałem rozwiązania tagów w programie Excel, ale to nie znaczy, że nie ma takiego. Może sprawdź Evernote
Raystafarian

Odpowiedzi:


13

O ile mi wiadomo, w programie Excel nie ma wbudowanych funkcji, które mogą analizować i podsumowywać tagi rozdzielane przecinkami. Możesz oczywiście stworzyć własne rozwiązanie z funkcjami arkusza i odrobiną VBA. Oto szybkie rozwiązanie tego problemu.

Krok 1 : Naciśnij Alt+, F11aby otworzyć panel edytora VBA w programie Excel. Wstaw nowy moduł i wklej ten kod dla funkcji niestandardowej.

Public Function CCARRAY(rr As Variant, sep As String)
'rr is the range or array of values you want to concatenate.  sep is the delimiter.
Dim rra() As Variant
Dim out As String
Dim i As Integer

On Error GoTo EH
rra = rr
out = ""
i = 1

Do While i <= UBound(rra, 1)
    If rra(i, 1) <> False Then
        out = out & rra(i, 1) & sep
    End If
    i = i + 1
Loop
out = Left(out, Len(out) - Len(sep))
CCARRAY = out
Exit Function

EH:
rra = rr.Value
Resume Next

End Function

Ta funkcja umożliwia tworzenie list oddzielonych przecinkami w celu podsumowania posiadanych danych znaczników.

Krok 2 : W arkuszu wprowadź w komórce (H2 w poniższym przykładzie) znacznik, który chcesz wyszukać. W komórce po prawej wprowadź następującą formułę, naciskając Ctrl+ Shift+ Enter.

=IFERROR(CCARRAY(IF(NOT(ISERROR(FIND(H2,$B$2:$B$6))),$A$2:$A$6),", "),"No matches found.")

Naciśnięcie Ctrl+ Shift+ Enterpowoduje wprowadzenie formuły jako formuły tablicowej. Zostanie wyświetlony w otoczeniu {...}na pasku formuły. Zauważ, że we wzorze $B$2:$B$6jest zakres, który zawiera wszystkie tagi dla pozycji wymienionych w $A$2:$A$6.

wprowadź opis zdjęcia tutaj

EDYCJA:
Jeśli nie przeszkadza ci to, że dopasowania są wyświetlane w kolumnie zamiast na liście w jednej komórce, możesz zwrócić dopasowania dla znaczników, używając tylko funkcji arkusza.

Gdzie są tytuły Column A, znaczniki są Column B, a tag szukasz jest H2, można użyć następującej formuły tablicowej w I2i wypełnić w dół tak daleko, jak trzeba:

=IFERROR(INDEX($A$1:$A$6,SMALL(IF(NOT(ISERROR(FIND($H$2,$B$1:$B$6))),ROW($B$1:$B$6),2000000),ROW()-1)),"")

wprowadź opis zdjęcia tutaj

Formuła działa najpierw poprzez utworzenie tablicy liczb na podstawie tego, czy znaczniki w każdym wierszu zawierają wyszukiwane hasło. Jeśli znaleziono dopasowanie, numer wiersza jest zapisywany w tablicy. Jeśli nie zostanie znaleziony, 2000000 zostanie zapisany w tablicy. Następnie SMALL(<array>,ROW()-1)część formuły zwraca ROW()-1najmniejszą wartość z tablicy. Następnie ta wartość jest przekazywana jako argument indeksu do INDEX()funkcji, gdzie zwracana jest wartość tego indeksu w tablicy tytułów. Jeśli liczba większa niż liczba wierszy w tablicy tytułowej zostanie przekazana INDEX()jako argument, zwracany jest błąd. Ponieważ 2000000 jest przekazywany jako argument, gdy nie znaleziono żadnych dopasowań, zwracany jest błąd. W takim przypadku IFERROR()funkcja powraca "".

Ważne jest, aby zrozumieć, jak ROW()jest stosowany w tej formule. Jeśli chcesz wyświetlić listę wyników zaczynającą się w innym wierszu, musisz dostosować drugi argument SMALL()funkcji, aby zwracała pierwszą najmniejszą wartość z tablicy. Na przykład, jeśli lista wyników zaczyna się w wierszu 1 zamiast w wierszu 2, należy użyć SMALL(...,ROW())zamiast SMALL(...,ROW()-1).

Ponadto, jeśli lista tytułów i tagów nie rozpoczyna się w wierszu 1, musisz również dostosować formułę. Drugi argument IF()funkcji musi zostać dostosowany, aby dopasowanie w pierwszym wierszu danych zwróciło 1. Np. Jeśli lista tytułów rozpoczyna się w wierszu 2 zamiast w wierszu 1, konieczne będzie uwzględnienie formuły IF(...,ROW($A$2:$A$7)-1,...)zamiast IF(...,ROW($A$1:$A$6),...).


Wow, działa idealnie! Jednak wolę to w Arkuszu kalkulacyjnym Google. Ale używa JavaScript. Spróbuję tam zrobić coś podobnego, chociaż jestem kompletnym i całkowicie początkującym językiem. Jeszcze tylko dwa pytania: co to znaczy „podsumowywać” dane? Co robi Twój kod w kroku 1? Zadaję to pytanie w szczególności, ponieważ myślę, że mógłbym zrobić to samo z JavaScript w Arkuszu kalkulacyjnym Google. Dziękuję bardzo za twoje rozwiązanie!
nusantara

Świetnie, cieszę się, że się przyda. Przez „podsumowanie” mam na myśli utworzenie listy pasujących tytułów oddzielonych przecinkami. W tym celu wymagany jest VBA. Dzięki wbudowanym funkcjom Excela dość łatwo jest stworzyć formułę tablicową, która może wybierać pasujące tagi. Jednak nie można zwrócić tych wyników w jednej komórce bez VBA, ponieważ CONCATENATEfunkcja w programie Excel nie może przyjąć tablicy jako argumentu (odczytuje tylko pierwszy element z tablicy, jeśli spróbujesz). Funkcja VBA służy do tworzenia list oddzielonych przecinkami z tablicy wartości.
Excellll,

Po napisaniu powyższej odpowiedzi zrozumiałem, że istnieje sposób na zrobienie czegoś podobnego bez VBA. Zamiast zwracać listę pasujących tytułów w jednej komórce, można zwrócić kolumnę pasujących tytułów. Mogę edytować mój post, aby uwzględnić tę metodę, jeśli jesteś zainteresowany.
Excellll,

Czy to możliwe bez VBA? Myślę, że tak byłoby lepiej. Jeśli nie jest to zbyt niewygodne, tak, byłoby wspaniale, gdybyś mógł dołączyć wspomnianą metodę. Byłoby to bardzo pomocne dla każdego, kto szuka tego tematu! Dziękujemy za wyjaśnienie „podsumowania”. Próbowałem też konkatenacji, ale tak, nie mogłem tego użyć tak, jak tego chciałem. Teraz wiem, że nie może zachowywać się jak kłótnia.
nusantara

Tak, to możliwe. Spróbuję opublikować rozwiązanie tutaj za kilka minut. Przepraszam za opóźnienie!
Excellll,

2

Podczas gdy programowanie to działa dobrze w niektórych przypadkach, okazało się, że zadziałało dla mnie ręczne podejście. Używając kolumn do znaczników, możesz łatwo oznaczyć element wiersza, wpisując 0 w kolumnie. Następnie masz jeden wiersz i wpisujesz 1s w każdej kolumnie znaczników dla tego wiersza (możesz pokolorować ten wiersz). Następnie podczas sortowania według jednego ze znaczników wiersz 1s (niebieski) działa jak separator. Pomiędzy przefiltrowanymi wynikami (0) a wszystkim innym (_).

Ma to kilka zalet. 1. Nie musisz za każdym razem wpisywać tagu. 2. Możesz łatwo sprawdzić, czy masz zduplikowane lub podobne elementy, które można zredukować do jednego.


1

Inny pomysł:

Skorzystaj z wbudowanej funkcji filtra. Filtruj według kolumny Tagi, a następnie możesz wyszukiwać wiersze zawierające dany tag.

Wiersz z listą tagów: komedia, horror, romans

pojawi się podczas wyszukiwania któregokolwiek z tych trzech tagów.


1

Moje podejście inne niż VBA polega na wyszczególnieniu znaczników w jednej kolumnie (powiedzmy w kolumnie H), oddzielając elementy przecinkami. Następnie używam kombinacji „Tekst do kolumn” i „Usuń duplikaty”, aby uzyskać moją listę tagów. Kopiuję to do wiersza nagłówka mojego głównego arkusza (w tym przypadku, zaczynając od kolumny L. Następnie w każdej komórce poniżej poszczególnych nagłówków znaczników wpisz:

=IF((ISNUMBER(SEARCH(L$1,$H2)))=TRUE,1,0)

To daje najlepsze z obu światów - kolumna znaczników (H) jest łatwa do odczytania; poszczególne kolumny znaczników (z ich zerami i zerami) są łatwe do odczytania przez komputer. Jest dynamiczny, jeśli pozostajesz w obrębie danego zestawu tagów; jeśli nie, musisz dodać nowy tag do wiersza nagłówka, a następnie skopiować formuły.

Stamtąd tabele przestawne są twoim przyjacielem.


0

Nie jest dla mnie jasne, w jaki sposób chcesz używać tagów. Ale w Arkuszach Google możesz użyć funkcji SPLIT (), aby podzielić ciąg tagów (rozdzielonych spacjami, przecinkami lub cokolwiek chcesz) na kilka innych komórek i jestem pewien, że nawet Excel ma funkcję, która może wyszukiwać komórka dla znacznika.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.