Parmi Notes Random short any article

Excel my Copilot, olah berbagai teks

Q: Saya masih ingin berdiskusi soal pengolahan sumber teks pada Excel 365 saya, region Indonesia.

Berikut saya punya contoh beberapa teks :

Cellular (Priority 1) connected to Telkomsel Flash (10.8.120.84) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.121.16) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.121.2) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.121.208) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.121.235) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.122.179) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.122.24) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.122.255) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.122.62) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.123.196) in SIM A Cellular (Priority 1) connected to Telkomsel Flash (10.8.123.32) in SIM A Cellular (Priority 1) disconnected (WAN failed SmartCheck) in SIM A Cellular (Priority 1) disconnected in SIM A Cellular (Priority 1) disconnected in SIM A Cellular (Priority 1) disconnected in SIM A Cellular failed to read SIM A, reloading SIM DC-BALI (AGREGABLI-MAST01, sn:1926-C5E4-CE93) connected to DC-BALI DC-BALI (AGREGABLI-MAST01, sn:1926-C5E4-CE93) disconnected from DC-BALI (handshake failed) DC-BALI failed to establish connection (failed to connect remote peer) DC-BALI: Initiated TLSv1.3 connection to 103.182.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 103.182.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 103.182.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 103.182.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 192.168.221.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 192.168.221.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 192.168.221.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 192.168.221.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 192.168.221.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 36.89.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 36.89.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 36.89.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-BALI: Initiated TLSv1.3 connection to 36.89.242.253 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA (AGREGAJKT-MAST01, sn:1926-C5CC-EC19) connected to DC-JAKARTA DC-JAKARTA (AGREGAJKT-MAST01, sn:1926-C5CC-EC19) disconnected from DC-JAKARTA (link failure detected) DC-JAKARTA failed to establish connection DC-JAKARTA failed to establish connection (failed to connect remote peer) DC-JAKARTA: Initiated TLSv1.3 connection to 103.167.219.51 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 103.167.219.51 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 103.167.219.51 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 103.167.219.51 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 192.168.111.1 using cipher suite (NONE) DC-JAKARTA: Initiated TLSv1.3 connection to 192.168.111.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 192.168.111.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 192.168.111.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 192.168.111.1 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 202.90.198.83 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 202.90.198.83 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 202.90.198.83 using cipher suite TLS_AES_256_GCM_SHA384 DC-JAKARTA: Initiated TLSv1.3 connection to 202.90.198.83 using cipher suite TLS_AES_256_GCM_SHA384 Device is offline Device is offline Device is online (8.5.2 build 5243) Device is online (8.5.2 build 5581) Radio selected channel 1 Radio selected channel 149 Remote Web Admin initiated from InControl 2 by yudha.mardyansyah@bmkg.go.id (180.252.174.65) Started up (8.5.2 build 5581) Time synchronization fail Time synchronization fail Time synchronization successful Time synchronization successful Time synchronization successful (0.peplink.pool.ntp.org) TO DC-JAKARTA: Initiated TLSv1.3 connection to 103.167.219.51 using cipher suite TLS_AES_256_GCM_SHA384 TO DC-JAKARTA: Initiated TLSv1.3 connection to 202.90.198.83 using cipher suite TLS_AES_256_GCM_SHA384 WAN VSAT 1 (Priority 1) connected (172.17.108.122) WAN VSAT 1 (Priority 1) disconnected (WAN failed PING test) WAN VSAT 2 (Priority 1) connected (172.17.108.106) WAN VSAT 2 (Priority 1) disconnected (WAN failed PING test) WAN VSAT-1 (Priority 1) connected (172.17.108.146) WAN VSAT-1 (Priority 1) connected (172.17.108.34) WAN VSAT-1 (Priority 1) connected (172.17.108.82) WAN VSAT-1 (Priority 1) disconnected (WAN failed PING test) WAN VSAT-1 (Priority 1) disconnected (WAN failed PING test) WAN VSAT-1 (Priority 1) disconnected (WAN failed PING test) WAN VSAT-2 (Priority 1) connected (172.17.108.154) WAN VSAT-2 (Priority 1) connected (172.17.108.42) WAN VSAT-2 (Priority 1) disconnected (WAN failed PING test) WAN VSAT-2 (Priority 1) disconnected (WAN failed PING test) WAN_GSM (Priority 1) connected to Telkomsel Flash (10.8.121.75) in SIM A WAN_GSM (Priority 1) disconnected in SIM A Wi-Fi AP Normal Mode, Turn ON SSIDs

anggap teks-teks ini berada pada kolom A

Nah di kolom B bersesuaian saya ingin membuat kelompok, contoh mengacu dari sampel teks: Cellular (Priority 1) connected to Telkomsel Flash (10.8.120.84) in SIM A

di sini ada beberapa catatan yang mirip, sehingga hanya beda seperti "Connected"; "disconnected" dan IP address

Saya ingin ini di kelompokkan kepada teks olahan yang sederhana, semisal: Cellular Connected atau Cellular disconnected dan sebagainya

Ada ide atau cara untuk menyesaikan ini dengan formula-formula canggih di Excel ?

A: Tentu, saya bisa bantu — dan tidak perlu web search karena ini murni soal formula Excel.

Mari kita bahas langkah demi langkah bagaimana cara mengelompokkan teks log panjang seperti contoh Anda menjadi kategori sederhana, misalnya:

  • Cellular Connected
  • Cellular Disconnected
  • DC‑BALI Connected
  • DC‑BALI Disconnected
  • WAN VSAT Connected
  • WAN VSAT Disconnected
  • dst.

Tujuannya: mengambil inti event dan membuang detail seperti IP address, build number, pesan panjang, dsb.

