MS EXCEL - vlookup, saper terer?

duchessofkent

turun2 kan la ilmu tu sket kat sini .... hehehe .... thanks

Remy_3D

tak faham....

duchessofkent

vlookup to satu command dlm MS Excel. It is good in sorting things kalau we have beribu2 lemon database ...  I donno how to program it ...

Remy_3D

I rasa help file tu dah boleh bantu u....

ps: duke mana

Originally posted by duchessofkent at 7-3-2004 09:26 PM:
vlookup to satu command dlm MS Excel. It is good in sorting things kalau we have beribu2 lemon database ...  I donno how to program it ...

kasi cth operasi apa yg ko nak buat..

maybe aku leh tolong..

nape tak guna index match...lagi power ooo....hehe

duchessofkent

Originally posted by Remy_3D at 2004-3-7 09:41 PM:
I rasa help file tu dah boleh bantu u....

ps: duke mana

dah check kat F1 ... nothing much la ... more on definition rather than how to...

duke?  kat Kent, England :kerek:

duchessofkent

Originally posted by 蝯z坞悬衻 at 2004-3-7 11:42 PM:

kasi cth operasi apa yg ko nak buat..

maybe aku leh tolong..

nape tak guna index match...lagi power ooo....hehe

index match? ala kat opis ni ader excel jer ... ok la tu ... tu pon dah helps me a lot ...

operasi ek? contoh cam nak kutip utang la kan ....

Column A: Nama Company
Column B: No. Invoice
Column C: Issue Date
Column D: Due Date
Column E: Amount
Column F: Description

So kata la ada 200 kompeni, 1 kompeni ada 10-15 invoice yang outstanding... so kenkadang nak sort by compeni name, kenkadang nak sort by Invoice No, Kenkadang nak sort by due date ... cam ner tu? selain daripada kita guna sort biasa ... rasa nak try guna vlookup la plak .... boleh narrow down our search kalau guna vlookup ni kan ...

tolong ajar leh? :nerd:

Remy_3D

Originally posted by duchessofkent at Mon 08th Mar 04 22:51:


dah check kat F1 ... nothing much la ... more on definition rather than how to...

duke?  kat Kent, England :kerek:

Try this site:
http://www.ideamarketers.com/lib ... p;wherefrom=RESULTS
http://www.techonthenet.com/excel/formulas/vlookup.htm

:cak: pesal duchhess tak ikut duke kat kent? :hmm:

Bukan Vlookup yang diperlukan...
Kalau nak narrowdown search, cuma guna Data>Filter>AutoFilter sudah cukup.

Originally posted by duchessofkent at 8-3-2004 10:59 PM:



index match? ala kat opis ni ader excel jer ... ok la tu ... tu pon dah helps me a lot ...

operasi ek? contoh cam nak kutip utang la kan ....

Column A: Nama Company
Column B: No. Invoi ...

index match punya function..mmg dalam excel la..:jeling:

index match ni cam search enginela..


tp kalau melihat problem ni..ko nak sort kan je kan..

maksudnya kenkadang ikut kriteri A..

kenkadang kriteria B dan c..dan etc,,

betul ke ?

kalau betul..ko guna pakai SORT function je..

pastu buat macro dan sediakan button utk pilih kriteria yg ko pilih.

so..bila ko klik button tu je..data ko akan disort mengikut apa yg ko nak

H猷L鰃頧

kenape tak pakai Access aje, buat query + filter aje

duchessofkent

oooo ye ker

tadik try buat auto filter.. cam menarik gakss ... index match tu lom try lagi. cam ner nak buat tu?

aaa tang macro tu.... kena tau vbasic sket kan? lepas tu run.... larriiii ... ok gak rasanya tapi  tak baper terrer pasai tu tak penah buat. tak per skrang ada few tools yg korang sumer suggest. Nanti ada time saya buat tutorial sendiri tenkiu ...

dude jgn jeling2 aaa nanti juling.. :kerek:

Originally posted by 蝯z坞悬衻 at 2004-3-10 10:56 PM:



index match punya function..mmg dalam excel la..:jeling:

index match ni cam search enginela..


tp kalau melihat problem ni..ko nak sort kan je kan..

maksudnya kenkadang ikut kriteri ...

duchessofkent

Originally posted by H猷L鰃頧 at 2004-3-11 01:46 PM:
kenape tak pakai Access aje, buat query + filter aje

access? tak penah blaja pon access ni .... tapi tau pakai dss hehehe ... thanks anyway

Originally posted by duchessofkent at 11-3-2004 10:07 PM:
oooo ye ker

tadik try buat auto filter.. cam menarik gakss ... index match tu lom try lagi. cam ner nak buat tu?

aaa tang macro tu.... kena tau vbasic sket kan? lepas tu run.... larriiii .. ...

heh..auto filter hanya utk tapis data..

excel punya keje ko buat pakai cell..so camane ko nak pakai autofilter..

lainlah keje ko pakai satu column panjang..atau satu row panjang je..

