28 Nisan 2020 Salı

ÇAPRAZARA / XLOOKUP Fonksiyonu


ÇAPRAZARA (XLOOKUP) Fonksiyonu

DÜŞEYARA (VLOOKUP) çok sık kullanılan bir fonksiyon şüphesiz. Ama artık onun yerine daha gelişmiş yeteneklere sahip ÇAPRAZARA (XLOOKUP) fonksiyonu çıktı. Microsoft 365 / Office 365 ile kullanabileceğiniz ÇAPRAZARA fonksiyonu (İngilizce sürümlerde XLOOKUP) şu durumlarda kullanılıyor:
1. Normal bir düşeyarama gibi değerin karşılığını verilerin olduğu diğer bir tablodan getirmeyi (eşleştirmek) sağlar. Varsayım olarak tam karşılığını getirmek üzere düzenlenmiş (arama parametresi 0 olarak).
2. Aynı anda çok sayıda sütunda veri getirmek. Örneğin hem adını (fç) hem de (görevini) getirmek
3. Bulamadığında istenilen değeri ya da yazıyı hücreye girmek / EĞERHATA (IFERROR) gibi. Bulunamadığında
4. Bir değerler tablosundaki aralıklara eşleştirmesi yapmak. Bu aralıkta üst ya da alt değeri getirmek. DÜŞEYARA (VLOOKUP) fonksiyonunun 1 arama parametresiyle kullanımı gibi.
5. İki boyutlu düşeyarama yapmak. DÜŞEYARA ve KAÇINCI bileşimi (VLOOKUP ve MATCH) bileşimine gerek kalmadan tek seferde iki boyutlu aramayı diğer bir deyişle dikey ve yatay aramayı birlikte yapmak. Ayrıca İNDİS ve KAÇINCI, İngilizce kullanımlardaki INDEX ve MATCH yerine de geçebilecektir. Hem fç’nin adını bulurken hem de aylar sütunlarından istenilen ay değerini getirebilecektir.
6. Verilen iki hücre aralığı arasındaki değerleri getirmek ve toplamak. Belli bir koddan diğerine kadar olanları istenilen sütunlardaki toplamları.
7. Baştan sona ya da sondan başa doğru arama. Düşey arama işleminde listenin başından başlanıp sonuna doğru aranıyor ve ilk eşleşen geliyordu. Şimdi bunun baştan ya da sondan başlanacağını belirtebiliyorsunuz.

Detaylar:
=ÇAPRAZARA (aranan değer, tablo dizisi, dönüş dizisi, [bulunmadağında döndürülecek değer], [eşleştirme türü], [arama şekli])
=XLOOKUP (lookup, lookup_array, return_array, [not_found], [match_mode], [search_mode])
Argümanları:
aranan değer: Aranılacak değer.
tablo dizisi: Aranacak alan (sütun).
dönüş dizisi: Döndürülecek alan (sütun)
bulunamadığında: (seçimlik) Bulunamadığında döndürülecek değer ya da mesaj.
eşleştirme modu: (seçimlik)  0 tam karşılığı, -1 tam karşılığı yoksa en yakın küçük değer, 1 tam karşılığı yoksa en yakın büyük değer, 2 joker değer (*,?) eşleştirme.
arama şekli: (seçimlik) 1 ilk değerden başlama, -1 son değerden başlama, 2 iki arama artan, -2 ikili arama azalan. İkili arama (binary search) tekniği ile sıralama yaparak arama yapar (sıralı bir listede).
1) ürün adını getirmek (normal kullanım)
2) Birden birden çok sütunu getirmek
3) Bulunamadığında
4) Aralıktaki küçük değeri
5) aralıktaki büyük değeri
6) Dikey ve yatay arama birlikte (iki boyutlu)
7) İKi aralık arasındaki değerlerin toplanması
8) Joker karakterlerin kullanımı * ? ve tilda
9) Baştan sonra ya da sondan başa



Videolar:


Video oynatma listesi:


24 Mart 2020 Salı

Excel’de Bilmemiz Gereken Teknikler