✅ IDE DASAR

Setiap teks log Anda selalu punya pola seperti:

  • Nama interface → contoh: Cellular, DC-BALI, WAN VSAT-1, WAN_GSM, dll.
  • Status → connected, disconnected, failed, online, offline, dll.

Jadi kita cukup:

  1. Ekstrak nama interface
  2. Ekstrak status
  3. Gabungkan menjadi kategori sederhana.

🎯 SOLUSI PRAKTIS DENGAN FORMULA EXCEL (Region Indonesia)

## 1️⃣ Formula untuk Menangkap “Status Utama” (Connected / Disconnected / Failed / Online dsb.)

Di kolom B (misalnya B2), gunakan formula:

=IF(ISNUMBER(SEARCH("connected";A2));"Connected";
IF(ISNUMBER(SEARCH("disconnected";A2));"Disconnected";
IF(ISNUMBER(SEARCH("failed";A2));"Failed";
IF(ISNUMBER(SEARCH("offline";A2));"Offline";
IF(ISNUMBER(SEARCH("online";A2));"Online";
"Other")))))

📌 Region Indonesia menggunakan titik koma (;), bukan koma.

Formula ini akan menghasilkan status ringkas seperti:

  • Connected
  • Disconnected
  • Failed
  • Offline
  • Online

## 2️⃣ Formula untuk Menangkap “Nama Interface”

Nama interface selalu berada di awal teks hingga muncul tanda ( atau spasi tertentu.

Formula berikut mengambil kata pertama atau dua kata pertama jika nama interface terdiri dari 2 kata (mis. "WAN VSAT"):

=IF(LEFT(A2;3)="WAN"; 
   LEFT(A2;FIND("(" ;A2&"(")-2);
   LEFT(A2;FIND(" " ;A2)-1)
)

Atau versi lebih fleksibel:

=LEFT(A2; FIND("(" ; A2 & "(") - 2)

Ini akan menghasilkan string seperti:

  • Cellular
  • DC-BALI
  • DC-JAKARTA
  • WAN VSAT 1
  • WAN_GSM
  • Wi-Fi AP

## 3️⃣ Gabungkan Jadi Kategori Sederhana

Jika nama interface (kolom C) dan status (kolom D), maka:

=C2 & " " & D2

Hasil akhirnya:

  • Cellular Connected
  • Cellular Disconnected
  • DC-BALI Connected
  • DC-BALI Failed
  • WAN VSAT 1 Connected
  • WAN VSAT 2 Disconnected
  • Device Online
  • Device Offline

🔥 REKOMENDASI: MEMBUAT VERSI FORMULA ALL‑IN‑ONE (langsung 1 sel)

Jika Anda ingin hanya satu formula di kolom B tanpa kolom bantu:

=TRIM(
    LEFT(A2; FIND("(" ; A2 & "(") - 2)
    & " "
    &
    IF(ISNUMBER(SEARCH("connected";A2));"Connected";
    IF(ISNUMBER(SEARCH("disconnected";A2));"Disconnected";
    IF(ISNUMBER(SEARCH("failed";A2));"Failed";
    IF(ISNUMBER(SEARCH("offline";A2));"Offline";
    IF(ISNUMBER(SEARCH("online";A2));"Online";
    "Other")))))
)

Copy ke bawah → selesai ✔️

🎁 BONUS (Cara Lebih Modern – Excel 365 Text Functions)

Karena Anda pakai Excel 365, bisa pakai fungsi TEXTSPLIT, TEXTBEFORE, TEXTAFTER, dll.

Contoh ekstrak interface name:

=TEXTBEFORE(A2;"(")

Ekstrak status:

=IFERROR(TEXTAFTER(A2;"connected";1);"")

Jika Anda ingin, saya bisa bantu:

✅ Membuat tabel kategorisasi otomatis
✅ Membuat PowerQuery untuk mengelompokkan ratusan ribu log
✅ Menyusun Pivot summary event (berapa kali disconnect, dll.)
✅ Membuat versi formula yang lebih pintar (regex Excel 365)

Kalau mau, kirimkan 10–20 contoh log lagi, dan saya bisa buatkan kategori final otomatis yang paling sesuai dengan kebutuhan monitoring Anda.

Q:

1️⃣ Formula untuk Menangkap “Status Utama” (Connected / Disconnected / Failed / Online dsb.) ==> OK, ide bagus!

2️⃣ Formula untuk Menangkap “Nama Interface” ==> OK, sepertinya ide bagus

3️⃣ Gabungkan Jadi Kategori Sederhana ==> OK, bermanfaat untuk kombinasi tertentu

REKOMENDASI: MEMBUAT VERSI FORMULA ALL‑IN‑ONE (langsung 1 sel) ==> OK, hanya perlu penyempurnaan khususnya yang teks panjang , ide keren!

BONUS (Cara Lebih Modern – Excel 365 Text Functions) ==> ok juga, hanya saja perlu trik menghindari #NA contoh: teks sumber: Cellular failed to read SIM A, reloading SIM hasil rumus: =TEXTBEFORE(A17;"(") hasil: #NA

untuk metode formula : =IFERROR(TEXTAFTER(A2;"connected";1);"") perlu penyempurnaan saja.

semua rumus-rumus tidak ada lagi sintaks error, terimakasih saya mau: ✅ dibuatkan tabel kategorisasi otomatis ✅ dibuatkan PowerQuery untuk mengelompokkan ratusan ribu log ✅ disusun Pivot summary event (berapa kali disconnect, dll.) ✅ dibuatkan versi formula yang lebih pintar (regex Excel 365)

hehehe

A: Mantap, Kevin! 🎯 Kita kerjakan empat deliverable sekaligus — tanpa sintaks error dan cocok untuk Excel 365 region Indonesia (titik koma “;”). Saya sertakan dua pendekatan: rumus lembar kerja (dynamic array) dan Power Query (skala ratusan ribu baris), plus langkah Pivot. Terakhir saya beri versi “regex” via Power Query agar paling fleksibel.

1) Tabel Kategorisasi Otomatis (berbasis pola)

