SharePoint List – Custom Progress Calculated Column

În SharePoint folosim de multe ori coloanele calculate în liste pentru a determina anumite valori pe baza altor coloane. De cele mai multe ori aceste operațiuni se rezumă la operațiuni simple de adunare, scădere, și în cazuri mai avansate la folosirea funcțiilor dedicate pentru a reprezenta starea unui element de listă. La această adresă aveți o listă destul de bogată de formule și funcții pentru câmpurile calculate.

Într-un proiect curent am descoperit cât de utilă pentru utilizatorii finali poate fi crearea unei liste de gestionare a activităților, un fel de plan de proiect care presupune execuția unor pași determinați/cunoscuți în ordinea lor.

Custom project list

Coloanele Step1, Step2, Step3 si Step4 sunt coloane de tip Choice și pot avea valorile? Not started, In progress sau Done! În funcție de starea fiecărei coloane se poate determina valoarea coloanei calculate Progress care are la bază următoare formulă:

=IF(AND(Step1="Done!";Step2="Done!";Step3="Done!";Step4="Done!");1;
(IF(AND(Step1="Done!";Step2="Done!";Step3="Done!";Step4="In progress");0,85;
(IF(AND(Step1="Done!";Step2="Done!";Step3="Done!");0,75;
(IF(AND(Step1="Done!";Step2="Done!";Step3="In progress");0,65;
(IF(AND(Step1="Done!";Step2="Done!");0,5;
(IF(AND(Step1="Done!";Step2="In progress");0,35;
(IF(Step1="Done!";0,25;
(IF(Step1="In progress";0,15;0)))))))))))))
))

Formula este în Romanian format. Pentru cei care o vor în Engleză trebuie să înlocuiască caracterul virgulă cu punct și apoi punct și virgulă cu virgulă.

În mod normal se spune că în coloanele calculate există o limitare de 7 IF-uri imbricate, dar se pare că în articolul Maximum number of IF statements in a Calculated Column? veți găsi o metodă de concatenare a mai multor IF-uri pentru a putea scrie formule mai complexe.

Următoarea etapă în proiectarea unei astfel de liste este aceea de a valida datele introduse în așa fel încât fiecare pas să înceapă abia după ce s-a finalizat cel anterior. Aici este o adevărată problemă pentru că în caseta Formula de la : List Settings : Validation Settings este permis doar un număr limitat de caractere, și trebuie găsită o altă metodă de a scrie funcția logică prin limitarea numărului de coloane introduse și a valorilor posibile de luat. De aceea eu am preferat să folosesc o funcție AND() de intercorelare cumulativă a rezultatului evaluării mai multor funcții logice IF().

Cam așa ar arăta formula de validare a listei:

=AND(
IF(Step1<>"Done!";Step2="Not started";OR(Step2<>"Not started";Step2="Not started"));
IF(Step2<>"Done!";Step3="Not started";OR(Step3<>"Not started";Step3="Not started"));
IF(Step3<>"Done!";Step4="Not started";OR(Step4<>"Not started";Step4="Not started"))
)

În traducere, dacă Step1 nu este finalizat, înregistrarea este validă doar dacă Step2=”Not started”. Dacă Step1 este finalizat (valoarea Done!) atunci pasul 2 poate avea orice stare diferită de Not started sau chiar egală cu aceasta. Este un artificiu și mie mi-a luat ceva timp să ajung la formulele acestea. Sper voi doar să beneficiați de ele ;)

În cazul în care doriți să testați live lista, o găsiți la adresa: http://portal.feaa.uaic.ro/Test/Lists/Custom%20Project/AllItems.aspx

Enjoy!

Excel IF() – Insane Level

Multe probleme din domenii diverse de activitate se pot rezolva in Microsoft Excel. În timp devin rutină și nu le mai dăm importanță…. sau le tratăm superficial, sau … cum îmi place mie să spun facem coloane ajutătoare… și ajutătoare și coloane ajutătoare la cele ajutătoare :)

Astăzi am o provocare pentru cei care vor să-și pună mintea la contribuție.

Se dă următorul tabel de date:

InsaneIF

Specificați, care este formula de calcul pentru a determina procentul de AC (Adaos comercial – pentru cei care nu știu) după următorul algoritm:

Stoc Pret<5 Pret<10 Pret<15 Pret>=15
<500 AC=1 AC=2 AC=3 AC=4
<1000 AC=5 AC=6 AC=7 AC=8
<1500 AC=9 AC=10 AC=11 AC=12
>=1500 AC=13 AC=14 AC=15 AC=16

Exemplu: dacă prețul este mai mic decât 10 și stoc < 1000, adaos comercial va fi 6%. Dacă Prețul este mai mare sau egal decât 15 și stocul < 1500 atunci adaosul comercial va fi de 12%.

Soluțiile le trimiteți pe e-mail până în data de 10.03.2013 ora 22:00.