Excel’de Bilmemiz Gereken Teknikler
Videolar:
Excel’de teknikler, her biri bir işlemi yerine getirmek için kullanılan bir işlem – araç ve fonksiyon kullanımlarını gösterir. Günlük hayatında Excel’i sıkça kullanan kişiler için hazırladığım bu numaralar size zaman kazandıracak pratik çözümlerdir, ipuçları, tips, hints olarak da adlandırılır.
Tekniklerde birçok konu ele alınacaktır. Bunlar kısayol tuşu, formül geliştirme, biçimleme, koşullu biçimlendirme, bir fonksiyon kullanımı, filtreleme, pivot tablo vb. birçok konu içerebilir. Fonksiyon kullanımlarında fonksiyonların Türkçe ve İngilizce sürümdeki karşılıkları kullanılmaktadır. Örnek olarak DÜŞEYARA (VLOOKUP), İNDİS (INDEX), KAÇINCI (MATCH) gibi fonksiyonlar…
Tekniklerden bazıları:
Çift Tıklama ile Yapılan 15 Şey, Hızlı Doldurma (Flash Fill), Tarih Serileri, Sayı Serileri, Özel Liste Kullanmak, Çiftleri Bulmak / Saymak / Kaldırmak, Form Aracı, Sesli Okumak, Özel (Custom) Biçimlemeler, Kümülatif Toplam, Emoji kullanmak, Gülen Yüzler kullanmak, Boşlukları Kaldırmak, Üç Boyutlu Formül Kullanımı, Çift Tıklama, Köprüler (Hyperlinks), Filtrelenen Verilerin Toplanması, Ay ve Gün Adları, Tablo Yapısı, Dosyaya Parola Koymak, Sayfaya Kilitlemek, Hücrede Kaç Kelime Var, Büyük Küçük Harf Değiştirmek, Tabloyu Terse Çevirmek, Hızlı Grafik, Hafta Sayısını Bulmak, İş Gününü Bulmak, İstenilen Ay Kadar İleriye, İstenilen Hafta Kadar Geriye, Koşullu Simgeler, Metinleri Ayırmak / Birleştirmek, Gelişmiş Filtreleme, Filtrelemede * ve ? Kullanmak, Tarihi metin ya da sayı ile Birleştirme, Tarih Verisini Ayırma ve Birleştirme, Tüm satırı renklendirme, Verileri Gizlemek, CTRL+ENTER, ALT+ENTER, Pivot Tablo ile Yeni Sayfalar Oluşturmak, Kamera Aracı, Hüceden Filtreleme, Şekilli Açıklamalar, F5 Tuşu, F3 Tuşu, Dış Bağlantıları Yönetmek, Veri Doğrulama , Liste Kutuları, İlişkili Liste Kutuları, Araya Tire Ekleme, Sayıların Önündeki Sıfırları Göstermek / Kaldırmak, Formülleri Göstermek, Hedef Ara (Goal Seek), Nesneleri Bulmak, Ağırlıklı Ortalama, CTRL+M, CTRL+SHIFT+L, Günün Tarihi, Bağlantı Yapıştırmak, Gruplandırma (Outline), Birleştirme (Consolidate), Dilimleme (Slice), Mini Grafik, Veri Çubukları (Data Bars), İnternet'den veri almak, Senaryo Yöneticisi,  KAYDIR (OFFSET) ile formülleri farklı yönlerde sürükleme, Onay Kutusu, Numaratör Kullanımı (Spin Button), En Düşük Fiyatı Veren Firmayı Bulmak, Alttoplam (Subtotal) Alma -Renkli ve boşluklu, Belli Sayıdan Büyük Olanları Toplamak , Radyo Düğmesi , Formül Araçları, BUL (FIND) - İçinde Geçen Kelime, ETARİHLİ - DATEDIF, Hedefli Grafik, Sayları Yuvarlama, Sayfayı Dosya Yapma, Sayfayı Bölme, Bölmeleri Dondurma, SATIR ve SÜTUNA Göre Düşeyara (Vlookup), Eğer Yerine Düşeyara (Vlookup), F9 Tuşu, Özel Yapıştırma (Paste Special), Kilitli Adresler ($), CTRL+Z ve CTRL+Y, Sayıları ve Dolu Hücreleri Sayma, Sıralama Seçenekleri, Renge Göre Filtreleme, Renge Göre Sıralama, & İle Birleştirme, DOLAYLI (INDIRECT) Fonksiyonu, Rastgele Sayı Üretmek, Satır ve Sütun Seçme - Ekleme /Silme, EĞERSAY ile yapabilecekleriniz, Metin Verideki İlk Sayıyı Bulmak, Hesaplanmış Alanlar, Düşeyara-Kaçıncı / Vlookup-Match, Grafik Şablonları Oluşturmak, Sütun başlıklarını korumak, Sayıları biçimlemek, Bağlantıları Yönetmek, dinamik alanların kullanımı, hücre içinde resimlerin kullanımı, Günü geçenleri renklendirme …
Microsoft Excel, Microsoft şirketinin tescilli markasıdır. Video’lara anlatılanlar yazarın kendi deneyim ve bilgi birikimleri temelinde geliştirdiği teknik ve kullanımlardır.