Ide: buat tabel kamus pola → kategori. Lalu satu rumus di kolom hasil akan mencari pola yang cocok (wildcard) dan mengembalikan kategori.

Langkah

  1. Buat sheet baru: Kategori.
  2. Buat Table (Ctrl+T) dengan kolom:
    • Pola (berisi wildcard)
    • Kategori (hasil yang diinginkan)

Contoh isian awal (paling spesifik diletakkan di atas agar prioritas match benar):

Pola                                  | Kategori
Cellular*connected*                   | Cellular Connected
Cellular*disconnected*               | Cellular Disconnected
Cellular*failed*                     | Cellular Failed
DC-*-*connected*                     | DC Connected
DC-*-*disconnected*                  | DC Disconnected
DC-*-*failed*                        | DC Failed
*Initiated TLSv1.3 connection*       | TLS Initiated
Device is online*                    | Device Online
Device is offline*                   | Device Offline
Started up*                          | Started
Time synchronization successful*     | Time Sync OK
Time synchronization fail*           | Time Sync Fail
Remote Web Admin initiated*          | Remote Admin
Radio selected channel*              | Radio Channel Selected
WAN VSAT*connected*                  | WAN VSAT Connected
WAN VSAT*disconnected*               | WAN VSAT Disconnected
WAN_GSM*connected*                   | WAN GSM Connected
WAN_GSM*disconnected*                | WAN GSM Disconnected
Wi-Fi AP*                            | Wi-Fi AP
*                                      | Other

Catatan:

  • Gunakan wildcard * agar fleksibel terhadap IP, build number, dsb.
  • Anda bisa memperkaya daftar ini kapan saja tanpa mengubah rumus.

Rumus kategorisasi (letakkan di B2 bila teks sumber di A2)

=LET(
    teks; A2;
    pola; Kategori[Pola];
    kat;  Kategori[Kategori];
    cocok; ISNUMBER(SEARCH(pola; teks));
    idx;  XMATCH(TRUE; cocok; 0);
    IFERROR(INDEX(kat; idx); "Other")
)

Kelebihan:

  • Satu rumus untuk semua baris.
  • Tinggal edit tabel kamus jika ada pola baru (tidak perlu utak-atik rumus).

2) Formula All‑in‑One yang lebih tangguh (tanpa tabel)

