Category: Excel

Excel, Excellent application :)


Excel Makro ile Sayfa Adını Değiştirmek

April 17th, 2009 — 7:05am

Aktif sayfanın adını aşağıdaki kod ile değiştirebilirsiniz.
ActiveSheet.Name = “Sheet1”

İndex ile de sayfa adını değiştirmek mümkündür.
Sheets(1).Name = “Test”

Comments Off on Excel Makro ile Sayfa Adını Değiştirmek | Excel

Personal Xls Dosyası Nerde?

April 17th, 2009 — 6:58am

Personal.xls excel’in önemli bir dosyasıdır. Mesela bu dosyaya kaydettiğiniz makrolar bilgisayarınızdaki bütün excel dosyalarınızda çalışır. Bu dosyanın yeri işletim sistemine göre değişebilmektedir.

Xp’de
C:\Documents and Settings\Serdar\Application Data\Microsoft\Excel\XLSTART

Vista’da ve Windows 7’de
C:\Users\Serdar\AppData\Roaming\Microsoft\Excel\XLSTART

Comments Off on Personal Xls Dosyası Nerde? | Excel

Excel ile Sql Server dan Data Çekmek(veri al – import data)

January 3rd, 2009 — 1:35pm

Excel ile sqlserver‘a bağlanıp, satır ve sütun sayısı taşmıyorsa, bilgi çekebilsiniz.

import data

import data

Yapılması gerekenleri madde madde yazacak olursak şöyle,

  1. “Data > import external data > import data” komutuna basalım.
  2. Gelen diyalog kutusundan sql server connection.odc dosyasını seçip aça basalım.

    sql server connection

    sql server connection

  3. Data connection wizard açılır bu ekranda da bağlanacağınız veritabanı, kullanıcı adı ve şifreyi yazalım.

    user - password

    user - password

  4. next e basıp tabloyu seçip finish ‘e tıkladığımızda aktif olan sayfaya seçtiğiniz tablodaki data gelir.

Dilerseniz joinli bir sorgu ilede bilgi çekebilirsiniz. Edit query komutuna basarak isteğiniz sorguyu yazıp çekebilirsiniz.

edit query - refresh

edit query - refresh

Çektiğiniz data kendini güncellemez bunu ünlem şeklindeki butona basarak biz yapmalıyız.

4 comments » | Excel

Excel’de Veri Doğrulama

May 9th, 2008 — 10:53pm

Microsoft Excel de bir hücreye girilmesini istediğiniz veri türünü sınırlayabilirsiniz. Örneğin, yalnızca 1 ile 10 arasındaki sayıların girilmesine izin verebilirsiniz. Bu sınırlamayı “Veri doğrulama” ile yapabiliriz.

Excel veri doğrulama - validation

Veri doğrulama özelliğinin amacı kullanıcıların geçersiz veri girmelerini önlemektir.

Uygulamak için, uygulamak istediğimiz hücreyi veya hücreleri seçip, Veri menüsünde Veri doğrulama düğmesine basmalıyız. Komut çalıştığında karşımıza veri doğrulama penceresi gelir. “Ayarlar” sekmesinde izin verilen değerleri belirleyebiliyoruz.

İlk örnekte 1 ile 10 arasındaki tam sayılara izin verelim. “İzin verilen değer”de tam sayısı seçip. “En az” için 1 “En Fazla” için 10 girelim.

Excel veri doğrulama - validation

Tamam’a bastığımızda seçtiğimiz hücre için belirlediğimiz aralık dışında bir değer girilememesini sağlamış oluruz. Veri doğrulama ekranında 2 sekme daha var. “Girdi iletisi” ve “Hata Uyarısı”. Bu sekmelerle de neler yapabileceğimiz adlarından malum. Girdi iletisi sekmesinde o hücreyi seçtiğinizde açılacak bir uyarı yazısı, ipucu belirliyorsunuz. (tooltip gibi) Hata uyarısında da yanlış değer girildiğinde nasıl bir uyarı verelim onu belirliyorsunuz.

Excel veri doğrulama - validation

Veri doğrulamayı, başvuru formülleri ile ileri düzeyde kullanabiliriz. Örneğin yapılan bir seçime göre hücrenin içine gelecek değerleri kısıtlamak isteyebiliriz ve o değerler bir listeden gelsin isteriz. Bunu bir örnekle daha rahat açıklayabiliriz.