27 Şubat 2020 Perşembe

Excel'de veri temizleme (data cleansing)


Veri temizleme (data cleansing)

Microsoft Excel ® ile yaptığımız işlemlerinden önemli bir kısmını veri temizleme oluşturuyor. Veri temizleme ERP ya da başka veri kaynaklarından aldığımız verilerin temizlenmesi.
Bu işlemler; boşlukların kaldırılması, araya ekleme yapılması, tarihlerin düzenlenmesi, sayısal verilerin düzenlenmesi, verilerin farklı şekillerde biçimlenmesi (formatlanması), eşleştirme yapılması, birleştirilmesi, ayrılması, çiftlerinin kaldırılması vs. Bu işlemler için çok sayıda fonksiyon ve araca sahip.
VİDEO:

Veri Temizlemek İçin Kullanılan Fonksiyonlar / Araçlar
Türkçe (İngilizce) Sürümler için:

1) Temizleme: KIRP (TRIM), TEMİZ (CLEAN), YERİNEKOY (SUBSTITUTE)
2) Çiftleri Kaldırma
3) Değiştirme: BUL (FIND), BULM (SEARCH), DEĞİŞTİR (REPLACE), YERİNEKOY (SUBSTITUE),
4) Ayırma: SOLDAN (LEFT), SAĞDAN (RIGHT), PARÇAAL (MID), UZUNLUK (LEN)
5) Büyük küçük harf değiştirme
6) Düzenleme: METNEÇEVİR (TEXT), SAYIDÜZENLE (FIXED), SAYIDEĞERİ (VALUE)
7) Birleştirme: BİRLEŞTİR (CONCATENATE)
8) TRANSPOSE / TABLOYU ÇEVİRME
9) EŞLEŞTİRME - DÜŞEYARA (VLOOKUP), KAÇINCI (MATCH), İNDİS (NDEX)
10) Metni Sütuna Çevirme (Text to columns)
11) Tarih Düzenlemeleri: Ayır, birleştir, parçala
12) CTRL+H





8 Şubat 2020 Cumartesi

Excelde işgünün bulmak ? Ayın, haftanın ilk işgünü bulmak.


Excelde sık karşılaşılan sorunlardan birisi de tarihle ilgili işlemlerdir.
Excelde yılın ilk iş günü nasıl bulunur?
 Excelde ayın ilk iş günü nasıl bulunur? 
Excelde haftanın ilk iş günü nasıl bulunur? İlgili fonksiyonlar (Türkçe ve İngilizce Excel ) olarak:

HAFTANINGÜNÜ  (WEEKDAY)
İŞGÜNÜ (WORKDAY)
SERİAY (EOMONTH)
TARİH (DATE)
HAFTANINGÜNÜ  (WEEKDAY) - HAFTANIN KAÇINCI GÜNÜ
İŞGÜNÜ (WORKDAY) - İŞGÜNÜNÜ BULUR
SERİAY (EOMONTH) - AYIN SON GÜNÜ
TARİH (DATE) - GÜN, AY, YIL DEĞERLERİNDEN TARİHİ VERİSİNİ OLUŞTURUR.
Www.farurukcubukcu.com