kalau dah banyak pakai row dan column..sort mmg solutionnya..

yup..macro mmg pakai vba..

lagi satu.sebelum aku lupa..kalau ko nak aku tolong utk buat macro die..

tlg perincikan keperluan yg ko nak..maybe i can help it..hehe..


aku pun gian dah lama tak buat coding excel ni..kekkek:ah:

yowa2

hehehe...
dulu ada mmbr buat formula nak kira CGPA danGPA pakai excel. mamat ni mmg scorer. siap ada bhgn probability, berdasarkan carry mark. dasat tol. aku pon tiru2 la formula dia. time tu pakai IF jer. same je ngan vlookup, cuma formula tu panjang.

then kitorg ada main "Mock Trading"... ala2 saham BSKL. hah, bukan main lagi, fail excel nak manage mende ni jer lebih 15Mb kalau tak silap. Excel tuh. so, baru kitorg tau... better pakai vlookup.

operasi ek? contoh cam nak kutip utang la kan ....

Column A: Nama Company
Column B: No. Invoice
Column C: Issue Date
Column D: Due Date
Column E: Amount
Column F: Description

paisey... susah lak nak paham situasi contoh nih. pakai contoh aku leh?
vlookup ntk merujuk ...translate grade (A, A-, B+, B, B-, ...) jadi nombor.. dan calculate GPA n CGPA. cuma nak pahamkan jer..
1st column: Bil.
2nd: Subject
3rd: Credit
4th: Grade
5th: Point
6th: point value
    cuma column point jadi rujukan. okeh, then buat bahagian lain, jadual translate Grade tu.(eg: 1st column: A, 2nd: 4; then next row A-, point 3.67, next row B+, pointnyer 3.33, and so on..). then highlight both row, sort ascending.
okey, now bubuh formula kat cell "point" lak. err... rupa paras formula tu lebih kurang camni la... =VLOOKUP(F10,$I$9:$J$20,2)

mula2 taip "=", klik function button, atau "=", pilih vlookup.
Lookup Value ialah value yg ko rujuk sebelum ditranslate. dlm kes aku, cell Grade. (maknenye, bila aku tulis B, cell yg aku tulis formula tu, akan kuar no 3. kalau B+, kuar la 3.33. gitu lar).
Table array ialah range table yg ko dah sort ascending tadi. maybe ko kena buat 'absolute value' (dollar sign).. tekan F4 kat hujung nama cell.
Column index Number ialah result rujukan tadi. tulis "2". column ke 2. 1st column kan Grade.
Range Lookup ialah... ntah. aku abaikan je mende ni. biar kosong.

so, ok ker gini? aku nk bagi ko DL fail aku... ada data2 aku lak. segan la wei. try dulu yer. ada problem, bley tanya lagiks.

nak buat? Goodluck!

Fast-N-Furious

Originally posted by H猷L鰃頧 at 11-3-2004 01:46 PM:
kenape tak pakai Access aje, buat query + filter aje

yup, haku rasa pakai access lagi elok, buat query form, nampak lagi kemas. tapi memandangkan ko tak belajar, payah lar sket nak ajar.

yowa2

MS access haku xreti. makin rumit lak. tak best lak tu.
Filter excel lebih ok.
Macro... jangan main la kalau xreti...mcm aku. dulu, abis assignment aku kena buat smula, pasal macro la. dah elok2 buat, tertekan macro (F8 kalau tak silap) trus dia overwrite kerja2 aku. cis!

Originally posted by yowa2 at 21-3-2004 12:37 AM:
hehehe...
dulu ada mmbr buat formula nak kira CGPA danGPA pakai excel. mamat ni mmg scorer. siap ada bhgn probability, berdasarkan carry mark. dasat tol. aku pon tiru2 la formula dia. time tu paka ...

kelemahan formula vlookup hanya boleh cari value yg berada di sebalah kiri saja..

pasal tu aku tak nak tulis formula apa2..apsal aku tak tau apa sebenar kehendak

kent tu..

function index match lebih tepat..boleh cari value dimana2 pun..tak kira kiri kanan

atau mana2 pun..pasal tu aku istilahkannya sbg search engine formula..

tp kalau hanya nak sort ikut kriteria..mmg paling sesuai pakai macro utk sort ikut

kategori..berdasarkan cell dan kriteria yg dipilh..

Originally posted by yowa2 at 21-3-2004 11:30 PM:
MS access haku xreti. makin rumit lak. tak best lak tu.
Filter excel lebih ok.
Macro... jangan main la kalau xreti...mcm aku. dulu, abis assignment aku kena buat smula, pasal macro la. dah elok2  ...

maybe ko tekan ctrl+s tu..f8 bukan utk buat overwrite..

lagi satu macro ialah vba..yg perlu ditulis..berdasarkan kehendak ko..

vba based on visual basic language ..tp vba ialah vb utk application2 lain dalam ms

office..maksudnya bahasa vb utk customised ko punya kehendak berdasarkan sesuatu program