Monday, November 24, 2008
Friday, August 22, 2008
Menuliskan kalimat dengan rumus di Excel
Kadang kita mau menuliskan hasil perhitungan ms excel yang kita buat dalam kalimat. Sebagai contoh kita menghitung rata-rata waktu pelayanan pelanggan dan simpangan bakunya dengan data sebagai berikut: Untuk menampilkan dalam kalimat misalnya " "Rata-rata waktu pelayanan pelanggan adalah 39.6 dengan Simpangan Baku/Standar Deviasi sebesar 6.77" Maka kita dapat menggunakan rumus sebagai berikut: ="Rata-rata waktu pelayanan pelanggan adalah "&B12&" dengan Simpangan Baku/Standar Deviasi sebesar " &TEXT(B13,"#.##") Atau langsung dihitung dalam kalimat: ="Rata-rata waktu pelayanan pelanggan adalah "&AVERAGE(B2:B11)&" dengan Simpangan Baku/Standar Deviasi sebesar " &TEXT(STDEV(B2:B11),"#.##") Selamat mencoba !!!
Menggunakan Vlookup untuk Pengganti IF
Misalnya kita menggunakan fungsi IF yang saling bertumpuk seperti contoh dibawah ini
Contoh:
=IF(C2=1,"Nama",IF(C2=2,"Alamat",IF(C2=3,"Kota",IF(C2=4,"Kode Pos","tidak tahu"))))
Kita dapat mengganti fungsi seperti di atas dengan menggunaka fungsi VLOOKUP untuk mendapatkan nilai tertentu.
Yaitu
=VLOOKUP(C2,A1:B6,2,FALSE)
1- Argument pertama fungsi VLOOKUP Function mencari data yang cocok pada kolom pertama range A1:B6 dengan nilai yang ada di sel C2.
2- Hasilnya berasal dari kolom kedua dari range A1:B6.
Tampilan Range yang dipilih
Saturday, August 9, 2008
Normal Distribution
Normal distribution can be tested for by: a) plotting the data on a graph and visually inspecting the line b) using a probability graph in which if it is possible to draw a straight line through the values distribution is normal c) applying the Chi-Square Test for normal distribution. d) checking whether the mean, mode and median coincide (which they do if distribution is normal) or e) testing that: 68% of all values will fall between -1/+1 standard deviation and
95% of all values will fall between -2/+2 standard deviations.
Wednesday, August 6, 2008
Soal Statistik Deskriptif untuk Ms Excel
No Pengamatan Waktu Kasir 1 Waktu Kasir 2 No Pengamatan Waktu Kasir 1 Waktu Kasir 2 1 9 9 16 9 18 2 12 17 17 5 8 3 19 13 18 13 12 4 12 6 19 15 9 5 5 11 20 7 6 6 23 7 21 6 13 7 13 7 22 6 4 8 14 6 23 18 19 9 13 16 24 20 17 10 13 17 25 22 11 11 7 12 26 21 5 12 11 10 27 8 19 13 8 7 28 12 11 14 9 17 29 12 17 15 5 10 30 21 8 Pertanyaan:
Soal Untuk MS Excel Solver
Tabel 2.1 Data perusahaan "Toko Mebel" Pekerjaan Jam yang dibutuhkan Jam kerja tersedia per minggu kerja Meja Kursi Tukang kayu 4 3 240 Pegecatan 2 1 100 Keuntungan per Unit (Rp.) 700.000 500.000 Pertanyaan: Berapa seharusnya produksi meja dan kursi dalam satu minggu kerja agar profit total perusahaan "Toko Mebel" maksimal? Gunakan Solver dalam aplikasi Ms Excel 2007 !!
Saturday, July 26, 2008
Simple Debt Amortization.
Incurring debt and making a series of payments to reduce this debt to nil is something we all must do in our lifetime as we make purchases that would only be feasible if we are given sufficient time to pay down the amount of the transaction. The challenge: John decides to buy a car. The dealer gives him a price and tells him he can pay on time as long as he makes 36 installments and agrees to pay six per cent interest. (6%). The facts are: Agreed price 18,000 for the car, taxes included. 3 years or 36 equal payments to pay out the debt. Interest rate of 6%.
This is referred to as 'amortizing' a debt, a term that takes it's roots from the French term ' amortir', which is the act of providing death to something.
The basic definitions required for someone to understand the concept are:
1. Principal - the initial amount of the debt, usually the price of the item purchased.
2. Interest Rate - the amount one will pay for the use of someone else's money. Usually expressed as a percentage so that this amount can be expressed for any period of time.
3. Time- essentially the amount of time that will be taken to pay down (eliminate) the debt. Usually expressed in years, but best understood as the number of and interval of payments, i.e., 36 monthly payments.
Simple interest calculation follows the formula:
I= PRT, where
I=Interest
P=Principal
R= Interest Rate
T= Time.
The first payment will occur 30 days after receiving the loan
A simple timeline will give you an idea of the question we need to address.
To simplify the problem, we know the following:
The monthly payment will include at least 1/36th of the principal so we can pay off the original debt.
The monthly payment will also include an interest component that is equal to 1/36 of the total interest.
Total interest is calculated by looking at a series of varying amounts at a fixed interest rate.
Take a look at this chart reflecting our loan scenario.
This picture shows the calculation of interest for each month, reflecting the declining balance outstanding due to the principal pay down each month . ( 1/36 of the balance outstanding at the time of the first payment.,., in our example 18,090/36 = 502.50)
By totaling the amount of interest and calculating the average, you can arrive at a simple estimation of the payment required to amortize this debt. Averaging will differ from exact because you are paying less than the actual calculated amount of interest for the early payments, which would change the amount of the outstanding balance and therefore the amount of interest calculated for the next period.
Understanding the simple effect of interest on an amount in terms of a a given time period and realizing that amortization is nothing more then a progressive summary of a series of simple monthly debt calculations should provide a person with a better understanding of loans and mortgages. The math is both simple and complex; calculating the periodic interest is simple but finding the exact periodic payment to amortize the debt is complex.
Sunday, May 18, 2008
Marine Renewable Energy
Marine Renewable Energy (source http://www.bwea.com/marine/devices.html) Devices To date there are only a few technologies that have progressed as far as the full-scale deployment and testing. However, there are several UK-based companies presently actively involved in the construction of such devices, supported in their endeavours by numerous financial channels including the Government and private investors. A number of technologies developed in the UK have been outlined below. Follow this link if you would like to see a summary of how each device works. AquaEnergy Group AquaBuOY combines elements of two prior, successfully ocean-tested, technologies – the IPS Buoy and the Swedish Hose-pump. It builds upon the proven advantages of both systems, while advancing the state-of-the-art in design efficiency, functionality and reliability. Click here to see an animation showing how the PowerBuOY works (requires Flash plug-in) For further information please visit the AquaEnergy Website: http://aquaenergygroup.com or email: aqua@aeg-ltd.com Ocean Power Technologies The primary product of OPT for the utility industry is the PowerBuoy, which is a patented, scaleable energy conversion system engineered to obtain wholesale amounts of electrical power from wave energy. Ocean trials were conducted off the coast of New Jersey from 1997 onwards to demonstrate the concept of a floating buoy capturing energy and then converting it into electricity. The technology is on a growth curve towards full-scale commercial application, the first example of which is the 50kW PowerBuoy recently installed in Hawaii, in a scheme to be scaled up to 1MW. For further information please visit the OPT Website: www.oceanpowertechnologies.com or email: mrdraper@oceanpowertech.com Pelamis, Ocean Power Delivery (OPD) The Pelamis is a semi-submerged, articulated structure composed of cylindrical sections linked by hinged joints. The wave-induced motion of these joints is resisted by hydraulic rams, which pump high-pressure oil through hydraulic motors via smoothing accumulators. The hydraulic motors drive electrical generators to produce electricity. Power from all the joints is fed down a single umbilical cable to a junction on the sea bed. Several devices can be connected together and linked to shore through a single seabed cable. Following six years of detailed design and development, OPD has completed the build of the first full-scale Pelamis Wave Energy Converter. The 750kW machine measures 120m long by 3.5m wide (about the size of four train carriages) and weighs 750 tonnes fully ballasted. In August 2004 Pelamis was connected to the UK gird at the European Marine Energy Centre (EMEC) in Orkney to be tested. This is the first offshore wave energy to be exported into the UK electricity system. Video footage of Pelamis in action (2.7 MB © OPD) For more information go to: www.oceanpd.com Marine Current Turbines (MCT) An experimental 300kW test system, called "Seaflow" (as shown above) was successfully installed off Lynmouth in Devon in May 2003 and continues to deliver vital data. This is the world's first successful offshore tidal turbine and is the first step in developing this entirely new clean method of power generation. Seaflow is unique among "wet renewable" technologies in having met its design specification in full (it has actually exceeded its rated power of 300kW and consistently exceeded its rotor design efficiency) as well as having remained operable in open sea exposed conditions for almost three years. The commercial prototype known as "Seagen" is presently under construction (above left) and due for installation in Strangford Narrows, Northern Ireland in the late summer of 2006. The design process has been validated by the leading marine classification agency, DNV (Det Norsk Veritas) with a view to developing and obtaining type approval for the MCT Seagen turbine system. The Seagen twin rotor turbines, rated at approximately 1MW, incorporate a patented system for raising the rotors and power train above the surface of the sea for ease of maintenance (as illustrated above right). It is hoped that the first commercial demonstration array as illustrated top right will be installed by the end of 2008. MCT is also exploring opportunities for demonstration projects in other parts of the world including North America and S E Asia in parallel with the first UK commercial array. MCT also has plans for more advanced "Second Generation" technology which can be scaled to higher (or lower) power levels than Seagen and applied in a greater range of flow conditions. MCT is dedicated solely to the development of commercially viable marine current turbines and has a number of commercial strategic partners and shareholders, including EDF Energy, Guernsey Electricity, BankInvest, Seacore and Bendalls Engineering. The Seaflow project was financially supported by the UK DTI (Department of Trade and Industry) and the European Commission and Seagen is also being supported by the DTI. For more information go to: www.marineturbines.com Sperboy™ With a minimum number of moving parts, which are above the surface of the sea, maintenance requirements are minimised and energy is produced at a very competitive rate. Current research is looking to extend the life of the vessel to some 50 years and includes several initiatives to deliver higher levels of power. Consequently the device has considerable potential for further reducing the cost of delivered power. The absence of large quantities of invasive products such as oils and lubricants coupled with minimal impact on seabed ecosystems makes the device environmentally friendly. SPERBOY™ has completed the Marine Energy Challenge, where independent consultants investigated its performance in terms of power capture as well as carrying out a detailed study of both capital and maintenance costings in arriving at their prediction for the cost of delivered power. The device is now ready to exploit the very successful and encouraging results of this 'Challenge' and will now proceed to the deployment of full-scale prototypes. For further information see www.sperboy.com Stingray and the EB Frond, The Engineering Business (EB) Stingray uses the flow of the tidal stream over a hydroplane to create an oscillating motion that operates hydraulic cylinders to drive a motor that, in turn, drives an electrical generator. This device is a seabed mounted machine, to be situated typically in any water depth up to 100m. The EB Frond, on the other hand, is a seabed mounted nearshore wave energy device designed for installation in 20-35 metres of water. A paddle-like collector on the end of a long lever is placed close to the sea surface and driven by the waves to produce hydraulic power similar to that of the Stingray. In September 2002 EB tested the Stingray generator offshore in the Yell Sound off the Shetland Islands in the Highlands. The 180 tonne, 150 kW machine was installed and removed safely and data collected for analysis to aid the further development of both the demonstrator and commercial units. The 3rd phase of the development programme, a reinstallation of the demonstrator in the Yell Sound, has also been completed in 2003 and included a more flexible control system to allow the performance of the generator to be accurately controlled and recorded over a longer period. The EB Frond is not at such an advanced stage of development but the phase 1 physical testing has been undertaken at scales of 1/33rd and 1/25th in the Lancaster University test tank, the academic partners in the project. Phase 2 is proposed for development in which a larger model will be deployed in more varied and extreme wave conditions. EB state that if this is successful it should lead to the design, build and installation of a full-scale demonstrator in a real near-shore wave environment, possibly as early as this year. Future plans for the Stingray include a 5MW pre-commercial Stingray farm to be connected to a local power distribution system in order to test an innovative system for smoothing the flow of electricity from the oscillating devices. For more information go to: www.engb.com TidEL, SMD Hydrovision At present SMD Hydrovision is in the process of testing a 10th scale device in a specially developed tidal facility at NaREC. The results of this testing will feed into the development of a full-scale prototype with 15 meter diameter fixed pitched blades driving the turbine powertrains that are housed within a pod. For more information go to: www.smdhydrovision.com/products Wave Dragon Wave Dragon consists of three main elements: When waves have been channelled by the reflectors, they overtop the ramp and fill the reservoir, which is situated at a higher level than the surrounding sea. This hydraulic head is used for power production through the hydro turbines. Wave Dragon is unique among wave energy converters as it harnesses the energy in the water directly via water turbines in a one-step conversion system. A very simple construction in which, importantly, the turbines are the only moving parts. Click here to see a video of Wave Dragon in operation (.mpg file, 7.3MB) For further information please visit the Wave Dragon website: www.wavedragon.net or email: info@wavedragon.net The Wave Rotor, EcoFys, Holland www.ecofys.nl/nl/expertisegebieden/product_systeemontwikkeling/waverotor.htm Wavegen The LIMPET (Land Installed Marine Powered Energy Transformer) is the smallest of Wavegen's shoreline energy modules, generating 0.5MW of power for local communities. At the end of 2000 it successfully fed electricity into the local Islay electricity network having secured a 15-year Power Purchase Agreement with the major public electricity suppliers in Scotland. More recently Wavegen has collaborated with SEV, a Faroese energy company, with the intention of bringing wave energy to the Faroe Islands. The boards of both companies have agreed to form a joint venture company to oversee the initial design and engineering phase of the project with a value of £600,000. Phase two will see the construction of the wave power station in a cliff wall using a series of Wavegen's air turbine power generation modules. This project is reported to be worth up to £7m and could provide a maximum capacity of up to 100MW. Wavegen state that this will form the blueprint for wave power stations in similar locations both in the Faroes and other parts of the world. For more information go to: www.wavegen.com Wavegen also have a test facility.
AquaEnergy Group Ltd. combines technology and project development to produce electricity from ocean energy. AquaEnergy Development UK Ltd. is a subsidiary with a mission to advance AquaEnergy's core technology, the AquaBuOY wave energy conversion device.
OPT was formed in 1994 by Dr George Taylor and Dr Joseph Burns in pursuit of their vision of harnessing the boundless energy captured within the waves of the world's oceans. The company has focused on the PowerBuoy technology, capturing the energy using large floating buoys anchored to the sea bed and converting the energy into electricity via innovative power take-off systems.
OPD was established at the beginning of 1998 by Dr Richard Yemm to develop the Pelamis Wave Energy Converter. This device is known as a 'point absorber' and can effectively focus energy from a wide area by interacting with an area of waves much larger than its physical size. As it is flexibly moored it swings head-on to the incoming waves and can therefore span successive wave crests.
Marine Current Turbines Ltd is an independent company dedicated to developing a revolutionary technology for large-scale generation of clean electricity from the seas. MCT's patented technology is a submarine tidal current turbine based on using pitch regulated axial flow rotors, which has proved to be the successful formula for the vast majority of wind turbines and which seems likely to be the definitive solution for water current kinetic energy conversion too.
SPERBOY™, developed and patented by Embley Energy, is a floating wave energy converter based on the 'oscillating water column' principle. Air displaced by the oscillating water column is passed through rectifying turbine-generators. Designed to be deployed in large arrays 8 to 12 miles off shore providing large-scale energy generation at a competitive cost.
The Engineering Business started investigating renewables as a potential third market, behind submarine telecom and offshore oil and gas, in 1997 and comments that it soon became clear that Renewable Offshore Power Generation would be the most attractive technology area given the experience and expertise of EB staff. The devices currently being developed include both tidal and wave energy technologies, dominated by the 'Stingray' tidal generator and the 'EB Frond' wave energy converter.
SMD Hydrovision has developed a device for the extraction of energy from tidal flows. The TidEL concept consists of a pair of contra-rotating 500kW turbines, mounted together on a single crossbeam. The complete assembly is buoyant and tethered to the seabed by a series of mooring chains. The mooring system allows the turbines to align themselves down stream of the prevailing tidal flow without requiring any external intervention, providing a reliable and economical way of tracking the alternating tidal flow-cycle. As the system requires no support structure, it can be fitted in any reasonable coastal water depth.
Wave Dragon marks a significant breakthrough towards commercial exploitation of the abundant energy concentrated in ocean waves, where seagoing trial of the Wave Dragon 20 kW prototype has proven its offshore survivability since March 2003 and verified the potential for commercial feasibility with large scale power generation below the costs of offshore wind power.
The Wave Rotor, a concept designed by EcoFys of Holland, is part of the Carbon Trust Marine Energy Challenge. At present a scale device has been tested but there is potential for this to develop further during the MEC process
Wavegen was established in 1992 by Professor Alan Wells and has developed into one of the key leaders in the manufacture of wave power systems. Together with Queens University, Belfast (QUB), Wavegen has built the world's first commercial scale wave energy converter, LIMPET, on the coast of Islay in the Highlands of Scotland. They also develop the OSPREY, a near shore Oscillating Water Column (OWC) and are working on a number of similar concept designs.
Saturday, May 17, 2008
Memasukkan Baris atau Kolom dengan Keyboard
Memasukkan Baris atau Kolom Baru - dng Keyboard
Insert a New Row or Column - Excel Keyboard Shortcut
Untuk memasukkan baris atau kolom baru dengan keyboard, tanpa mouse maka dua langkah dibawah ini dapat diterapkan- Shift+SpaceBar = Pilih baris (row)
- Ctrl+Shift+PlusSign = Insert Baris (row)
Sunday, May 11, 2008
Menggunakan Tabel Pivot untuk Meringkas Data
Misalnya anda memiliki data penjualan yang isinya 50.000 baris seperti terlihat pada Gambar 1 Dengan data itu anda ingin membuat laporan ringkas yang menunjukkan penjualan per wilayah dan produk (region and product). Gambar 1. Ringkas tabel data berikut. Caranya: Untuk Peringatan: Pivot tables di Excel 2007 sangat berubah dari versi Excel 97-2003. Untuk membuat ringkasan pendapatan berdasarkan wilayah dan produk membutuhkan klik mouse sebanyak 4 kali dan satu kali tahan tarik mouse (drag): Pastikan bahwa data yang ada dalam daftar memiliki judul (pada baris paling atas) yang berbeda satu dengan lainnya (unik). Pilih salah satu sel dalam database (data anda). Pilih Menu Insert – Pivot Table. IntelliSense Excel (sensor cerdas Excel) akan menduga range data anda. Dan biasanya benar seperti pada Gambar 2. Dan Klik OK. Dengan menekan tombok OK, anda akan melihat tabel pivot yang masih kosong dan dua toolbar tabel pivot baru, dan sebuah dialog daftar PivotTable baru. Dialog Excel versi 2007 memperlihatkan daftar filed pada bagian atas dan empat tempat meletakkan (Drop Zone) field pada bagian bawah dialog, seperti terlihat pada Gambar Sedangkan pada Excel versi sebelumnya, maka anda harus menarik field dari Dialog Field List langsung ke pivot table. Proses ini sangat menyulitkan bagi orang yang masih baru mengenal pivot tables. Dalam Excel 2007, anda menarik field dari dialog field list bagian atas ke zona yang yang cocok pada bagian bawah dialog Field List. Biasanya dengan hanya meng-klik field yang ada di dialog Field List akan menempatkan field tersebut pada zona yang tepat. Untuk kasus yang kita miliki ini, maka kita mencoba meletakkan produk merupakan daftar yang ke bawah (judul baris) sedangkan wilayah merupakan judul mendatar (judul kolom). Gambar 3. Dialog PivotTable Field List di bagian atas dan Drop Zone di Bagian Bawah Klik Check box Produk di Dialog Field List bagian atas. Excel akan secara otomatis memasukkan ke dalam Row Labels (Label baris) pada drop zone. Pivot table akan menunjukkan daftar produk satu per satu (tidak ada duplikasi) pada Kolom A (lihat Gambar 4). Gambar 4. Klik sebuah field text, dan Excel memasukkan field itu ke area Baris. Klik check box Pendapatan yang ada di dialog Field List bagian atas. Dan karena field tersebut adalah numeric maka Excel akan menambahkan pada bagian Values (nilai) dari pivot table. Jika anda meng-klik check box Wilayah, maka Excel akan meletakkan field tersebut di area row pada pivot table. Karena anda ingin wilayah menjadi judul kolom maka, klik mouse, tahan dan tariklah field Wilayah dari dialog Field List dan letakkan di drop zone Column Labels yang terletak di bagian bawah dialog Field List. Hasilnya adalah ringkasan data penjualan berdasarkan produk dan wilayah, seperti terlihat dalam Gambar 5. Gambar 5. Pivot table membuat anda mudah untuk meringkas laporan. Tingkat Lanjut: Pivot table memberikan banyak opsi (pilihan) yang sangat berguna. Untuk mempelajari lebih lanjut klik disini Untuk Pemakai Versi Sebelumnya: Jika anda menyukai pivot table pro pada Excel versi sebelumnya, maka anda akan dengan cepat menggunakan pivot table Excel 2007. Drop zones telah diubah namanya. Drop Zone Row Area sekarang bernama Row Labels. Sedangkan drop zone Column Area sekarang menjadi Column Labels. Drop Page Field menjadi Report Filter. Dan drop zone Data Area menjadi Σ Values (walaupun saya menyebutnya drop zone Values, dengan mengabaikan symbol Σ). Agar Lebih Mudah: Pada Bentuk Drop Zone: Anda dapat mengatur bentuk dialog PivotTable Field List dengan mengklik dropdown pada bagian atas. Bentuk yang ditawarkan untuk tampilan ini ada lima (5). Tiga diantaranya tidak menyertakan daftar field atau drop zones. Jika tampilan dialog box anda tidak menampilkan salah satu bagian tersebut, maka gunakan panah dropdown untuk mengembalikan tampilan ke Fields Section and Areas Section Stacked. Ada juga yang menampilkan field dan drop zone secara berdampingan. Kesimpulan: Perintah Perintah Di Menu: Insert – Pivot Table
meringkas data di atas, anda dapat menggunakan pivot table. Pivot tables sngat cocok untuk analisis data seperti di atas.
awal pembuatan sebuah Pivot Table, maka dialog PivotTable Field List akan berada di layar sebelah kanan. Anda dapat memindahkan ke tengah dengan cara klik pada Judul Dialog dan tarik ke worksheet agar terlihat mengambang di atas worksheet dan mempermudah anda dalam mengatur data. Dalam membuat artikel ini saya menggeser ke tengah agar tampak dalam gambar yang disajikan sebagai contoh.
Insert – Pivot Table dapat meringkas ribuan baris data secara cepat. Excel tidak membutuhkan anda mengerti rumusnya. Anda hanya harus dapat meletakkan field ke dala tata letak (layout) laporan.
Wednesday, May 7, 2008
Cara menulis rumus matematika di Blog
Kadang kita mau menuliskan lebih besar sama dengan ( ≤ ) dalam blog. Contoh kita mau menulis Rumus a + b ≤ 0
maka yang harus kita harus menulis dalam mode Edit Html sbb:
a + b &le 0 (setelah a + b spasi kemudian &le spasi dan 0
ingat menulisnya harus huruf kecil bukan &LE tapi &le
kode lain yang biasa < ( < ), > ( > ) dan &ge ( ≥ )
Kode ini disebut ISONUM character and Glyphs dan dapat dilihat di:
http://www.w3.org/TR/MathML2/isonum.html
Mengecek Umur Dalam Excel
Misalnya kita punya daftar tanggal lahir beberapa orang, kemudian kita mau mengecek yang berumur 18 tahun ke atas maka dalam excel kita dapat menghitung sbb:
Untuk mementukan apakah umurnya 18 tahun atau lebih ( hasil TRUE berarti benar)
Rumus di Kolom Cek umur
=AND(ISNUMBER(A1),(A1-1)< style="font-weight: bold;">
Kolom Dikembangkan
=IF(AND(ISNUMBER(A2),(A2-1)< style="font-weight: bold;">Hasilnya
| Tgl Lahir | Cek umur | Dikembangkan |
| 4/19/1962 | TRUE | Ya tuh sudah tua |
| 4/20/1992 | FALSE | Masih muda koq |
Menghitung Umur dalam Excel
Untuk menghitung umur dalam Excel
Fungsi yang dapat digunakan untuk menghitung umur dalam excel adalah DATEDIF
Penggunaannya adalah sebagai berikut:
atau
=DATEDIF(A1,NOW(),"y")
Sebagai alternatif dengan menggunakan rumus yang agak panjang adalah
=TEXT(IF(OR(MONTH(TODAY())>MONTH(A1),AND(MONTH(TODAY())=MONTH(A1),
DAY(TODAY())>=DAY(A1))),(TODAY()-A1)/365.25,(TODAY()-A1)/365.25-1),"0")
Tinggal pilih yang mana ... mau panjang dan lama atau cepat ... Kalau untuk jarak tanggal ya Today() atau Now() diganti tanggal aja
Ini adalah iklan :
Rumus Array (CSE) dalam Excel
Rumus Array berbeda dengan rumus biasa yang kita masukkan dalam excel. untuk memasukkan rumus ini kita perlu dua hal berikut
1. Pada saat memasukkan rumus untuk array, maka pertama pilih range (ingat range bukan sel) yang akan diberi rumus, kemudian masukkan rumusnya, setelah selesai tekan Ctrl, Shift dan Enter setaca bersamaan (jangan hanya tekan Enter saja)
2. Pada saat kita memasukkan rumus array, maka akan muncul dua kurung kurawal seperti pada contoh di bawah ini
Dan Yang Perlu Diingat ::::: Jangan memasukkan kurung kurawal sendiri dengan mengetikkannya, Excel akan otomatis membuat kurung tersebut dengan kita menekan CSE (Ctrl, Shift dan Enter)
Monday, May 5, 2008
Menghilangkan Duplikasi Dalam Excel
Berikut adalah kode VBA untuk menghapus duplikasi data serta menghilangkan item yang tidak sama. Sub RemoveDuplicatesInCells() . Sub RemoveDuplicatesInCells2()
Gambar di bawah menunjukkan dua buah kolom yang berisi data. Kita ingin menghapus duplikasi data dari kolom kedua dengan membandingkan ke isi kolom A.
Kode VBAnya adalah sebagai berikut:
Dim c As Range
Dim rCheckForDupes As Range
Set rCheckForDupes = Range("A1:B14")
With rCheckForDupes
For Each c In .Columns(2).Cells
If WorksheetFunction.CountIf(.Columns(1), c.Value) > 0 Then
c.ClearContents
End If
Next c
End With
Set rCheckForDupes = Nothing
End Sub
Hasilnya adalah sebagai berikut:
Bagaimana untuk menghapus sel-sel yang kosong? Maka kita ubah kode VBA menjadi
Dim c As Range
Dim rCheckForDupes As Range
Set rCheckForDupes = Range("A1:B14")
With rCheckForDupes
For Each c In .Columns(2).Cells
If WorksheetFunction.CountIf(.Columns(1), c.Value) > 0 Then
c.ClearContents
End If
Next c
.Columns(2).SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
.Cells(1).Select
End With
Set rCheckForDupes = Nothing
End Sub
Hasilnya adalah:.
Untuk menghapus data yang tidak sama maka, ubahlah tanda lebih besar (>) dalam "If WorksheetFunction.CountIf(.Columns(1), c.Value) > 0" menjadi tanda sama dengan (=) sign.
Friday, May 2, 2008
Paper, Articles and Presentation of Hydrogen Energy
Please note - that the icon on the following pages indicates that the document is in PDF format. Source: http://www.h2net.org.uk/