Elimizde alt gurupları olan bir ürün grubu var diye düşünebiliriz. Ligler ve o liglerden her birinde 3 er takım var. 9 takımdan 1 takım seçmek yerine bir üst grubu belirleyip yalnızca gerekli 3 takımı göstereceğiz.

Eğer hücrede “birinci lig” seçili ise yanındaki hücredeki doğrulama birinci lig takımlarının listesi olacak. Bu örneği hazırlamak için önce aşağıdaki tabloyu hazırlayalım.

Excel veri doğrulama - validation

Tablomuzu hazırladıktan sonra tablonun sütunlarında “Ad Tanımlama” işlemi yapacağız.

Ad tanımlama nedir kısaca bir açıklayalım. Excel’de bir hücrenin adresi vardır. Mesela A4; biz o hücreye ad tanımlama ile özel bir ad verebiliriz. Bir hücreyi “Toplam” olarak adlandırabiliriz. Ya da bir hücre aralığını; (A2:A4) “Ligler” olarak adlandırabiliriz.

Excel veri doğrulama - validation

Ad tanımlama işlemi A1 hücresinin hemen üstündeki ad kutusundan yapılabilir. Bir hücre ya da aralık seçersiniz. Ad kutusuna tıklar vermek istediğiniz adı yazar “enter” a basarsınız. Bundan sonra seçtiğiniz hücre yada hücreler o şekilde çağırılabilir. Bu şekilde =topla(Sayilar) gibi bir formül çalıştırmak mümkündür.

Bu örnekte Ad tanımlama için bir kolaylık söz konusu. Formüller menüsünde “Tanımlı Adlar” diye bir alan var. Orda seçimden oluştur komutu var. Bu komutu kullanarak, hazırladığımız tablonun sütunlarına hızlıca adlandırma yapacağız

  1. Tabloyu seç
  2. Formüller menüsünden “Seçimden oluştur” komutuna tıkla
  3. Açılan ekrandan sadece “üst satırı” seç

Şu anda sütunlar özel olarak adlandırıldı. Örneğin; A2:A4 aralığı “ligler” olarak adlandırıldı.

Şimdi tablonun biraz altında bir hücre seçelim. Bu hücrede ligler başlığının altındaki değerlerden oluşan bir liste doğrulaması yapacağız.

  1. Hücreyi seç
  2. Veri menüsünden Veri doğrulamaya tıkla
  3. İzin verilen değerde “liste” değerini seç
  4. Kaynak kısmına =ligler yaz

Şu anda seçtiğiniz hücrede bir doğrulama çalışıyor. Hücrenin yanında bir ok çıktı. O oka bastığımızda girilebilecek değerleri gösteriyor. Şimdi bir yandaki hücrede de bir doğrulama yapacağız. Bu doğrulama da seçilmiş olan değeri baz alarak o değerle
adlandırılmış aralığı doğrulama olarak kullanacak. Yani birinci_lig seçilmişse yandaki hücreye sadece bjk, fb ve gs yazılabilecek.

Excel veri doğrulama - validation

Bu işlem için DOLAYLI() adında bir formül kullanacağız. Hücrenin içindeki değeri adres olarak veri doğrulamasına vermemizi sağlayacak. Yani birinci_lig olarak adlandırdığımız aralığı veri doğrulamasına verebileceğiz.

  1. Hücreyi seç
  2. Veri menüsünden Veri doğrulamaya tıkla
  3. İzin verilen değerde “liste” değerini seç
  4. Kaynak kısmına =DOLAYLI(A7) yaz (A7 tablonun altında seçtiğiniz ilk hücrenin adresi.)

Şimdi ikinci doğrulama uyguladığımız hücre birinci ile bağımlı çalışmakta.

1 comment » | Excel

Excel 2007 de Boş Satır ve Sütunları Silen Makro

April 14th, 2008 — 8:11pm

Excel’de, eğer bir kısa yol oluşturmamışsanız saatlerce uğraşmanız gereken basit işler karşınıza çıkabilir.

Mesela aynı olan satırları silmek. Bu işlem için bir makro kullanmak şarttı. Ama 2007 de artık bu iş için bir komut veri menüsünün altına eklenmiş.

Excel Makro

Bir diğer çok sıkıcı işde boş satır ve sütunları silmektir. bir rapordan veya dış bir veri kaynağından veri alırsınız arada boş satırlar yada boş sütunlar oluşur. bunlar yüzlerce satır olabilir. Bunları bi düğmede silmek gerek. Basit bi iş ve buna mesai harcamamalı.