Vă rog să nu puneți comentarii cu formula până nu se termină timpul alocat!

Succes!

Update 10.03.2013 după ora 22:00

 

Trăim într-o lume dinamică, dar cu mult prea multe aspecte statice. De multe ori spuneam că ceea ce nu vedem nu avem cum să înțelegem sau invers, așadar justific numărul mic de răspunsuri prin prezența slabă în social media într-o zi de duminică.

Câteva explicații teoretice:

Sunt conștient că vin dintr-o zonă academică, dar astfel de probleme provin din practică. Rolul cercetării ar fi acela de optimizare a metodelor de lucru din practică, și găsirea celor mai rapide metode de rezolvare a problemelor.

Așadar, cele mai comune probleme din domeniul economic, vin din zona interpretării intervalelor de valori. Mulți practicieni folosesc Excelul într-un mod… ”minunat” ( a se citi: de te crucești) dar mi-a fost dat să întâlnesc și oameni care știu clar că datele le înregistrezi în baze de date, pe baza cărora faci rapoarte, iar dacă vrei grafice și floricele, cel mai bine este să conectezi Excelul la acea sursă de date și să faci interpretări câte în lună și în stele.

Revin la problema cu intervalele:

Funcția IF() permite selectarea unei acțiuni din două posibile pe baza evaluării unei condiții.

Formatul general al funcţiei este (pentru setări ale calculatorului cu Regional Settings Romania):

=IF(conditie;actiune_adevarat;actiune_fals)

image

Introducerea unei funcţii IF într-o foaie de calcul se poate realiza prin una din următoarele metode:

1. Acţionarea pictogramei Insert Function din linia Formula bar. În acest caz apare fereastra pentru alegerea funcţiei dorite. Se selectează categoria de funcţii (în cazul nostru Logical) şi apoi funcţia IF;

2. Precizarea argumentelor funcției: condiția (Logical_test), acțiunea adevărat (Value_if_true) și acțiunea fals (Value_if_false);

3. Finalizarea funcției prin apăsarea butonului OK.

Altă metodă de deschidere a constructorului de funcții este utilizarea meniului Formulas, opțiunea Logical și se alege din listă formula IF sau scrierea funcţiei utilizând tastatura și, în anumite cazuri, mouse-ul.

Problemele clasice cu IF-uri sunt cele legate de intervale de valori: Discounturi, Impozite/Taxe, Ajustari.

Intervalele de valori pot fi într-o dimensiune, două dimensiuni intersectate sau mai multe. Până la această oră am reușit să determin algoritmul de calcul matematic pentru prima și a doua dimensiune.

O singură dimensiune

Dacă ai un interval de valori pentru care trebuie să faci o funcție IF atunci numărul de IF-uri dintr-o funcție Excel este egal cu numărul intervalelor minus unu.

Exemplu:

Să se calculeze procentul de discount acordat pe o comandă de produse, in funcție de următoarele criterii:

  • o in cazul in care cantitatea este mai mic decât 10 – atunci procentul de discount este de 0%
  • o cantitate mai mare decât 10 si mai mic decât 15 atunci discount 0,5%
  • o cantitate mai mare decât 15 si mai mic decât 20 atunci discount 1%
  • o cantitate intre 20 si 25 atunci discount 1,25%
  • o cantitate mai mare decât 25 atunci discount 1,5%

Intervalul de valori este în cazul nostru: 0 – 10 – 15 – 20 – 25 – x adică 5 intervale de valori, ceea ce înseamnă în grafic 5-1 ==> 4 funcții IF.

Cel mai simplu este să tratezi un interval de valori într-un IF de jos în sus.

Două dimensiuni

Problemele se complică puțin când avem două dimensiuni care depind una de cealaltă și în care trebuie să tratăm toate combinațiile posibile. Doar în scop de exemplu:

Calculați prețul de vânzare fără TVA (per unitate) al produselor după următoarele constrângeri:

  • dacă stocul este mai mare sau egal decât 1000, și prețul este mai mare sau egal cu 5 lei, adaosul comercial va fi 20%,
  • dacă stocul este mai mare sau egal decât 1000, și prețul este mai mic de 5 lei, adaosul comercial va fi 25%,
  • dacă stocul este mai mic decât 1000, și prețul este mai mare sau egal cu 5 lei, adaosul comercial va fi 25%,
  • dacă stocul este mai mic decât 1000, și prețul este mai mic de 5 lei, adaosul comercial va fi 30%.

Așadar, în acest exemplu avem două intervale de valori: stocul și prețul pe care trebuie să le tratăm global:

Schematic:

  • Stoc: 0 – 1000 – x
  • Preț: 0 – 5 – y

Modul de calcul al IF-urilor care vor apărea în formulă este: numărul de intervale de pe prima dimensiune minus 1 + numărul de intervale de pe a doua dimensiune înmulțit cu numărul de deasupra minus 1.