Kita buat rumus yang:

  • Ekstrak “Interface” secara cerdas (mengatasi #N/A saat tidak ada tanda kurung).
  • Tentukan “Status” dengan kamus mini di dalam rumus.
  • Hasil akhir: Interface & " " & Status.

Mengatasi kasus Anda:
Cellular failed to read SIM A, reloading SIM → Interface = Cellular, Status = Failed (bukan #N/A)

Rumus (letakkan di B2; copy ke bawah):

=LET(
  t; A2;
  // ---- Ekstrak Interface ----
  // base: potong di "(" atau ":" atau "," jika ada; jika tidak ada, ambil seluruh teks
  base; IFERROR(TEXTBEFORE(t;"("); IFERROR(TEXTBEFORE(t;":"); IFERROR(TEXTBEFORE(t;","); t)));
  baseTrim; TRIM(base);
  firstWord; TEXTBEFORE(t;" ");
  iface;
    IF(LEFT(baseTrim;3)="TO ";
        MID(baseTrim;4;999);
    IF(LEFT(baseTrim;3)="WAN";
        baseTrim;
    IF(LEFT(baseTrim;3)="DC-";
        baseTrim;
    IF(firstWord="Cellular";
        "Cellular";
    IF(firstWord="WAN_GSM";
        "WAN_GSM";
    IF(firstWord="Device";
        "Device";
    IF(firstWord="Radio";
        "Radio";
    IF(firstWord="Wi-Fi";
        "Wi-Fi AP";
        firstWord
    ))))))));

  // ---- Tentukan Status ----
  lc; LOWER(t);
  status;
    IF(ISNUMBER(SEARCH("connected";lc)); "Connected";
    IF(ISNUMBER(SEARCH("disconnected";lc)); "Disconnected";
    IF(ISNUMBER(SEARCH("failed";lc)); "Failed";
    IF(ISNUMBER(SEARCH("offline";lc)); "Offline";
    IF(ISNUMBER(SEARCH("online";lc)); "Online";
    IF(ISNUMBER(SEARCH("initiated tls";lc)); "TLS Initiated";
    IF(ISNUMBER(SEARCH("time synchronization successful";lc)); "Time Sync OK";
    IF(ISNUMBER(SEARCH("time synchronization fail";lc)); "Time Sync Fail";
    IF(ISNUMBER(SEARCH("remote web admin initiated";lc)); "Remote Admin";
    IF(ISNUMBER(SEARCH("started up";lc)); "Started";
    IF(ISNUMBER(SEARCH("selected channel";lc)); "Channel Selected";
    IF(ISNUMBER(SEARCH("normal mode";lc)); "Normal Mode";
    "Other")))))))))));

  // ---- Output ----
  TRIM(iface & " " & status)
)

Tips penyesuaian cepat:

  • Tambah/ubah kata kunci status di blok status; IF(... sesuai kebutuhan Anda.
  • Log “TO DC‑JAKARTA: …” → prefix “TO ” otomatis dihapus.

3) Power Query: Mengelompokkan ratusan ribu log (cepat & rapi)

A. Langkah Import & Transform

  1. Seleksi kolom log → Data > From Table/Range (kolom A menjadi sebuah Table, beri nama kolom Teks).
  2. Power Query terbuka. Pilih Home > Advanced Editor, ganti dengan skrip M di bawah (pilih salah satu: Non‑Regex atau Regex).

Opsi A — Non‑Regex (paling kompatibel)

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Renamed = Table.RenameColumns(Source,{{"Column1","Teks"}}),

  // Fungsi bantu: ekstrak interface
  AddInterface = Table.AddColumn(Renamed, "Interface", each
    let
      t = [Teks],
      base = if Text.Contains(t, "(") then Text.BeforeDelimiter(t, "(")
             else if Text.Contains(t, ":") then Text.BeforeDelimiter(t, ":")
             else if Text.Contains(t, ",") then Text.BeforeDelimiter(t, ",")
             else t,
      baseTrim = Text.Trim(base),
      first = if Text.Contains(t, " ") then Text.BeforeDelimiter(t, " ") else t,
      iface =
        if Text.StartsWith(baseTrim, "TO ") then Text.Range(baseTrim, 3)
        else if Text.StartsWith(baseTrim, "WAN") then baseTrim
        else if Text.StartsWith(baseTrim, "DC-") then baseTrim
        else if Text.StartsWith(first, "Cellular") then "Cellular"
        else if Text.StartsWith(first, "WAN_GSM") then "WAN_GSM"
        else if Text.StartsWith(first, "Device") then "Device"
        else if Text.StartsWith(first, "Radio") then "Radio"
        else if Text.StartsWith(first, "Wi-Fi") then "Wi-Fi AP"
        else first
    in Text.Trim(iface), type text
  ),

  // Status via kata kunci
  AddStatus = Table.AddColumn(AddInterface, "Status", each
    let
      lc = Text.Lower([Teks])
    in
      if Text.Contains(lc, "connected") then "Connected" else
      if Text.Contains(lc, "disconnected") then "Disconnected" else
      if Text.Contains(lc, "failed") then "Failed" else
      if Text.Contains(lc, "offline") then "Offline" else
      if Text.Contains(lc, "online") then "Online" else
      if Text.Contains(lc, "initiated tls") then "TLS Initiated" else
      if Text.Contains(lc, "time synchronization successful") then "Time Sync OK" else
      if Text.Contains(lc, "time synchronization fail") then "Time Sync Fail" else
      if Text.Contains(lc, "remote web admin initiated") then "Remote Admin" else
      if Text.Contains(lc, "started up") then "Started" else
      if Text.Contains(lc, "selected channel") then "Channel Selected" else
      if Text.Contains(lc, "normal mode") then "Normal Mode" else
      "Other"
    , type text
  ),

  // (Opsional) bersihkan IP di dalam tanda kurung agar lebih rapi:
  // Hapus apapun di dalam tanda kurung
  CleanText = Table.AddColumn(AddStatus, "TeksBersih", each
    let t = [Teks] in
      if Text.Contains(t, "(") and Text.Contains(t, ")")
      then Text.Trim(Text.BeforeDelimiter(t, "(") & Text.AfterDelimiter(t, ")", {0}))
      else t
  , type text),

  // Ringkasan (group)
  Grouped = Table.Group(AddStatus, {"Interface","Status"}, {{"Jumlah", each Table.RowCount(_), Int64.Type}})
in
  Grouped

Opsi B — Regex (jika tersedia di build Excel Anda)

Beberapa build Excel 365 modern sudah mendukung fungsi M Text.RegexReplace/Text.RegexMatch di Power Query. Jika tidak tersedia, gunakan Opsi A.

let
  Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
  Renamed = Table.RenameColumns(Source,{{"Column1","Teks"}}),

  // Bersihkan IP/catatan dalam "(" ")" via Regex: hapus "(...)" apapun
  StripParen = Table.TransformColumns(Renamed, {
      {"Teks", each Text.RegexReplace(_, "\([^)]*\)", ""), type text}
  }),

  // Interface = sebelum ":" atau sisa teks pertama
  AddInterface = Table.AddColumn(StripParen, "Interface", each
    let
      t = Text.Trim([Teks]),
      iface =
        if Text.Contains(t, ":") then Text.Trim(Text.BeforeDelimiter(t, ":"))
        else if Text.Contains(t, " ") then
              let first = Text.BeforeDelimiter(t, " ")
              in if first="WAN" then Text.Trim(t) // karena "WAN VSAT-1 ..." tetap ambil head
                 else if Text.StartsWith(t, "DC-") then t
                 else if Text.StartsWith(t, "TO DC-") then Text.Range(t,3, Text.Length(t)-3) // buang "TO "
                 else first
        else t
    in iface, type text
  ),

  AddStatus = Table.AddColumn(AddInterface, "Status", each
    let lc = Text.Lower([Teks]) in
      if Text.RegexMatch(lc, "\bconnected\b") then "Connected" else
      if Text.RegexMatch(lc, "\bdisconnected\b") then "Disconnected" else
      if Text.RegexMatch(lc, "\bfailed\b") then "Failed" else
      if Text.RegexMatch(lc, "\boffline\b") then "Offline" else
      if Text.RegexMatch(lc, "\bonline\b") then "Online" else
      if Text.RegexMatch(lc, "initiated\s+tls") then "TLS Initiated" else
      if Text.RegexMatch(lc, "time synchronization successful") then "Time Sync OK" else
      if Text.RegexMatch(lc, "time synchronization fail") then "Time Sync Fail" else
      if Text.RegexMatch(lc, "remote web admin initiated") then "Remote Admin" else
      if Text.RegexMatch(lc, "started up") then "Started" else
      if Text.RegexMatch(lc, "selected channel") then "Channel Selected" else
      if Text.RegexMatch(lc, "normal mode") then "Normal Mode" else
      "Other"
  , type text),

  Grouped = Table.Group(AddStatus, {"Interface","Status"}, {{"Jumlah", each Table.RowCount(_), Int64.Type}})
in
  Grouped

B. Keluaran

  • Query Grouped menghasilkan tabel ringkasan: Interface | Status | Jumlah.
  • Klik Close & Load: bisa langsung ke sheet atau ke Data Model (disarankan untuk volume besar).

C. Tips Kinerja (ratusan ribu baris)

  • Load to Data Model (Connection Only) lalu analisis dengan Pivot → hemat memori sheet.
  • Hindari langkah berulang; gabungkan transformasi dalam sedikit step.
  • Matikan Automatically detect data types saat import (mengurangi overhead).
  • Jika sumber file besar (CSV/Log), gunakan From Text/CSV langsung ke PQ.

4) Pivot Summary Event (berapa kali disconnect, dll.)

Dari hasil kategorisasi (baik dari rumus atau PQ):

Pivot dari Rumus (sheet biasa):

  1. Pastikan kolom hasil berisi Interface, Status (boleh dari rumus LET di atas).
  2. Insert > PivotTable → pilih range → New Worksheet.
  3. Seret:
    • Rows: Interface
    • Columns: Status
    • Values: Count of Status (atau Count of Teks)
  4. Aktifkan Report Layout > Show in Tabular Form agar rapi.
  5. Tambahkan Filter untuk waktu/lokasi kalau Anda menambah kolom tanggal/cluster.

Pivot dari Power Query (Data Model):

  1. Di PQ, Close & Load To… > Only Create Connection + Add to Data Model.
  2. Insert > PivotTable > Use this workbook’s Data Model.
  3. Susun field sama seperti di atas.

5) “Regex Excel 365” — Klarifikasi & Solusi

  • Worksheet Excel belum punya fungsi REGEX native.
  • Power Query (M) di beberapa build Excel 365 sudah punya Text.RegexReplace / Text.RegexMatch. Jika tidak ada, pakai Opsi Non‑Regex.
  • Alternatif regex lain: Office Scripts (TypeScript) atau Power Automate — bila diinginkan, saya bisa siapkan skripnya.

6) Bonus: Rumus anti #N/A untuk kasus tanpa tanda kurung