Bunu bir makro oluşturarak yapabiliriz. 2007 de görünüm menüsünün altında makro kaydı düğmesi var. Eskiden araçlar menüsündeydi. Şimdi görünümle daha alakalı görmüşler… Ben pek sevmedim yeni yerini.

Excel Makro

Kayıt makrosu, her düzey kullanıcının yapabileceği bir makro türüdür. Sık yaptığınız uzun işlemleri kaydederek otomatik tekrar etmesini sağlayabilirsiniz.

Mesela hazırladığınız bir raporun adımlarını kaydedersiniz. bi düğmeye basarsınız o adımları her ay siz değil excel tekrar tekrar yapar.

Excel Makro

Bir diğer yöntemde kod ile makro oluşturmaktır. Excel içerisinde bir Visual basic düzenleyicisi barındırır. Buradan her türlü programı yazabilirsiniz. Boş satır ve sütunları anla ve sil gibi bir kod mesela.


Sub DeleteBlankRowsAndColumns()

'A1 hücresinden dolu olan son hücreye kadar olan alanı seç
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Dim i As Long

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

'Tüm Satırlarda CountA(hücrenin içi dolu mu?)
'fonksiyonu 0 döndürüyorsa sil
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
Selection.Rows(i).EntireRow.Delete
End If
Next i

'Tüm Sütunlarda CountA fonksiyonu 0 döndürüyorsa sil
For i = Selection.Columns.Count To 1 Step -1
If WorksheetFunction.CountA(Selection.Columns(i)) = 0 Then
Selection.Columns(i).EntireColumn.Delete
End If
Next i

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

Bu kodu bir modüle eklemelisiniz. Alt + F11 kısa yolu ile VB düzenleyicisini açıp ekranın sol tarafındaki Project ekranında sağ tık yapıp menüden insert > module komutuna tıklayalım eklenen modüle kodu ekleyebiliriz. kaydettiğinizde artık makroları görüntüle dediğimizde “DeleteBlankRowsAndColumns” makrosuda görüntülenir.

Excel Makro

Excel dosyaları aslında bu makro sayesinde bir virüs taşıyıcısı da olabilir. Her türlü kod yazılabilir dedik. Virüs de bu her türe dahil :) tanımadığınız dosyalarda makro varsa aman açmayın. En basitinden şakacı biri size google’dan bulduğu bir kodla çok acı verebilir. Mesela bütün word dosyalarınızı silebilir!

3 comments » | Excel

C Sharp ile Excel Grafiği ve Pivot Table Oluşturmak

April 5th, 2008 — 10:21pm

Pivot table çok gelişmiş bir veri özetleme aracıdır. excelde open office calc’te yada başka bir elektronik tablolama programında eklenmiş olarak bulabilirsiniz. en genel olarak sıralama, veri sayma, veri işleme gibi özellikleri vardır.

Pivot tablolara matrix tablolarda diyebiliriz.

Excel PivotTable, C#

Bu makaledeki amacımız c# koduyla excel verisinden pivota tablosu ve grafik oluşturmak.

Excel Grafik, C#

Visual Studio 2008 ile hazırlandı.

Kodları indirmek için Tıklayınız

Örneğin Kodları

protected void Page_Load(object sender, EventArgs e)
{
//System.Globalization.CultureInfo eskiKultur = System.Threading.Thread.CurrentThread.CurrentCulture;
//System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo(“en-US”);

ChartWorks();

PivotWorks();

//System.Threading.Thread.CurrentThread.CurrentCulture = eskiKultur;

Response.Write(“İşlem Tamamlandı…”);

}

private void PivotWorks()
{
Excel.Application xlApp = new Excel.Application();

if (xlApp == null)
{
throw new Exception(“Excel Başlatılamadı. Excel Kurulu Olmaya Bilir.”);
}

xlApp.Visible = true;
xlApp.DisplayAlerts = false;

Excel.Workbook wb = xlApp.Workbooks.Open(
Server.MapPath(ConfigurationManager.AppSettings[“PivotFile”]),
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[1];

Excel.Range sourceData = ws.get_Range(“A1”, “G37”);

Excel.Worksheet ws2 = (Excel.Worksheet)wb.Sheets.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);

CreatePivotTable(wb, ws2, sourceData, “MARKA”, “TARİH”, “ADET”,
“Adet Toplamı”,
Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlSum);

wb.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
}

