Începeți să scrieți următorul articol, aș dori să îmi exprim admirația pentru colegii mei care lucrează în testarea pe teren a sistemelor de asistare a deciziilor, de asigurare, impozitare, etc. etc.
Pentru toți cei care trebuie să sorteze laborios prin informații din cărți de referință. se calculează cu atenție formulele fără sfârșit și să încerce să se potrivească cerințelor de magie fără magie mai puțin abil a funcționalității puse în aplicare.
2. Referințe și ce mănâncă
Toată lumea, chiar și oamenii departe de IT sferă trebuie să facă față în fiecare zi, cu această funcționalitate în fiecare zi. Nu mă crezi - atunci un mic test. Ai dificultăți în a înțelege aceste fraze?
- Moneda principală Statele Unite ale Americii - este USD
- KGB și DMB, deși sună la fel, dar sunt teribil de departe unul de altul
- DE, FR, IT, AU - cineva care nu face parte din UE
Sunt încrezător că problemele cu citire și înțelegere, nimeni nu a venit. Ceea ce, de fapt, se spune că directoarele au devenit parte din viața noastră de zi cu zi, care merg dincolo de domeniu foarte specializat. În cazul în care au apărut toate aceleași dificultăți, acestea sunt rezolvate cu ușurință cu ajutorul tabelului următor:
care de fapt este un reprezentant tipic al „spravochnikovyh“ de familie.
Aceasta este, în general, referința - este un tabel din care valoarea coloanei selectate (cod) poate extrage în mod unic datele corespunzătoare pentru aceasta.
Dacă începe să sape mai adânc în direcția bazei de date, putem vedea că nu există nici o carte de referință, și pur și simplu nu se poate face un pas. Dar tema acestui articol nu este o teorie a bazelor de date relaționale și experiență de lucru cu Ehcel, așa că propun încă o dată pentru a vedea ce putem face doar cu un ciocan în mâinile sale, și Microsoft Office unghiile și capul luminos.
3. Ce pot să placă Excel?
Suntem cu voi va fi interesat în a 2-o - VLOOKUP și HLOOKUP. Aceste caracteristici sunt în general similare și diferă numai în direcția de căutare (pe verticală și pe orizontală, respectiv). Pentru majoritatea dintre noi este aranjament mai natural de rânduri orizontale și verticale - coloane, astfel încât toate exemplele se vor baza pe utilizarea funcției VLOOKUP, realizând lucrul cu acest plan particular.
4. Intrupari
Pentru început se va contura un obiectiv al aventurii noastre, care este ceea ce dorim să primim în schimbul suferinței noastre.
Exemplu de cineva poate părea exagerată, dar cu toate acestea, este destul de prezentabil pentru a servi ca un instrument de formare.
Deci, un caz simplu de utilizare sugerează că fiecare cod corespunde o singură intrare unică în tabel. Eu, după cum știți, în beneficiul a luat o listă de state din SUA (numele lor se răcească și internetul este plin de informații în formă de tabel):
Pentru comoditate, o listă de lucrări l-au transportat pe o pagină separată și marcate ca o serie pe nume
După aceea, singurul lucru pe care trebuie să facem - este de a determina în cazul în care vom lua codul de căutare și pe care dorim să-l înlocuiască acest cod. Ca rezultat, obținem următoarea expresie:
= VLOOKUP (B2; Spisok_Shtatov; 2; FALSE)
B2 - o celulă cu codul
Spisok_Shtatov - este ghidul nostru, în care suntem interesați în a doua coloană cu numele complet al statului
Fii atent cu ultima opțiune.
Ultimul parametru (FALSE) definește o regulă codul de căutare:
- Dacă este TRUE sau omis - găsește cea mai apropiată opțiune
- Dacă este FALSE - căutare efectuate în conformitate deplină
Încercați să joc cu această opțiune, ca să spunem așa, simți diferența.
Apropo, nimeni nu a acordat nici o atenție la ceea ce este încă lipsește în parametrii de intrare ai funcției VLOOKUP pentru a completa imaginea? Cum știa în care coloana din directorul nostru conține codul de proprietate?
Dar acest lucru este obyazannnost ta directă - asigură că codurile coloana a fost prima coloană din directorul nostru
Versiunea mai complicată în loc de cod deja există un astfel de lucru ca o serie de valori și au fiecare interval corespunde la orice înregistrare.
Un exemplu tipic este un tabel cu pas în sus a valorilor sivity pentru calcularea primelor de asigurare în funcție de vârsta vehiculului.
Pentru a calcula de mână este de informații și dificultăți de calcul suficiente în sine nu provoacă. Dar, pentru a fi utilizate ca date de intrare pentru a calcula în mod automat vom avea nevoie de un pic converti acest tabel.
Ca rezultat, obținem tabelul următor:
Iar formula de calcul a „suplimentul de vârstă“, va fi după cum urmează
= VLOOKUP (D10; Vozrast_avtomobilya; 2; TRUE)
D10 - o celulă cu codul
Vozrast_avtombilya - aducerea în minte catalogul nostru
Și să acorde o atenție la ultima opțiune - ea are acum o valoare TRUE, indicând faptul că căutarea regulă pentru valori aproximative și nu o valoare exactă.
Prezentarea materialului în paragrafele precedente a început cu cuvintele „... fiecare cod și fiecare bandă ...“. Aceasta este ipoteza de bază este că avem o valoare unică pe de o parte și un set de informații suplimentare pentru a-l pe de altă parte.
Să considerăm acum situația în care nu ne putem baza pe un mesaj de user-friendly. Să presupunem că selectarea informațiilor necesare depinde de un set de valori-cheie, cum ar fi un sistem extrem de simplificat pentru calcularea primei de asigurare pentru asigurare auto din Statele Unite, depinde de următorii parametri:
- vârstă vehiculului: 1-3, 4-7, 8-10,> 10
- podeaua conducătorului auto (nu le pasă au vrut să egalitatea de gen): M, F
- Experiența de conducere: <2, 2-5,>5
Un set complet de toate opțiunile, chiar și în astfel de versiune uzhaty conține 24 de intrări, fiecare dintre care au coeficientul adecvat pentru calcularea primelor de asigurare. Cum se poate, în acest caz, vom ști funcția VLOOKUP, care nu funcționează cu un set de chei, și acceptă doar un singur cod de la intrare.
Răspunsul este - nici un fel ... fără pre-reglare. Ce vom face cu tine un pic mai departe.
Potrivirea va fi de a crea un câmp suplimentar cu un cod care identifică în mod unic fiecare set, de exemplu,
1-3, M, <2 преобразуется в 1M0
4-7 M, 2-5 este convertit în 11ZH2
În printspipe, informațiile obținute din exemplele de mai sus este suficient pentru a acoperi majoritatea cazurilor, utilizarea de cărți de referință. Dar, ca un bonus (pentru desert să spun așa), aș sugera că a găsit în lucrarea este interesantă, asociată cu materialul de mai sus.
5.1. Folosind coloana cod pentru validare
În primul nostru exemplu, avem cu statele ca răspuns la starea codul pe care doriți să obțineți numele complet. Estesvenno dorinta a fost de a se proteja împotriva introducerii de cod greșit, cu atât mai mult, deoarece toate codurile suntem prezenți în directorul nostru și avem cunoștințele necesare (amintiți-vă aici), pentru a aranja lista drop-down pentru coduri.
În cazul în care nu au fost pentru un singur lucru - organizarea de validare a listei, avem nevoie pentru a specifica sursa de umplere lista drop-down, iar sursa nu poate fi de 2-dimensional de masă, care este Spisok_Shtatov directorul nostru. Ce vă spun imediat Excel
S-ar părea că singura opțiune validă este de a defini în continuare intervalul numit numai pentru coloana coduri, dar în ceea ce privește panglica corectă ar dori să găsească o opțiune mai elegantă. Și este :)
În sursa de date trebuie să specifice o expresie:
Și „cheia de aur în buzunar.“
5.2. ghiduri conexe
Primirea mai potrivite pentru secțiunea de trucuri decât trucuri, dar cu toate acestea, uneori utile (cunosc personal un astfel de caz).
Tot ce trebuie să facem - este de a pregăti listele de orașe, inclusiv cu numele de cod de stat, de exemplu,
Goroda_FL pentru Florida,
Goroda_TX pentru Texas
Și pentru a cunoaște un alt mare fuktsii INDIRECT. care să permită să creeze un standard Excel-link din valoarea șir.
Ca urmare, expresia de validare va fi după cum urmează:
Stimați colegi, am încercat să explic în articol toate evoluțiile legate de testarea algoritmilor pe baza tabelelor de referință. Găsim aceasta tehnica foarte util atunci când se testează sisteme foarte complicate de calcul a primelor de asigurare. Eu chiar sper că veți găsi aceste informații în ceva va fi de folos.
Dându-și seama că teoria fără practică este rău este depozitat chiar și în capete luminoase, împreună cu materialele pe care le va găsi Excel-fișier cu toate exemplele implementate în articol.