Rumus Excel - Membuat array OR dalam SUM

Rumus array yang saya miliki saat ini sangat mendekati yang saya inginkan, hanya dibatasi oleh kenyataan bahwa saya hanya dapat memiliki dua kondisi (kecuali saya ingin menambahkan lebih banyak secara manual). Yang ingin saya lakukan adalah dapat menggunakan nama yang berasal dari kolom tabel untuk mencocokkan kriteria, mirip dengan menggunakan OR() dengan rentang.

Saat ini, rumus array saya terlihat mirip dengan ini (menggunakan nama atau referensi rentang)

=SUM((JOB_HRS)*(TEXT($E4,"0")=TEXT(EMP_ID,"0"))*(JOB_DATE>=$M4)*(JOB_DATE<=$N4)*((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9)))

Saya mendapatkan lembar laporan dengan sekitar 5000 baris data. Rumus ini secara akurat menguraikan lembar dan merangkum total jam kerja seorang karyawan (berdasarkan ID yang terkadang disimpan sebagai angka atau teks berdasarkan perasaan program keluaran hari itu)

Bagian terakhir adalah apa yang ingin saya sederhanakan

((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9))

Berhasil, tapi saya sedikit dibatasi oleh jumlah sel, ditambah lagi rumusnya terlihat terlalu panjang. Saya ingin menyederhanakannya menjadi seperti ini, tetapi tidak dapat menemukan referensi yang bagus untuk bekerja dengan array SUM. Ada pemikiran?

(JOB_TYPE=RNG_OF_JOB_TYPES)

Sunting: Menambahkan gambar

Hasil yang Diharapkan