private void ChartWorks()
{
Excel.Application xlApp = new Excel.Application();

if (xlApp == null)
{
throw new Exception(“Excel Başlatılamadı. Excel Kurulu Olmaya Bilir.”);
}

xlApp.Visible = false;
xlApp.DisplayAlerts = false;

Excel.Workbook wb = xlApp.Workbooks.Open(
Server.MapPath(
ConfigurationManager.AppSettings[“OkunacakExcelFile”]),
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets.Add(Missing.Value,
Missing.Value, Missing.Value, Missing.Value);

Excel.Worksheet ws2 = (Excel.Worksheet)wb.Sheets[2];

Excel.Range ChartRange = (Excel.Range)ws2.get_Range(“A1”, “B15”);

CreateChart(wb, ws, ChartRange, “Grafik Nesnesi”,
Microsoft.Office.Interop.Excel.XlRowCol.xlRows,
10, 20, 700, 400);

wb.Close(true, Missing.Value, Missing.Value);
xlApp.Quit();
}

private void CreateChart(Excel.Workbook book, Excel.Worksheet sheet,
Excel.Range range, string name, Excel.XlRowCol plotby,
int top, int left, int width, int height)
{
Excel._Chart oChart;
oChart = (Excel._Chart)book.Charts.Add(Missing.Value, Missing.Value,
Missing.Value, Missing.Value);

oChart.ChartType =
Microsoft.Office.Interop.Excel.XlChartType.xlBarClustered;

oChart.SetSourceData(range, plotby);

oChart.ApplyDataLabels(
Microsoft.Office.Interop.Excel.XlDataLabelsType.xlDataLabelsShowLabel,
Missing.Value, Missing.Value, false, false, false,
true, false, false, Missing.Value);

oChart.PlotArea.Interior.Color = System.Drawing.ColorTranslator.ToOle(
System.Drawing.Color.White);
oChart.ChartArea.Shadow = true;

Excel.Axis axis =
(Excel.Axis)oChart.Axes(Excel.XlAxisType.xlValue,
Excel.XlAxisGroup.xlPrimary);

axis.TickLabels.Font.Size = 7;
axis.TickLabels.Font.Name = “Tahoma”;

Excel.Axis axis2 = (Excel.Axis)oChart.Axes(
Excel.XlAxisType.xlCategory,
Excel.XlAxisGroup.xlPrimary);

axis2.TickLabels.Font.Name = “Tahoma”;
axis2.TickLabels.Font.Size = 7;

oChart.Legend.Font.Size = 7;
oChart.Legend.Font.Name = “Tahoma”;
oChart.Legend.Position =
Microsoft.Office.Interop.Excel.XlLegendPosition.xlLegendPositionBottom;

oChart.Location(Excel.XlChartLocation.xlLocationAsObject, sheet.Name);

sheet.Shapes.Item(1).Top = top;
sheet.Shapes.Item(1).Left = left;
sheet.Shapes.Item(1).Width = width;
sheet.Shapes.Item(1).Height = height;
}

private void CreatePivotTable(Excel.Workbook workbook, Excel.Worksheet sheet,
Excel.Range range, string rowfiledname, string columnfieldname,
string valuefieldname, string valuelabel, Excel.XlConsolidationFunction function)
{
workbook.PivotCaches().Add(Excel.XlPivotTableSourceType.xlDatabase,
range).CreatePivotTable(sheet.get_Range(“A2”,
Type.Missing), “PivotTable”
+ sheet.Name, Type.Missing, Type.Missing);

Excel.PivotTable PT = (Excel.PivotTable)sheet.PivotTables(“PivotTable”
+ sheet.Name);
PT.NullString = “0”;
PT.GrandTotalName = “Toplam”;

Excel.PivotField _RowField = (Excel.PivotField)PT.PivotFields(rowfiledname);
_RowField.Orientation = Excel.XlPivotFieldOrientation.xlRowField;
_RowField.Position = 1;

Excel.PivotField _ColumnField =
(Excel.PivotField)PT.PivotFields(columnfieldname);
_ColumnField.Orientation = Excel.XlPivotFieldOrientation.xlColumnField;
_ColumnField.Position = 1;

PT.AddDataField(PT.PivotFields(valuefieldname), valuelabel, function);

Excel.Range _grpRange = (Excel.Range)sheet.Cells[3, 2];

_grpRange.Group(Missing.Value, Missing.Value, 5,
new bool[] { false, false, false, false, true, false, true });

}

4 comments » | Csharp - C#, Excel

Page 1 of 212

Back to top