Jika Anda ingin tetap pakai rumus sederhana untuk interface saja:

=LET(
  t; A2;
  IFERROR(TEXTBEFORE(t;"(");
    IFERROR(TEXTBEFORE(t;":");
      IFERROR(TEXTBEFORE(t;",");
        IFERROR(TEXTBEFORE(t;" "); t)
))))

Contoh tadi:
Cellular failed to read SIM A, reloading SIM → berhenti di spasi pertama jika tidak ada (, :, , → hasil Cellular.

7) End-to-End Contoh Minimal (Rumus saja)

  • C2 (Interface):
=LET(
  t; A2;
  base; IFERROR(TEXTBEFORE(t;"("); IFERROR(TEXTBEFORE(t;":"); IFERROR(TEXTBEFORE(t;","); t)));
  baseTrim; TRIM(base);
  first; TEXTBEFORE(t;" ");
  iface;
    IF(LEFT(baseTrim;3)="TO "; MID(baseTrim;4;999);
    IF(LEFT(baseTrim;3)="WAN"; baseTrim;
    IF(LEFT(baseTrim;3)="DC-"; baseTrim;
    IF(first="Cellular"; "Cellular";
    IF(first="WAN_GSM"; "WAN_GSM";
    IF(first="Device"; "Device";
    IF(first="Radio"; "Radio";
    IF(first="Wi-Fi"; "Wi-Fi AP"; first))))))));
  TRIM(iface)
)
  • D2 (Status):
=LET(
  lc; LOWER(A2);
  IF(ISNUMBER(SEARCH("connected";lc)); "Connected";
  IF(ISNUMBER(SEARCH("disconnected";lc)); "Disconnected";
  IF(ISNUMBER(SEARCH("failed";lc)); "Failed";
  IF(ISNUMBER(SEARCH("offline";lc)); "Offline";
  IF(ISNUMBER(SEARCH("online";lc)); "Online";
  IF(ISNUMBER(SEARCH("initiated tls";lc)); "TLS Initiated";
  IF(ISNUMBER(SEARCH("time synchronization successful";lc)); "Time Sync OK";
  IF(ISNUMBER(SEARCH("time synchronization fail";lc)); "Time Sync Fail";
  IF(ISNUMBER(SEARCH("remote web admin initiated";lc)); "Remote Admin";
  IF(ISNUMBER(SEARCH("started up";lc)); "Started";
  IF(ISNUMBER(SEARCH("selected channel";lc)); "Channel Selected";
  IF(ISNUMBER(SEARCH("normal mode";lc)); "Normal Mode"; "Other"))))))))))))
  • E2 (Kategori gabungan):
=C2 & " " & D2

8) Mau saya kirimkan workbook contoh siap pakai?

Saya bisa siapkan file:

  • Sheet Log (data mentah)
  • Sheet Kategori (kamus pola)
  • Sheet Hasil (rumus dinamis + validasi)
  • Query PQ_Grouped (ringkasan besar)
  • Pivot Summary (hitungan disconnect/connected/failed per interface)

