Scribd Logo
Upload

Welcome to Scribd!

  • 384 views

    Database Spare Parts: No. Part Number Description Price Bin

    Uploaded by

    Achmad Zajid
    The document contains a database listing 20 spare parts with their part numbers, descriptions, prices, storage bin locations, and quantities per bin. Various formulas are also provided as examples for summing values based on criteria, looking up values, and removing #N/A errors from lookups. The document aims to provide a database of spare parts and examples of formulas that can be used to analyze the data.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd

    Database Spare Parts: No. Part Number Description Price Bin

    Uploaded by

    Achmad Zajid
    384 views7 pages
    The document contains a database listing 20 spare parts with their part numbers, descriptions, prices, storage bin locations, and quantities per bin. Various formulas are also provided as examples for summing values based on criteria, looking up values, and removing #N/A errors from lookups. The document aims to provide a database of spare parts and examples of formulas that can be used to analyze the data.

    Original Description:

    rumus excel

    Original Title

    025 Rumus2 Excel

    Copyright

    © © All Rights Reserved

    Available Formats

    XLSX, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains a database listing 20 spare parts with their part numbers, descriptions, prices, storage bin locations, and quantities per bin. Various formulas are also provided as examples for summing values based on criteria, looking up values, and removing #N/A errors from lookups. The document aims to provide a database of spare parts and examples of formulas that can be used to analyze the data.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    384 views7 pages

    Database Spare Parts: No. Part Number Description Price Bin

    Uploaded by

    Achmad Zajid
    The document contains a database listing 20 spare parts with their part numbers, descriptions, prices, storage bin locations, and quantities per bin. Various formulas are also provided as examples for summing values based on criteria, looking up values, and removing #N/A errors from lookups. The document aims to provide a database of spare parts and examples of formulas that can be used to analyze the data.

    Copyright:

    © All Rights Reserved
    Download as XLSX, PDF, TXT or read online from Scribd
    Download as xlsx, pdf, or txt
    of 7

    DATABASE SPARE PARTS

    No.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

    Part Number

    Description

    06141-KRS-505
    06141-KWB-505
    061A1-439-002
    061A1-GN8-002
    061A1-KEG-002
    061A1-KEH-002
    061A1-KEV-002
    061A1-KFL-002
    061A1-KG2-002
    061A1-KPH-002
    06381-KPH-900
    06381-KVB-930
    06381-KVY-960
    06401-KEH-900
    06401-KEV-881
    06401-KPH-881
    06401-KTL-700
    12341-KFL-850
    12361-035-000
    12361-383-000

    CAM CHAIN KIT


    CAM CHAIN KIT
    GASKET,KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    ROD KIT CONNECTING
    ROD KIT CONNECTING
    ROD KIT CONNECTING
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    COVER L CYL HEAD (SLV)
    CAP TAPPET
    CAP HOLE

    Price
    35,700
    37,500
    44,000
    67,500
    50,200
    51,000
    73,000
    55,000
    45,000
    62,000
    215,000
    165,000
    160,000
    241,000
    145,000
    125,000
    141,000
    21,500
    4,500
    5,000

    Bin
    G50711
    G50311
    B60111
    E40631
    D30231
    D40931
    D30921
    D30131
    D20131
    D10131
    C61021
    E40111
    E50711
    G60811
    G60811
    G20811
    G40811
    A60131
    G30311
    G30111

    Qty/Bin

    8,016
    1.53
    19
    35
    55
    267
    263
    287
    0
    392
    100
    16
    29
    2,321
    895
    4,233
    3.4
    72
    1,578
    2,552

    RUMUS - RUMUS
    1. FUNGSI SUMIF
    =SUMIF(RANGE;CRITERIA;[SUM_RANGE])
    =SUMIF(D6:D25;D6;E6:E25)
    Hasil :

    73,200

    2. FUNGSI " $ " untuk mengunci Cell


    =$CELL$VALUE

    Contoh :

    06141-KRS-505
    06141-KRS-505

    3. FUNGSI VLOOKUP
    =VLOOKUP(LOOKUP_VALUE;TABLE_ARRAY;COL_INDEX_NUM;[RANGE_LOOKUP])
    =VLOOKUP(K20;C6:G25;2;FALSE)
    Hasil :

    #N/A

    4. MENGHILANGKAN TAMPILAN #N/A


    =IF(ISNA(VLOOKUP(K25;C6:G25;2;0));"";VLOOKUP(K25;C6:G25;2;0))
    Hasil :

    E_LOOKUP])

    DATABASE SPARE PARTS


    DATABASE SPARE PARTS
    No.
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20

    Part Number

    Description

    06141-KRS-505
    06141-KWB-505
    061A1-439-002
    061A1-GN8-002
    061A1-KEG-002
    061A1-KEH-002
    061A1-KEV-002
    061A1-KFL-002
    061A1-KG2-002
    061A1-KPH-002
    06381-KPH-900
    06381-KVB-930
    06381-KVY-960
    06401-KEH-900
    06401-KEV-881
    06401-KPH-881
    06401-KTL-700
    12341-KFL-850
    12361-035-000
    12361-383-000

    CAM CHAIN KIT


    CAM CHAIN KIT
    GASKET,KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    GASKET KIT A
    CAP HOLE
    ROD KIT CONNECTING
    ROD KIT CONNECTING
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    DRIVE CHAIN KIT
    COVER L CYL HEAD (SLV)
    CAP TAPPET
    CAP HOLE

    Price
    35,700
    43,500
    44,000
    67,500
    50,200
    51,000
    73,000
    55,000
    45,000
    62,000
    215,000
    165,000
    160,000
    241,000
    145,000
    125,000
    141,000
    21,500
    4,500
    5,000

    Bin
    G50711
    G50311
    B60111
    E40631
    D30231
    D40931
    D30921
    D30131
    D20131
    D10131
    C61021
    E40111
    E50711
    G60811
    G60811
    G20811
    G40811
    A60131
    G30311
    G30111

    Qty/Bin
    8,016
    1.53
    19
    35
    55
    267
    263
    287
    0
    392
    100
    16
    29
    2,321
    895
    4,233
    3.4
    72
    1,578
    2,552

    CONTOH APLIKASI RUMUS :


    A. RANKING OTOMATIS
    Qty/Bin

    RUMUS UMUM : =RANK(J9;$J$9:$J$13)


    RUMUS RANK TIDAK GANDA : =RANK(J9;$J$9:$J$13)+COUNTIF($J$9:J9;J9)-1
    No
    1
    2
    3
    4
    5

    Nama
    CAM CHAIN KIT
    GASKET KIT A
    ROD KIT CONNECTING
    DRIVE CHAIN KIT
    CAP HOLE

    Qty (Nama)

    RANK umum

    2
    7
    2
    4
    2

    3
    1
    3
    2
    3

    RANK tidak
    ganda
    3
    1
    4
    2
    5

    B. MENGHITUNG NILAI DARI JENIS ORDER DALAM SEBUAH DATABASE


    =SUMIF($C$5:$C$24;I5;$F$5:$F$24)
    No
    1
    2
    3
    4
    5

    Nama
    CAM CHAIN KIT
    GASKET KIT A
    ROD KIT CONNECTING
    DRIVE CHAIN KIT
    CAP HOLE

    JUMLAH
    NOMINAL

    79,200
    403,700
    325,000
    652,000
    220,000

    A.1. HASIL AKHIR RANKING OTOMATIS


    Kolom Rumus Penghubung
    DATA AWAL
    RANK tidak ganda
    3
    1
    4
    2
    5

    DATA AKHIR
    Nama

    CAM CHAIN KIT


    GASKET KIT A
    ROD KIT CONNECTING
    DRIVE CHAIN KIT
    CAP HOLE

    RANK
    1
    2
    3
    4
    5

    B.1. LANGKAH CEPAT BACA LIST KRITERIA

    Klik : Data - Data Validation - List - Source - "Pilih Kolom/Baris yang akan dijadikan kr
    Contoh :
    Nama
    Jumlah Nominal
    ROD KIT CONNECTING
    325,000

    DATA AKHIR
    Nama
    GASKET KIT A
    DRIVE CHAIN KIT
    CAM CHAIN KIT
    ROD KIT CONNECTING
    CAP HOLE

    om/Baris yang akan dijadikan kriteria"

    You might also like