arrays conditional-statements excel sum
person Karl    schedule 29.11.2017    source sumber
comment
Adakah alasan mengapa Anda tidak menggunakan PivotTable saja?   -  person jeffreyweir    schedule 29.11.2017
comment
Dari apa yang saya temukan, PivotTable menggunakan referensi kode keras untuk filter dan saya harus dapat memperbarui milik saya secara dinamis dengan referensi sel.   -  person Karl    schedule 29.11.2017
comment
Gunakan Pemotong untuk memfilter Pivot. Hasil akhirnya sama, implementasi dan Pengalaman Pengguna jauh lebih sederhana.   -  person jeffreyweir    schedule 29.11.2017
comment
Saya kira bagian dari reservasi saya sedang diformat. Saya memiliki lembar yang diformat dengan sempurna untuk dicetak pada satu lembar. Saya tidak ingin PivotTable tumbuh dan menyusut. Selain itu, saya mencoba membatasi secara super apa yang dapat disesuaikan oleh pengguna akhir pada lembar itu sendiri menggunakan sel yang dilindungi dan ini sangat berat dengan pemformatan bersyarat. Saya dapat melakukan ini dengan VBA dengan sangat mudah, namun saya bahkan dibatasi dengan kebijakan keamanan pada komputer yang akan digunakan.   -  person Karl    schedule 29.11.2017
comment
Bukankah PivotTable akan menampilkan jumlah rekaman yang sama persis dengan rumus Anda? Selain itu, apakah rumus ini akan dijalankan untuk setiap Emp_ID? Berapa banyak yang Anda miliki? Pernahkah Anda melihat fungsi SUMIFS? Perhatikan bahwa SUMIF/SUMIFS mahal secara komputasi, dan harus digunakan dengan hemat. Hal terakhir yang Anda inginkan adalah lembar kerja dengan puluhan ribu SUMIFS di dalamnya.   -  person jeffreyweir    schedule 30.11.2017
comment
Emp_ID inilah yang akan berubah di setiap baris. Rumusnya hanya muncul di 1 kolom, saya punya banyak data ringkasan lain yang perlu diungkapkan sejalan dengannya sebagai satu dokumen sumber yang tidak cocok dengan PivotTable. Ini adalah sesuatu yang perlu dengan mudah disalin ke dalam PowerPoint dan diberikan pengarahan kepada orang-orang tipe CEO yang tidak punya waktu untuk menguraikannya. Saya benar-benar hanya membutuhkan 1 hasil rumus untuk dimasukkan ke dalam satu sel.   -  person Karl    schedule 30.11.2017
comment
Jika Anda memiliki Emp_ID di panel Baris dan kolom spesifik lainnya yang Anda inginkan di panel Baris atau Kolom, lalu bagaimana PivotTable akan menggunakan lebih banyak ruang daripada solusi rumus? Saya pikir saya melewatkan sesuatu...   -  person jeffreyweir    schedule 30.11.2017
comment
Apakah Anda akan membuat kode keras Emp_ID itu? Apakah ada kemungkinan mereka akan berubah di masa depan? Bukankah lebih baik menggunakan PivotTable dalam hal ini, sehingga Anda mendapatkan daftar lengkap Emp_ID, daripada beberapa yang mungkin mubazir? Maaf karena harus membahas tentang PivotTable...hanya mencoba memahami kebutuhan Anda dan memastikan Anda mendapatkan pendekatan terbaik yang diberikan :-)   -  person jeffreyweir    schedule 30.11.2017
comment
Saya menggunakan kolom yang memiliki validasi data untuk memilih Emp berdasarkan Nama, yang mengisi ID di kolom tersembunyi. Lembar tersebut diurutkan berdasarkan posisi karyawan yang diberi kode keras, yang darinya karyawan akan diganti secara berkala. Saya akan mengunggah cuplikan seperti apa hasilnya. Kolom di sebelah kanan adalah kolom terhitung berdasarkan rumus asli saya. Saya hanya ingin memperbaruinya untuk memungkinkan lebih banyak kriteria secara dinamis.   -  person Karl    schedule 30.11.2017
comment
Jadi Anda memiliki sekitar 5000 karyawan yang perlu ditampilkan? Atau ini lebih merupakan laporan ad-hoc di mana pengguna memilih segelintir karyawan yang diminati?   -  person jeffreyweir    schedule 30.11.2017
comment
Lebih bersifat ad-hoc. Saya hanya memiliki 42 karyawan untuk dilacak. Yang panjang adalah saya punya 5000 baris jam untuk penerbangan   -  person Karl    schedule 30.11.2017
comment
Saya lebih menyukai kontrol spesifik yang saya miliki dibandingkan pemformatan dan tata letak.   -  person Karl    schedule 30.11.2017
comment
Saya menggunakan Gaya PivotTable khusus untuk membuat pivot terlihat persis seperti yang saya inginkan. Tapi saya sangat bias...Saya pikir PivotTable adalah cara terbaik untuk melakukan apa saja secara praktis, dan menggunakannya sebagai pengganti VBA atau Rumus di mana pun saya bisa.   -  person jeffreyweir    schedule 30.11.2017
comment
Cara lain untuk mendapatkan yang terbaik dari kedua dunia adalah dengan menggunakan PivotTable pada lembar tersembunyi untuk menghitung angka, lalu mengambil jawaban spesifik yang Anda inginkan menggunakan fungsi GETPIVOTDATA yang mengagumkan. Telah memasukkan ini ke dalam jawaban saya.   -  person jeffreyweir    schedule 30.11.2017
comment
Saya kira saya bias terhadap mereka. Saya sebenarnya lebih suka Access tetapi saya harus membuat pelacak ini tetap sederhana... Saya kira saya mencari sesuatu yang mirip dengan *(IF(MATCH(FLTD_ACFT_MDS,CFG_PRIACFT,0),1,0)) tetapi ini menghasilkan kesalahan ini berfungsi dalam array hitungan.   -  person Karl    schedule 30.11.2017
comment
MAX(--(JOB_TYPE=RNG_OF_JOB_TYPES)) harus memberikan 0 jika tidak ada yang benar, dan 1 jika ada yang benar   -  person MacroMarc    schedule 30.11.2017
comment
Apakah ada cara untuk menggunakan MAX bersamaan dengan SUM()*()? Saya mendapatkan kesalahan dengannya.   -  person Karl    schedule 01.12.2017

Jawaban (1)


arrow_upward
1
arrow_downward

Rumus terbaik Anda adalah dengan menggunakan rumus SUMIFS. Anda masih perlu menentukan setiap kriteria satu per satu, namun sintaksisnya jauh lebih sederhana:

=SUMIFS(sum_range,criteria_range,criteria,...)

Namun seperti yang telah dibahas, preferensi pribadi saya adalah menggunakan PivotTable untuk melakukan penghitungan angka. Terkadang saya meletakkan Pivot pada lembar tersembunyi, dan menggunakan fungsi GETPIVOTDATA di lembar yang diekspos ke pengguna untuk mendapatkan masukan dan mengambil nilai agregat dari PivotTable. Ini memberi Anda fleksibilitas penuh dalam memformat.

person jeffreyweir    schedule 29.11.2017