Cukup kirimkan 10–20 log tambahan yang menurut Anda khas, dan (opsional) daftar kategori yang Anda ingin prioritaskan. Saya akan sesuaikan kamus pola + rumus supaya akurat dengan pola BMKG Anda.

Kalau ada preferensi lain (mis. mau Time Window per jam/hari, atau per lokasi), sebutkan ya—biar sekalian saya tambahkan kolom parsing timestamp & filter di PQ/Pivot.

=== Q: untuk: Rumus kategorisasi (letakkan di B2 bila teks sumber di A2) hasil eksekusi, timbul kesalahan, lihat lampiran: yaitu pada:

=LET(teks; A2; pola; Kategori[Pola]; kat; Kategori[Kategori]; cocok; ISNUMBER(SEARCH(pola; teks)); idx; XMATCH(TRUE; cocok; 0); IFERROR(INDEX(kat; idx); "Other"))

khusus ini, bisa di koreksi dulu ?

A: Siap, Kevin—masalahnya bukan di LET‑nya, melainkan pada referensi terstruktur ke tabel. Di screenshot terlihat Kategori[Pola] “Invalid” karena Excel mengira “Kategori” itu nama sheet, bukan nama Table.

Agar rumus jalan mulus dan wildcard * bisa dipakai seperti yang kita rencanakan, ikuti ini:

✅ Pastikan Tabel Kamus Benar

  1. Seleksi range kamus (dua kolom: Pola dan Kategori) → Ctrl + T → centang My table has headers.
  2. Pada Table Design > Table Name, ubah nama tabel menjadi: tblKategori. > Penting: Beda dengan nama sheet. Anda boleh beri nama sheet “Kategori”, tapi Table‑nya harus bernama tblKategori.
  3. Header kolom pastikan tepat:
    • Kolom 1: Pola
    • Kolom 2: Kategori

Contoh isian tblKategori (paling spesifik di atas):

Pola                                  | Kategori
Cellular*connected*                   | Cellular Connected
Cellular*disconnected*                | Cellular Disconnected
Cellular*failed*                      | Cellular Failed
DC-*connected*                        | DC Connected
DC-*disconnected*                     | DC Disconnected
DC-*failed*                           | DC Failed
*Initiated TLSv1.3 connection*        | TLS Initiated
Device is online*                     | Device Online
Device is offline*                    | Device Offline
Started up*                           | Started
Time synchronization successful*      | Time Sync OK
Time synchronization fail*            | Time Sync Fail
Remote Web Admin initiated*           | Remote Admin
Radio selected channel*               | Radio Channel Selected
WAN VSAT*connected*                   | WAN VSAT Connected
WAN VSAT*disconnected*                | WAN VSAT Disconnected
WAN_GSM*connected*                    | WAN GSM Connected
WAN_GSM*disconnected*                 | WAN GSM Disconnected
Wi-Fi AP*                             | Wi-Fi AP
*                                     | Other

Catatan wildcard *: SEARCH() tidak memahami * sebagai wildcard. Karena itu, di rumus bawah ini saya buat trik agar pola yang berisi * tetap berfungsi: pola dipecah menjadi token‑token (dipisah *) lalu dicek semua token muncul di teks.

✅ Rumus Kategorisasi (B2) — Diperbaiki & Lebih Pintar (mendukung *)

Letakkan di B2 (teks sumber di A2), regional Indonesia (pakai ;), butuh Excel 365 (fungsi modern):

=LET(
    teks; A2;
    pola; tblKategori[Pola];
    kat;  tblKategori[Kategori];

    /* cocok per baris pola:
       - Pecah pola di tanda * -> token
       - Abaikan token kosong
       - Semua token harus ditemukan di 'teks'
    */
    cocok; MAP(
        pola;
        LAMBDA(p;
            LET(
                token; TEXTSPLIT(p;"*";;TRUE);
                AND( ISNUMBER(SEARCH(token; teks)) )
            )
        )
    );

    idx; XMATCH(TRUE; cocok; 0);
    IFERROR( INDEX(kat; idx); "Other")
)

Yang diperbaiki dibanding rumus Anda:

  • tblKategori[Pola] dan tblKategori[Kategori] (bukan Kategori[...]) → mengacu ke Table, bukan sheet.
  • Menangani wildcard * dengan TEXTSPLIT → SEARCH untuk semua token → AND(...).
  • MAP + LAMBDA mengevaluasi logika cocok untuk setiap baris pola sekaligus.
  • IFERROR mengembalikan "Other" kalau tidak ada yang cocok.

Kalau Excel Anda belum punya MAP/LAMBDA, beri tahu ya — saya sediakan versi pengganti memakai BYROW atau solusi Power Query saja.