Așadar:

  • Stoc: 0 – 1000 – x ==> două intervale ==> 2-1 = 1 +
  • Preț: 0 – 5 – y ==> două intervale ==> 2x(2-1)=2×1=2=3

 

Folosind algoritmul în două dimensiuni

Soluția problemei din articolul inițial este:

75079_10200943603212056_225359347_n

Asta înseamnă rezolvare corectă din punct de vedere didactico-teoretic. O soluție pe care oricine o poate înțelege, reproduce și controla. În același timp: Schema scrie formula!

Cine a câștigat competiția inițială?

Sper că toți cei care văd acest algoritm de lucru.

În fapt, din totalul celor 194 care au deschis articolul,  au trimis e-mail (ordonat după timp) următorii:

1. Cristiana R. – Soluție funcțională în 16 IF-uri

2. Andreea A. – Soluție optimă în 15 IF-uri

Soluția ei:

=IF(E2<5,IF(D2<500,1,IF(D2<1000,5,IF(D2<1500,9,13))),IF(E2<10,IF(D2<500,2,IF(D2<1000,6,IF(D2<1500,10,14))),IF(E2<15,IF(D2<500,3,IF(D2<1000,7,IF(D2<1500,11,15))),IF(D2<500,4,IF(D2<1000,8,IF(D2<1500,12,16))))))

3. Catalin D. – Soluție funcțională folosind Macro-uri… Nu face parte din concurs, dar este o soluție ingenioasă.

4. Vladut A.P. – Soluție funcțională în 20 IF-uri

5. Radu G. – Soluție nefuncțională pentru că nu utilizează IF-uri, dar deosebit de interesantă:

=OFFSET(Sheet2!$B$2; MATCH(D2; Sheet2!$B$3:$B$6; 1); MATCH(E2; Sheet2!$C$2:$F$2; 1))

Tabelul din Sheet2:

    Pret<5 Pret<10 Pret<15 Pret>=15
    0 5 10 15
Stoc<500 0 1 2 3 4
Stoc<1000 500 5 6 7 8
Stoc<1500 1000 9 10 11 12
Stoc>=1500 1500 13 14 15 16

 

6. Ciprian a mai propus o soluție cu 96 de IF-uri…

7. Dragos si B.S. a propus o soluție funcțională cu 16 IF-uri.

Mulțumesc tuturor pentru implicare!

Future

Lucrez la algoritmul de calcul pentru 3 intervale… încă nu-mi iese… ceva…

Spor în ceea ce faceți și dacă mai aveți probleme interesante nu ezitați să le aruncați pe mail.

UPDATE 11.03.2013 după ora 9:30

Una din cele mai interesante soluții pe care am primit-o în dimineața aceasta de la Ionuț E. este cu o funcție IF ultra simplificată. Asta din cauză că procentele de AC sunt consecutive. Smart!

=IF(STOC<500,0,IF(STOC<1000,4,IF(STOC<1500,8,12)))+IF(PRET<5,1,IF(PRET<10,2,IF(PRET<15,3,4)))

Da merge!!! Că am testat. Dacă AC-ul ar fi fost altfel, dar proporțional probabil că ar fi găsit algoritmul de însumare.

De asemenea, la cererea publicului voi dezvălui soluția magică cu macro a lui Catalin D. El a creat un nou modul in VBA și a definit o nouă funcție cu numele IFS(). Codul funcției este.

Function Ifs(pret_ As Integer, stoc_ As Integer) As Double
    Application.Volatile True
    Select Case pret_
        Case Is < 5 And stoc_ < 500
                    Ifs = 0.01
        Case Is < 5 And stoc_ < 1000
                    Ifs = 0.06
        Case Is < 5 And stoc_ < 1500
                    Ifs = 0.09
        Case Is < 5 And stoc_ >= 1500
                    Ifs = 0.13
        Case Is < 10 And stoc_ < 500
                    Ifs = 0.02
        Case Is < 10 And stoc_ < 1000
                    Ifs = 0.06
        Case Is < 10 And stoc_ < 1500
                    Ifs = 0.1
        Case Is < 10 And stoc_ >= 1500
                    Ifs = 0.14
        Case Is < 15 And stoc_ < 500
                    Ifs = 0.03
        Case Is < 15 And stoc_ < 1000
                    Ifs = 0.07
        Case Is < 15 And stoc_ < 1500
                    Ifs = 0.11
        Case Is < 15 And stoc_ >= 1500
                    Ifs = 0.15
         Case Is >= 15
                Select Case stoc_
                    Case Is < 500
                         Ifs = 0.04
                    Case Is < 1000
                         Ifs = 0.08
                     Case Is < 1500
                         Ifs = 0.12
                     Case Is >= 1500
                         Ifs = 0.16
                End Select
    End Select
End Function

Pentru cei care doresc să studieze mai mult macro și VBA în Excel le recomand cărțile:

 

Succes și mulțumesc pentru interes.

Blog la WordPress.com.

SUS ↑