Tarih ve saat fonksiyonları, özellikle tarih ve saat türündeki verileri işlemek üzere geliştirilmiştir.
Hafta, gün ve saat hesaplamaları da Excel’de sık karşılaşılan uygulamalardır. Bu videoda tarih fonksiyonların HAFTANINGÜNÜ (WEEKDAY) fonksiyonu kullanılarak çözüm geliştirilmektedir.
Türkçesi               İngilizcesi             Açıklama
AY          MONTH               Bir tarih seri numarasını (tarih bilgisi) ay değerine çevirir.
BUGÜN                TODAY  Bugünün tarihini seri numarasına çevirir.
DAKIKA                MINUTE               Bir tarih seri numarasını dakikaya çevirir.
ETARİHLİ             DATEDIF              İki tarih arasındaki süreyi gün, hafta, ay ve yıl cinsinden bulur.
GÜN      DAY        Seri tarih numarasını güne çevirir.
GÜN360              DAYS360              Yılı 360 gün olarak hesaplar.
HAFTANINGÜNÜ              WEEKDAY            Bir tarih seri numarasını haftanın gününe çevirir.
HAFTASAY           WEEKNUM          Haftanın sayısını (1-53 arasında) verir.
İŞGÜNÜ               WORKDAY           Belirtilen gün sayısı sonrasında ulaşılan çalışma gününü hesaplar.
SAAT     HOUR    Bir tarih seri numarasını saate çevirir.
SANİYE                 SECOND               Bir tarih seri numarasını saniyeye çevirir.
SERİTARİH           EDATE   Belirtilen sayı kadar, sonraki ayı bulur.
SARİAY  EOMONTH          Ayın son gününü verir.
ŞIMDİ    NOW     Geçerli tarih ve saati verir.
TAMİŞGÜNÜ      NETWORKDAYS İki tarih arasındaki tam çalışma günlerini hesaplar.
TARİH   DATE     Belirli bir tarihin seri numarasını verir. Tarih değerini oluşturur. Yıl, ay ve gün olarak
TARİHSAYISI        DATEVALUE        Metin biçimindeki bir tarihin seri numarasını verir. Tarihe çevirir.
YIL          YEAR      Bir tarih seri numarasını yıla çevirir.
TARİHFARKI        YEARFRAC           İki tarih arasındaki yıl sayısını verir.
ZAMAN                TIME      Belirli bir zamanın seri numarasını verir
ZAMANDEĞERİ   TIMEVALUE        Metin tarih değerini sayısal değere dönüştürür.


6 Şubat 2020 Perşembe

Çok sayıda Excel dosyası üzerine Pivot tablo nasıl yapılır?


Çok sayıda Excel dosyası üzerine Pivot tablo nasıl yapılır.  
Çok sayıda Excel dosyasını bir araya getirerek pivot tablo oluşturmak.


Pivot tablo ile ne yapılır. Pivot tablo ile veri tablosunda belirtilen sütunlar temelinde gruplama ve toplama işlemleri yapılır. Gruplama temelinde belirtilen alanlar toplanabilir (sum), sayılabilir (count) ya da ortalaması (average) alınabilir. Pivot tablo üzerinde filtreleme yapmak, grafik çizmek çok kolay bir şekilde yapılır. Kurumsal ortamda yaygın olarak kullanılan pivot tabloların oluşturulması ve kullanım şekilleri bu videoda yer almaktadır.

Veri Modeli (Data Model), Excel 2013 ile gelen ve çok sayıda dosyanın / tablonun bir araya getirilebilmesini, aralarından ilişki kurulmasını ve pivot tablonun bu veri modeli üzerine – içindeki tablolar üzerine kurulmasını sağlar.

Bu konuda bilinmesi gereken şeyler:

İlişkiler bire-bir (one to one) ve bire-çok (one to many) olmak üzere iki şekilde oluşturulabilir. Bire-çok ilişkide bir tarafındaki tablonun Birincil (Primary) sütunu diğer tablonun (çok tarafındaki) Yabancı (Foreign) sütununa bağlanır.

Bire-çok ilişki bir tablonun (sorgunun) tek bir kaydı diğer bir tablonun (sorgunun) birden çok kaydıyla eşleşmesini belirtir. Örneğin bir müşterinin çok sayıda hareketi (satış olayı) vardır. Aynı şekilde bir ürünün hareketleri arasındaki ilişki sağlanmış olur.

Veri modeli / data model ve Power BI vidyolarımı izlerseniz bu işin mantığına ilişkin temel bilgileri öğrenebilirsiniz.


Excel’de neler bilinmeli?


Microsoft Excel® ’de neler bilinmeli?




İşe başvurdunuz ya da bir kurumda çalışıyor ve Excel kullanıyorsunuz. Ama ne kadar biliyorsunuz?

  • ·       Excel ortamında temel sayfa (sheet) yapısı – sayfa oluşturma, kopyalama, silme, bulma, vb.
  • ·       Temel formül geliştirme (dört işlem, yüzde, karşılaştırma, ….)
  • ·       Sabit adresleme (a1     $a$1)
  • ·       Temel olarak biçimleme – sayılar, metinler, kenarlık, renklendirme, %, vb.
  • ·       Dosya kaydetme şekilleri
  • ·       Güvenlik ayarları
  • ·       Temel fonksiyonlar: EĞER (IF), DÜŞEYARA (VLOOKUP), ETOPLA (SUMIF), BÜYÜKHARF (UPPER) gibi metin, ve tarih fonksiyonları …..
    25 tane bu konuda videolarımız var.  Sık kullanılan fonksiyonlar
  • 25 tane bu konuda videolarımız var.  Sık kullanılan fonksiyonlar
  • ·       Bağlantılı Çalışma (Linkler) – Bağlantıların Yönetme
  • ·       Grafikler
  • ·       Metinlerin ayırma ve birleştirme (metni sütunlara çevirme / text to columns)
  • ·       Hesaplama (Calculation) modu – F9
  • ·       Koşullu biçimlendirme (Conditional Formatting)
  • ·       Ad (name) oluşturma
  • ·       Filtreleme
  • ·       Pivot tablo, … veri modeli, sorgular (queries) …
  • ·       Uygulamalar arasında yapıştırma – PowerPoint
  • ·       Makro kaydetme, FÇ makro1 – grafik çizer, FÇ makro2 - filtreleme
  • ·       … gereksinimlere göre, şirketlerdeki uygulamalara göre, …
  • ·       … Kullanılan Microsoft Excel sürümü, dili, vs. ….
  • ·       www.excelci.com
  • ·       www.farukcubukcu.com
  • ·       https://www.youtube.com/user/farukcubukcu


30 Ocak 2020 Perşembe

Hücre içeriğini köprü / bağlantı haline getirmek


Dinamik olarak bir bağlantı nasıl oluşturulur?
Dinamik olarak bir köprü nasıl oluşturulur?
Hücre içeriğini köprü haline getirmek? Hücre içeriğini bağlantı haline getirmek?

Bu uygulamada KÖPRÜ / HYPERLINK kullanarak hücre içeriğine tıklayarak ilgili sayfaya (sheet) gitme işlemi yer almaktadır.
=KÖPRÜ(BİRLEŞTİR("#'  ";A5;"  '!A1");A5)
=HYPERLINK(CONCATENATE("#'  ";A5;"  '!A1");A5)

=KÖPRÜ(BİRLEŞTİR("#'  ";bağlantının alınacağı hücre;"  '!A1");"açıklama")

Kurumsal Excel, Excel İş Uygulamaları, Excel’in şirketlerde kullanımı Excel’in temel amacını ifade eder. Bir şirkette temel hesaplamalar, tablolar, formlar ve raporlamaya yönelik olarak Excel’in çoğu araçları ve fonksiyonları kullanılır.