🔎 Uji Cepat Kasus “tanpa kurung” (contoh Anda)

  • Cellular failed to read SIM A, reloading SIM
    Pola Cellular*failed* → token = { "Cellular"; "failed" } → dua‑duanya ditemukan → hasil “Cellular Failed” (tanpa #N/A).

🧩 Kenapa tadi muncul “Invalid” di Function Arguments dialog?

  • Dialog itu menunjukkan bahwa Kategori[Pola] tidak dikenali sebagai structured reference yang valid.
  • Penyebab umum:
    1. Anda belum membuat Table (baru berupa range biasa).
    2. Nama yang dipakai adalah nama sheet, bukan nama Table.
    3. Nama Table berisi spasi/karakter khusus → gunakan tblKategori (alfa‑numeric, tanpa spasi).

✅ Tips Tambahan

  • Prioritas kecocokan: urutkan pola dari paling spesifik ke paling umum (mis. Cellular*connected* di atas *connected*).
  • Kinerja: Untuk puluhan‑ratusan ribu baris, Power Query akan lebih cepat & stabil. Rumus di atas cocok untuk “di lembar” dan fleksibel, tapi PQ menang untuk skala besar.
  • Debug: Buat kolom bantu sementara yang menampilkan cocok untuk memantau pola mana yang match (gunakan TAKE, DROP, dsb.).

Kalau sudah oke, saya lanjutkan:

  • versi Power Query (Non‑Regex & Regex) yang langsung keluarkan Interface | Status | Jumlah;
  • template Pivot;
  • dan kalau Anda mau, saya siapkan workbook contoh sehingga tinggal paste log → Refresh → jadi dashboard.

Ping saya kalau MAP/LAMBDA belum tersedia di build Excel 365 Anda—saya akan kirim varian tanpa MAP.

Q: saya fokus dulu ke: Rumus kategorisasi (letakkan di B2 bila teks sumber di A2)

saya sudah membuat sheet "kategori" dengan nama tabel kamus dan berisi kolom Pola dan kategori, seperti terlampir.

Rumus saya masih bermasalah, yaitu: poin Rumus kategorisasi (letakkan di B2 bila teks sumber di A2) tidak bisa/error

A: Siap, Kevin. Dari screenshot saya lihat 2 hal yang paling sering bikin rumus “kategorisasi” gagal:

  1. Referensi ke Table
    Anda menamai Table sebagai kamus (bukan tblKategori). Artinya, di rumus harus pakai kamus[Pola] dan kamus[Kategori].

  2. Baris “header ganda” masuk sebagai data
    Di tangkapan layar, saya lihat ada baris data yang isinya persis Pola | Kategori di dalam tabel (bukan header). Baris itu akan dianggap pola nyata dan bisa bikin hasil salah/#N/A.
    ➡️ Hapus baris “Pola | Kategori” yang berada di dalam tabel (biarkan header asli Table saja).

Selain itu, pola seperti DC-*connected* sudah benar, tetapi pastikan tidak ada spasi atau tanda kutip tersembunyi. Untuk jaga‑jaga, nanti saya beri formula uji.

✅ Rumus “Kategorisasi” yang sudah dikoreksi (region Indonesia)

Letakkan di B2 (teks sumber di A2). Rumus ini mendukung wildcard * di kolom Pola, dan menggunakan nama Table Anda: kamus.
Rumus ini tidak memakai MAP (untuk kompatibilitas), cukup fungsi yang ada di Excel 365 standar.

=LET(
    teks; A2;
    pola; kamus[Pola];
    kat;  kamus[Kategori];

    /* Buat vektor TRUE/FALSE untuk setiap baris pola:
       - Pecah pola di tanda * menjadi token
       - Abaikan token kosong
       - Semua token harus ditemukan di 'teks'
    */
    cocok;
      BYROW(
        pola;
        LAMBDA(p;
          LET(
            token; TEXTSPLIT(p;"*";;TRUE);          /* pecah jadi token */
            AND( ISNUMBER(SEARCH(token; teks)) )     /* semua token musti ketemu */
          )
        )
      );

    idx; XMATCH(TRUE; cocok; 0);                     /* cari pola pertama yang cocok (prioritas atas) */

    IFERROR( INDEX(kat; idx); "Other")
)

Kenapa sekarang aman?

  • kamus[Pola] dan kamus[Kategori] mengacu ke Table bernama kamus.
  • BYROW menjalankan logika cocok per baris pola.
  • TEXTSPLIT(...;"*") memecah pola menjadi token; AND(ISNUMBER(SEARCH(token;teks))) memastikan semua token ditemukan (fungsi “wildcard” kita).
  • XMATCH(TRUE; …) ambil kecocokan pertama sesuai urutan tabel (jadi taruh pola paling spesifik di atas).

Penting: urutkan pola dari paling spesifik → paling umum (baris paling atas diprioritaskan).

🧰 Rumus bantu untuk diagnosis cepat (opsional)

Jika masih error, tempel dulu rumus ini di kolom sementara (mis. C2) untuk melihat pola mana yang match:

=LET(
  teks; A2;
  pola; kamus[Pola];
  cocok;
    BYROW(
      pola;
      LAMBDA(p;
        LET(token; TEXTSPLIT(p;"*";;TRUE); AND(ISNUMBER(SEARCH(token;teks))))
      )
    );
  TEXTJOIN(", "; TRUE; IF(cocok; "MATCH"; ""))
)

Jika hasilnya kosong, artinya tidak ada pola yang match untuk A2 → cek lagi baris pola Anda.

🔎 Cek hal‑hal ini jika masih muncul error

  1. Nama Table tepat “kamus”
    • Klik salah satu sel di tabel → Table Design > Table Name → harus kamus (tanpa spasi).
  2. Header tepat “Pola” dan “Kategori” (huruf besar/kecil bebas, yang penting sama dengan yang dirujuk).
  3. Tidak ada baris “Pola | Kategori” di dalam data (itu harus dihapus).
  4. Tidak ada karakter tak terlihat
    • Di beberapa entri terlihat “DC‑\~connected*” (kalau ada), pastikan benar‑benar hanya DC-*connected* (strip - biasa, tidak ada karakter lain).
  5. Urutan pola
    • Contoh: taruh Cellular*connected* di atas *connected* (jika Anda menambahkan pola general).
  6. Build Excel 365
    • Rumus di atas memakai LET, BYROW, LAMBDA, TEXTSPLIT — semuanya ada di Excel 365 terbaru. Jika BYROW belum ada, beri tahu saya—saya siapkan varian pengganti.

🧪 Contoh uji (harus sukses)

  • Cellular (Priority 1) connected to Telkomsel Flash (10.8.120.84) in SIM A
    ➜ Cellular Connected (match Cellular*connected*)
  • Cellular failed to read SIM A, reloading SIM
    ➜ Cellular Failed (match Cellular*failed*)
  • DC-BALI (AGREGABLI-MAST01, ...) disconnected from DC-BALI ...
    ➜ DC Disconnected (match DC-*disconnected*)
  • TO DC-JAKARTA: Initiated TLSv1.3 connection to ...
    ➜ TLS Initiated (match *Initiated TLSv1.3 connection*)

Kalau sudah berhasil, saya lanjutkan dengan:

  • Power Query yang langsung menghasilkan Interface | Status | Jumlah,
  • Pivot template,
  • dan varian regex (kalau build PQ Anda mendukung Text.RegexMatch).

Kalau masih error, kirim 1–2 baris contoh “A2” yang gagal + screenshot ringkas nama Table (bagian Table Design). Saya koreksi sampai mulus.

Q: Baiklah, untuk: ✅ Rumus “Kategorisasi” yang sudah dikoreksi (region Indonesia): komentar memang harus dihapus, baru berfungsi.

formula saat ini:

=LET(teks; A2; pola; kamus[Pola]; kat; kamus[Kategori];cocok; BYROW(pola; LAMBDA(p; LET(token; TEXTSPLIT(p;"*";;TRUE);AND(ISNUMBER(SEARCH(token; teks))))));

idx; XMATCH(TRUE; cocok; 0);IFERROR(INDEX(kat; idx); "Other"))

contoh: 1. sumber data (A2) = Cellular (Priority 1) connected to Telkomsel Flash (10.8.120.84) in SIM A hasil:

Cellular Connected

2. sumber data (A67)= WAN VSAT 1 (Priority 1) disconnected (WAN failed PING test) hasil: WAN VSAT Connected

dan seterusnya. Jadi nama tabel tetap "kamus" hasil percobaan saya, mantap!

Saya akan lanjut diskusi sebelumnya ( ada poin hingga 7 di atas ), poin berikutnya, sementara sudah OK

A: Keren, Kevin — senang dengar rumus Anda sudah jalan dan “kamus”‑nya mantap. 🎯
Soal kasus WAN VSAT 1 … disconnected tapi hasil “WAN VSAT Connected”, penyebabnya jelas:

Kata “connected” adalah substring dari “disconnected”, sehingga pola *connected* ikut “nyangkut” ketika kita pakai SEARCH().

Ada 2 solusi yang sama‑sama valid. Anda bisa pilih yang paling praktis.

Opsi A (paling cepat): Prioritaskan “disconnected” di atas “connected”

Di tabel kamus, pastikan baris:

WAN VSAT*disconnected*

diletakkan di atas:

WAN VSAT*connected*

Hal yang sama untuk kategori lain:

  • *disconnected* di atas *connected*
  • *failed* di atas *online* (kalau ada ambiguitas)
  • dst.

Karena rumus kita mengambil kecocokan pertama (XMATCH(TRUE; …)), prioritas baris akan menentukan hasilnya.

Ini biasanya cukup untuk menyelesaikan kasus “connected vs disconnected”.

Opsi B (lebih kokoh): Cocokkan sebagai “kata utuh” (hindari substring)

Jika Anda ingin anti‑nyasar ke substring (mis. ‘connected’ di dalam ‘disconnected’), pakai versi rumus yang menormalkan teks menjadi token ber-spasi, lalu mencari token dengan spasi di kiri‑kanan (simulasi word boundary). Tetap region Indonesia (pakai ;):

Ganti rumus B2 Anda dengan versi ini:

=LET(
    // --- Normalisasi teks sumber jadi "teksPad" ---
    t0; LOWER(A2);
    t1; SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(t0;"(";" ");")";" ");",";" ");":";" ");CHAR(10);" ");
    // kompres spasi ganda jadi tunggal
    t2; TRIM(TEXTJOIN(" "; TRUE; TEXTSPLIT(t1;" ")));
    teksPad; " " & t2 & " ";

    pola; kamus[Pola];
    kat;  kamus[Kategori];

    // --- Evaluasi kecocokan per baris pola ---
    cocok;
      BYROW(
        pola;
        LAMBDA(p;
          LET(
            pLow; LOWER(p);
            token; TEXTSPLIT(pLow;"*";;TRUE);                // pecah pola di tanda *
            // semua token harus muncul sebagai "kata" → cari " token "
            hit; ISNUMBER(SEARCH(" " & token & " "; teksPad));
            AND(hit)
          )
        )
      );

    idx; XMATCH(TRUE; cocok; 0);
    IFERROR(INDEX(kat; idx); "Other")
)

Kenapa ini menyelesaikan kasus Anda?

  • Kita mem‑padding teks menjadi " ... connected ... " sehingga " connected " tidak akan cocok dengan "disconnected" (karena tidak punya spasi di kiri/kanan).
  • Token lain (mis. tlsv1.3, remote, failed) tetap bisa dicocokkan normal.
  • Tetap support wildcard * (karena token dipisah dari * dan dicek satu‑per‑satu).

Catatan: jika ada token yang memang seharusnya bukan kata penuh (mis. Anda ingin cari substring), biarkan pola tetap seperti semula, karena kita hanya menambahkan spasi saat mencocokkan.

Rekomendasi saya

  1. Segera rapikan urutan baris kamus (Opsi A) — ini cepat.
  2. Setelah itu, upgrade rumus ke Opsi B agar makin kebal terhadap overlap substring.

Kalau mau, kirim 3–5 contoh tambahan yang “tricky” (mis. gabungan kata mirip), saya validasi kamus + rumus sampai semua kasus pass ✔️

Siap lanjut ke poin berikutnya kapan saja!