SEO adalah rutinitas. Terkadang Anda harus melakukan operasi yang sangat suram seperti menghapus "nilai tambah" dalam kata kunci. Terkadang ini sesuatu yang lebih canggih, seperti parsing tag meta atau konsolidasi data dari tabel yang berbeda. Bagaimanapun, semua ini memakan banyak waktu.
Tapi kami tidak suka rutin. Kami menawarkan 16 fitur Google Lembar berguna yang akan menyederhanakan bekerja dengan data dan membantu Anda membebaskan beberapa jam kerja atau bahkan berhari-hari. (Tentu, Anda tidak tahu tentang keberadaan beberapa fungsi).
1. JIKA - fungsi logika dasar
Ini adalah salah satu fitur dasar yang Anda kenal dari Excel. Ini membantu dalam memecahkan berbagai masalah SEO. Rumus IF mencetak satu nilai jika ekspresi logis itu benar, dan yang lain jika itu salah.
Sintaks:
=IF(_;"_";"_")
Sebuah contoh Ada daftar kunci dengan frekuensi. Tujuan kami adalah untuk menduduki TOP-3. Pada saat yang sama, kami hanya ingin memilih kunci semacam itu, yang masing-masing akan membawa kami setidaknya 300 pengunjung per bulan.
Kami menentukan berapa banyak lalu lintas yang jatuh ke posisi ketiga di sektor organik. Untuk melakukan ini, kita pergi ke layanan ini dan melihat bahwa posisi ketiga menghasilkan sekitar 10% dari lalu lintas organik (tentu saja, angka ini tidak akurat, tetapi ini lebih baik daripada tidak sama sekali).
Kami membuat ekspresi IF yang akan mengembalikan nilai 1 untuk kunci, yang akan menghasilkan setidaknya 300 pengunjung, dan 0 untuk kunci yang tersisa:
=IF(B2*0.1>=300;"1";"0")
Harap perhatikan bahwa pada baris 7 rumus menghasilkan kesalahan, karena nilai frekuensi diatur dalam format yang salah. Untuk situasi seperti itu, ada versi lanjutan dari fungsi IF - IFERROR.
Harap dicatat: penggunaan koma atau titik untuk pecahan desimal dalam rumus didefinisikan dalam pengaturan tabel Anda.
2. IFERROR - tetapkan nilai Anda jika terjadi kesalahan
Fungsi ini memungkinkan Anda untuk menampilkan setpoint dalam sel jika terjadi kesalahan.
Sintaks:
=IFERROR( ;" ")
Kami menggunakan fungsi ini dalam contoh yang dijelaskan di atas. Tetapkan nilai jika terjadi kesalahan "tidak ada data".
Seperti yang Anda lihat, nilainya #VALUE! mengubah tampilan menjadi dimengerti "tidak ada data".
3. ARRAYFORMULA - regangkan formula ke bawah dalam satu klik
Dalam bekerja dengan data, hampir setiap kali Anda harus menulis rumus untuk semua sel dalam kolom. "Tarik" dengan menahan tombol kiri mouse, atau salin-tempel - ini adalah abad terakhir.
Cukup dengan membungkus fungsi asli dalam fungsi ARRAYFORMULA, dan rumus akan berlaku untuk semua sel di bawah ini. Selain itu, jika Anda menghapus menambahkan baris, rumus akan tetap berfungsi - tanpa spasi dalam perhitungan.
Sintaks:
=ARRAYFORMULA( )
Sebuah contoh Kami akan secara otomatis menerapkan rumus yang dijelaskan di atas untuk semua sel dalam rentang. Untuk melakukan ini, kami menyimpulkan rumus asli di ARRAYFORMULA:
=ARRAYFORMULA(IFERROR(IF(B2:B*0,1>=300;"1";"0");" "))
Harap perhatikan bahwa alih-alih sel B2 kami menentukan rentang yang kami gunakan rumusnya (B2: B adalah seluruh kolom B, mulai dari baris kedua). Jika Anda menentukan satu sel, rumusnya tidak akan berfungsi.
Retas seumur hidup. Tekan CTRL + SHIFT + ENTER setelah memasukkan formula utama, dan fungsi ARRAYFORMULA akan diterapkan secara otomatis.
ARRAYFORMULA tidak bekerja dengan semua fungsi. Misalnya, itu tidak kompatibel dengan GOOGLETRANSLATE dan IMPORTXML, yang akan kita bahas di bawah ini.
4. LEN - menghitung jumlah karakter dalam sel
Fitur ini sangat berguna saat membuat iklan kontekstual - ketika penting untuk tidak melangkahi jumlah karakter yang dialokasikan untuk pos, deskripsi, URL tayangan, tautan cepat, dan penyempurnaan.
Dalam SEO, fungsi LEN digunakan, misalnya, ketika menyusun tag meta judul dan deskripsi. Fungsi simbol diperhitungkan dengan spasi.
Sintaks:
=LEN( )
Sebuah contoh Kita perlu membuat judul untuk semua halaman situs. Kita tahu bahwa sekitar 55 karakter ditampilkan di hasil pencarian. Tugas kami adalah menulis judul sehingga informasi yang paling penting adalah di 55 karakter pertama. Kami menulis rumus LEN untuk sel yang diisi. Sekarang kita tahu pasti kapan kita mendekati 55 karakter yang ditampilkan.
5. TRIM - menghapus spasi di awal dan akhir frasa
Ketika Anda mengurai semantik dari sumber yang berbeda, seringkali berisi elemen "sampah" - spasi, plus, karakter khusus. Pertimbangkan fitur yang membantu membersihkan kernel dengan cepat. Salah satunya adalah TRIM.
Fungsi ini menghilangkan spasi di awal dan akhir frasa yang ditentukan dalam sel.
Sintaks:
=TRIM(, )
Fungsi menghapus semua spasi sebelum dan sesudah frasa - tidak peduli berapa banyak ada.
6. SUBSTITUTE - ubah / hapus spasi dan karakter khusus
Fungsi universal untuk mengganti / menghapus karakter dalam sel.
Sintaks:
=SUBSTITUTE( ;" ";" "; )
Nomor korespondensi - nomor seri dari nilai met untuk mengganti, misalnya, ganti nilai met pertama, biarkan sisanya. Parameter opsional.
Sebuah contoh Kami memiliki bongkar frasa kunci dari Yandex.Wordstat. Banyak kunci berisi nilai tambah. Kami harus menghapusnya.
Rumusnya akan seperti:
=SUBSTITUTE(B12;"+";"";)
Apa yang telah kami lakukan:
- tempat mencari - menunjukkan sel dengan data;
- "Apa yang harus dicari" - menunjukkan tanda plus yang perlu dihapus;
- “Apa yang harus diubah” - karena karakter perlu dihapus, kami menunjukkan tanda kutip tanpa karakter di dalamnya; jika kami membutuhkan pengganti, di sini kami akan menulis teks yang kami butuhkan untuk mengganti tanda tambah;
- nomor kecocokan - di sini kami tidak menentukan apa pun, dan fungsinya akan menghapus semua plus dalam frasa; jika kita tentukan 1, maka fungsinya hanya akan menghapus tanda tambah pertama, jika 2 - yang kedua, dll.
7. RENDAH - menerjemahkan huruf dari huruf besar ke huruf kecil
Saat mengompilasi kunci dan parsing dari sumber yang berbeda (misalnya, dari meta tag pesaing), mungkin ternyata mereka memiliki huruf besar. Untuk membawa kunci dalam bentuk terpadu, Anda harus menerjemahkan semua huruf dalam huruf kecil. Untuk melakukan ini, gunakan fungsi RENDAH.
Sintaks:
=LOWER(, )
8. UNIK - mengeluarkan data tanpa sel duplikat
Fungsi ini menganalisis rentang yang dipilih untuk pengambilan penuh dan hanya menampilkan garis unik - dalam urutan yang sama seperti pada rentang asli.
Sintaks:
=UNIQUE( )
Sebuah contoh Kami mengumpulkan kunci dari Yandex.Wordstat, kiat pencarian, menguraikan kata-kata pesaing. Secara alami, dalam array kunci ini kita akan memiliki duplikat. Kami tidak membutuhkannya. Kami menghapusnya dengan UNIK.
Jika Anda ingin membersihkan kernel sampah dalam satu gerakan, gunakan normalizer kata gratis. Ini menghapus frasa duplikat (tunduk pada permutasi), mengubah kasus, menghilangkan spasi dan karakter khusus. Intinya, ia melakukan hal yang sama dengan fungsi TRIM, SUBSTITUTE, RENDAH, dan UNIK - hanya dalam satu klik.
9. CARI - cari data di baris
Dengan fungsi ini, Anda akan dengan cepat menemukan baris yang Anda butuhkan dengan sejumlah besar data.
Sintaks:
=SEARCH(« »; )
Fungsi ini digunakan dalam situasi yang berbeda:
- sorot frasa kunci dengan maksud yang diperlukan (misalnya, bermerek atau terkait dengan topik, produk, atau layanan tertentu);
- Temukan karakter tertentu di URL (misalnya, parameter UTM atau tanda tanya)
- temukan URL untuk keperluan pembuatan tautan - misalnya, yang mengandung kata "posting tamu").
Sebuah contoh Kami memiliki daftar kunci untuk toko pintu online. Kami ingin menemukan semua kueri bermerek dan menandainya di tabel. Untuk melakukan ini, gunakan rumus:
=SEARCH("porta";A1)
Tetapi dalam bentuk ini, rumus dengan tidak adanya kata "porta" pada kunci akan menunjukkan kepada kita #VALUE! .. Selain itu, jika kata ini ada di sel yang diinginkan, fungsi akan mencatat jumlah karakter yang digunakan untuk memulai kata ini. Hasilnya terlihat seperti ini:
Untuk mendapatkan hasil pencarian dalam bentuk yang nyaman bagi kami, kami juga menggunakan fungsi IF dan IFERROR:
=IFERROR(IF(SEARCH("porta";A1)>0;"";"0"))
10. SPLIT - memecah frase menjadi kata-kata yang terpisah
Fungsi membagi string menjadi fragmen menggunakan pembatas yang ditentukan.
Sintaks:
=SPLIT(;"")
Harus diingat bahwa bagian kedua dari teks yang dibagi akan menempati kolom berikutnya. Jadi, jika Anda memiliki tabel ketat, Anda perlu menambahkan kolom kosong sebelum menerapkan rumus.
Sebuah contoh Kami memiliki daftar domain. Kita perlu membaginya menjadi nama dan ekstensi domain. Dalam fungsi SPLIT, tentukan titik sebagai pemisah dan dapatkan hasilnya:
11. CONCATENATE - menggabungkan data dalam sel
Fungsi ini, tidak seperti yang sebelumnya, menggabungkan data dari beberapa sel.
Sintaks:
=CONCATENATE( 1; 2;...)
Penting: tidak hanya nilai-nilai sel, tetapi juga karakter (dalam tanda kutip) dapat dimasukkan dalam rumus.
Sebuah contoh Dalam contoh dengan fungsi SPLIT, kami membagi domain. Mari kita lakukan operasi terbalik menggunakan CONCATENATE (tentukan sel yang akan digabung dan tentukan pemisah - periode di antara mereka):
12. VLOOKUP - mencari nilai dalam rentang data yang berbeda
Fungsi mencari kunci di kolom pertama rentang dan mengembalikan nilai sel yang ditentukan di baris yang ditemukan.
Sintaks:
=VLOOKUP(;;_;[])
Contoh 1. Ada dua array frase kunci yang diperoleh dari sumber yang berbeda. Anda perlu menemukan kunci di array pertama yang tidak ditemukan di array kedua. Untuk melakukan ini, gunakan rumus:
=VLOOKUP(A2:A;B2:B;1;false)
Apa yang telah kami lakukan:
- atur rentang A2: A, dari mana kita mengambil kunci untuk perbandingan;
- atur rentang B2: B, yang digunakan untuk membandingkan kunci dari kolom A;
- atur nomor kolom (1), dari mana kami mengencangkan kunci untuk pertandingan;
- false - menunjukkan bahwa kita tidak perlu menyortir.
Fungsi VLOOKUP sering digunakan ketika mencari data pada lembar yang berbeda atau dalam dokumen yang berbeda.
Contoh 2. Kami mengunduh data dari Yandex.Webmaster dan Google Search Console tentang mengindeks halaman di situs. Tugas kami adalah membandingkan data dan menentukan halaman mana yang diindeks di satu mesin pencari, tetapi tidak diindeks di yang lain.
Kami mengunggah hasil unggahan ke file Google Sheets. Di satu lembar adalah URL dari Google, yang kedua dari Yandex.
Di sel C2, kita menulis fungsi VLOOKUP. Segera simpulkan dalam fungsi di ARRAYFORMULA untuk menarik secara otomatis:
=ARRAYFORMULA(VLOOKUP(A2:A;Yandex!A2:A;1;false))
Sekarang kita segera melihat halaman mana yang diindeks di Google, tetapi tidak diindeks di Yandex.
Apa yang telah kami lakukan:
- atur rentang A2: A dari lembar saat ini, dari mana kami mengambil nilai untuk perbandingan;
- atur rentang Yandex! A2: Lembar dengan pembongkaran dari Yandex, yang dengannya kami akan membandingkan nilai URL dari Google;
- menunjukkan nomor kolom lembaran dengan pembongkaran dari Yandex, nilai-nilai dari mana kami menarik ketika nilai-nilai dari rentang yang dibandingkan bertepatan;
- false - menunjukkan bahwa kita tidak perlu menyortir.
Jika Anda perlu memeriksa pengindeksan halaman tertentu secara bersamaan di Yandex dan Google, gunakan alat dari PromoPult. Unduh daftar URL dan jalankan cek. Jika halaman diindeks di mesin pencari, kolom akan memiliki nomor 1, jika tidak - 0.
Cara menggunakan alat ini dan dalam situasi apa itu berguna, baca dalam panduan ini .
13. IMPORTRANGE - mengimpor data dari tabel lain
Fungsi ini memungkinkan Anda untuk memasukkan data dari tabel lain ke file saat ini.
Sintaks:
=IMPORTRANGE(" ";" ")
Contoh:
=IMPORTRANGE("https://docs.google.com/spreadsheets/d//"," !A2:A25")
Sebuah contoh Anda mempromosikan situs klien. Tiga spesialis sedang mengerjakan proyek: pembuat tautan, spesialis SEO, dan copywriter. Semua orang membuat laporan. Klien tertarik untuk memantau proses online. Anda membuat satu laporan untuk itu dengan tab: "Tautan", "Posisi", "Teks". Data di setiap arah ditarik ke tab ini menggunakan fungsi IMPORTRANGE.
Keuntungan dari fungsi ini adalah Anda hanya membuka akses ke sheet tertentu. Pada saat yang sama, bagian internal dari laporan spesialis tetap tidak dapat diakses oleh pelanggan.
14. IMPORTXML - parse data dari halaman web
Fungsi menyebar untuk mem-parsing data dari halaman web menggunakan XPath.
Sintaks:
=IMPORTXML("url";"xpath-")
Berikut ini beberapa kegunaan untuk fitur ini:
- ekstrak metadata dari daftar URL (judul, deskripsi), serta header h1-h6;
- mengumpulkan email dari halaman;
- parsing alamat halaman di jejaring sosial.
Sebuah contoh Kami perlu mengumpulkan konten dari tag judul untuk daftar URL. Kueri XPath yang kami gunakan untuk mendapatkan tajuk ini terlihat seperti ini: "// title".
Rumusnya adalah:
=IMPORTXML(A2;"//title")
IMPORTXML tidak berfungsi dengan ARRAYFORMULA, jadi salin rumus secara manual ke semua sel.
Berikut adalah pertanyaan XPath lain yang mungkin berguna bagi Anda:
- bongkar tajuk H1 (dan dengan analogi - h2-h6): // h1
- uraian tag meta parse: // meta [@ name = 'description'] / @ konten
- parse meta tag keywords: // meta [@ name = 'keywords'] / @ content
- ekstrak alamat email: // a [berisi ( href , 'mailTo:') atau berisi ( href , 'mailto:')] / @ href
- ekstrak tautan ke profil di jejaring sosial: // a [berisi ( href , 'vk.com/') atau mengandung ( href , 'twitter.com/') atau berisi ( href , 'facebook.com/') atau berisi ( href , 'instagram.com/') atau mengandung ( href , 'youtube.com/'))/@href
Jika Anda perlu mengetahui permintaan XPath untuk elemen halaman lainnya, buka di Google Chrome, buka mode melihat kode, temukan elemen, klik kanan padanya dan klik Copy / Copy XPath.
15. GOOGLETRANSLATE - menerjemahkan kata kunci dan data lainnya
Dalam proyek multibahasa, Anda sering harus menerjemahkan frasa kunci. Ini paling mudah dilakukan menggunakan fungsi GOOGLETRANSLATE langsung di tabel.
Sintaks:
=GOOGLETRANSLATE(«»; [_]; [_])
Misalnya, jika kita perlu menerjemahkan kunci dari bahasa Rusia ke bahasa Inggris, rumusnya adalah:
=GOOGLETRANSLATE(A1;"ru";"en")
Jika kami menerjemahkan dari bahasa Inggris ke bahasa Rusia, maka kami perlu mengubah urutan bahasa:
=GOOGLETRANSLATE(A1;"en";"ru")
GOOGLETRANSLATE tidak berfungsi dengan ARRAYFORMULA, jadi, seperti dalam kasus IMPORTXML, kami meregangkan rumus secara manual.
16. REGEXEXTRACT - ekstrak teks yang diinginkan dari sel
Fungsi ini memungkinkan Anda untuk mengekstrak teks yang dijelaskan dengan menggunakan RE2 ekspresi reguler yang didukung oleh Google dari string data. Sintaks ekspresi reguler cukup rumit, Anda dapat menemukan lebih banyak contoh di Bantuan Google .
Sintaks:
=REGEXEXTRACT( ;” ”)
Contoh 1. Kami memiliki daftar URL. Perlu mengekstrak domain. Di sini ungkapan reguler akan membantu kami:
^(?:https?:\/\/)?(?:[^@\n]+@)?(?:www\.)?([^:\/\n]+)
Contoh 2. Dalam daftar frasa kunci, Anda perlu menemukan kunci bermerek dengan kata "porta" dan "port". Untuk mencari frasa yang mengandung kata-kata ini, gunakan ungkapan reguler:
(?i)(\W|^)(porta|)(\W|$)
Seperti yang Anda lihat, dalam tabel Anda bisa memotong dan memotong data sesuai kebutuhan, cukup pahami rumusnya.