Excel: How to add a formula to a comment

Nu de multe ori avem nevoie de a folosi formule in comentariile Excel.

Dar, daca avem cu adevarat nevoie de a afisa un comentariu bazandu-ne pe o formula, atunci singura metoda este sa utilizam un macro.

Exemplu de problema:

Add formula and pictures to comments

Avem o listă de echipamente și o dată de achiziție. Dorim ca în celula cu data să ni se afișeze mesaj cu numărul de zile trecut de la achiziție.

Pentru acest lucru trebuie să stabilim întâi un comentariu vid pentru E3 și E4 apoi creăm următorul macro:

Sub DateCmnt()
'
' DateCmnt Macro
' Show a comment to a cell using a calculated value.
'
' Keyboard Shortcut: Ctrl+q
'
    Dim Res1 As Double
    Res1 = (Range("A7").Value - Range("E3").Value)
    Range("E3").Comment.Text Text:="Au trecut: " & Res1 & " zile de la achizitie"
    
    Res1 = (Range("A7").Value - Range("E4").Value)
    Range("E4").Comment.Text Text:="Au trecut: " & Res1 & " zile de la achizitie"

Range("E4").Comment.Shape.Fill.UserPicture ("P:\Avatar2.jpg")
Range("E3").Select End Sub

Res1 este variabila în care se stochează diferența de zile dintre data achizției și data curentă stocată în A7.

În momentul apăsării combinației de comenzi Ctrl+q se actualizează comentariul cu valoarea formulei.

Dacă doriți să adăugați o imagine pe un comentariu… sau un grafic, sau un shape acest lucru se poate realiza cu instrucțiunea din VBA:

 Range("E4").Comment.Shape.Fill.UserPicture ("P:\Avatar2.jpg")

Sper să vă fie util.

 

Acest articol este republicat și adaptat pe un exemplu economic.

Active Directory, Exchange si PowerShell

 

PowerShell este un instrument din ce în ce mai utilizat. Scriptul următor poate fi folosit pentru crearea userilor în Active Directory şi trimiterea unui e-mail inţial de informare despre anumite resurse de access.

Scriptul trebuie salvat şi rulat cu drepturi administrative pe serverul de e-mail (Exchange 2007) care ar trebui să aibă instalat și PowerShell-ul. Din punct de vedere managerial, NU este corect ca parola utilizatorului să fie trimisă prin e-mail. Alte comentarii în cadrul scriptului.

O varianta în VBS poate fi găsită la adresa: http://searchwindevelopment.techtarget.com/tip/0,289483,sid8_gci1086268,00.html dar se foloseste doar pentru crearea contului şi a mail box-ului nu şi trimiterea de e-mail. Consider utilizarea PowerShell mult mai simplă şi mai uşor de utilizat.

Copiaţi scriptul de mai jos în Notepad sau alt editor de text şi schimbaţi valorile constantelor. Lansarea scriptului: ./psADAccCreate-MailSend.ps1 <prenume> <nume>

***** psADAccCreate-MailSend.ps1 ***** 
#  Creare cont de utilizator in AD,           * 
#  adresa mail in Exchange 2007 si            * 
#  trimitere email                            * 
#                                             *    
#  Creat de: Valy Greavu                      * 
#  09/11/2008                                 * 
# ************************************** 

# Scriptul trebuie executat pe serverul de e-mail cu rol de mailbox 
# Preluarea parametrilor de rulare: Nume, Prenume, Parola 

param ([string] $Prenume, [string] $Nume) 

# Definirea constantelor: 
$Domain = "feaa.uaic.ro" #Trebuie specificat numele complet de DNS
$ou = $Domain + "/Utilizatori/Studenti" #Este specificata toata calea incepind de la numele domeniului 
$MailDB = "MAIL\StudentStore\StudentsDB" #Se modifica in functie de numele serverului si a bazelor de date specifice. 
$SenderAdmin = "valy.greavu@feaa.uaic.ro" #Adresa de mail a adminului. 
$SMTPServer = "mail.feaa.uaic.ro" 

# Verificarea datelor introduse 
if ($Prenume -eq "") 
  { 
    write-host "Prenumele nu a fost introdus!" -foregroundcolor Red 
    write-host "Exemplu utilizare: ./psADAccCreate-MailSend.ps1 <prenume> <nume>" -foregroundcolor Red    
  } 
elseif ($Nume -eq "") 
  { 
    write-host "Numele nu a fost introdus!" -foregroundcolor Red 
    write-host "Exemplu utilizare: ./psADAccCreate-MailSend.ps1 <prenume> <nume>" -foregroundcolor Red    
  } 
else 
  { 
    write-host "Incepe crearea contului pentru:" $Prenume $Nume -foregroundcolor Green    
# Crearea contului si a adresei de mail 
    # Parola trebuie introdusa in aceasta etapa 
    # Parola nu poate fi trecuta in script pentru ca este incompatibila cu System.Security.SecureString 
    # ResetPasswordOnNextLogon trebuie sa fie $false pentru ca utilizatorul sa se poata conecta la sistemul de mail inainte de a isi schimba parola. 
    # Daca acesta se va loga pentru prima data pe un calculator din domeniu atunci va trebui parametrul trebuie setat pe $true 

    New-Mailbox -Name "$Prenume $Nume" -Alias $Prenume"."$Nume -OrganizationalUnit $ou -UserPrincipalName $Prenume"."$Nume"@"$Domain -SamAccountName $Prenume"."$Nume -FirstName $Prenume -LastName $Nume -ResetPasswordOnNextLogon $false -Database $MailDB 
# Comanda New-Mailbox trebuie scrisă pe o singură linie
    write-host "Am creat contul:" $Prenume $Nume -foregroundcolor Green    

# Compunere e-mail initial 
    $Destinatar = $Prenume+"."+$Nume+"@"+$Domain 
    $Subiect = "Bun venit in reteaua :"+$Domain+"!" 
# Corp mesaj scris linie cu linie. 
    # Combinatia `r`n face trecerea pe rindul urmator in continutul mailului. 
    $bodyln1 = "`r`Salut "+$Prenume + "`r`n" 
    $bodyln2 = "`r`Te informam ca adresa ta de e-mail: " + $Destinatar + " poate fi accesata prin Outlook sau prin OWA la adresa: https://"+$SMTPServer+"/owa. `r`n" 
    $bodyln3 = "Te rugam sa respecti politicile de securitate si regulamentele afisate in sistemul de Intranet accesibil la adresa: http://Intranet/. `r`n"
    $bodyln4 = "`r`Pentru a schimba parola de acces in retea te rugam sa folosesti combinatia de taste Ctrl+Alt+Del sau legatura de pe prima pagina din Intranet.`r`n" 
    $bodyln5 = "Pentru orice fel de probleme va rugam sa va adresati departamentului de Help Desk la adresa de mail: support@"+$Domain+".`r`n" 
    $bodyln6 = "Cu stima,`r`n <em>Departamentul IT</em>.`r`n" 

    $body = $bodyln1 + $bodyln2 + $bodyln3 +$bodyln4 +$bodyln5 + $bodyln6 

    $msg = new-object System.Net.Mail.MailMessage $SenderAdmin, $Destinatar, $Subiect, $body 

# Trimitere email 
    $client = new-object System.Net.Mail.SmtpClient $SMTPServer 
    $client.credentials = [System.Net.CredentialCache]::DefaultNetworkCredentials 
    $client.Send($msg) 
    write-host "Am trimis mail:"  -foregroundcolor Yellow 
    write-host "Terminat!"  -foregroundcolor Green 
  } 

# **** EOF() ****

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, „Courier New”, courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }

 

Firul de execuţie:

1. Lansare PowerShell cu drepturi administrative şi navigare în directorul de scripturi:

2. Rulare comanda în mod eronat:

3. Rulare comanda corect:

4. Mail initial:

Trebuie retinut ca marcajele HTML sunt interpretate ca si text dar adresele HTTP, HTTPS sunt interpretate corect.

 

Acest articol este o republicare din vechiul meu blog.

MaxIF() in Excel?!

Mai zilele trecute am avut o întrebare la un curs la care nu am știut să răspund: se poate face MaxIf() sau MinIf() în Excel? Am răspuns nonșalant că nu există astfel de funcții implementate nativ, dar dacă cineva s-a gândit la această posibilitate, sigur s-au mai gândit încă 100 de oameni la același lucru până la acel moment.

Concret:

Se dă tabelul cu date:

A B C D E F
1 Localitate Client Produs Cantitate Pret Unitar Valoare
2 Iasi SC Alfa SRL Ciocolată

150

2,25

337,50

3 Iasi SC Alfa SRL Biscuiți Oșteni

200

1,30

260,00

4 București SC Omega SRL Ciocolată

250

2,30

575,00

5 București SC Omega SRL Biscuiți Obișnuiți

300

1,25

375,00

6 București SC Vega SRL Biscuiți Obișnuiți

325

1,20

390,00

 

Să se determine valoarea maximă a valorilor vândute pentru localitatea București.

Pentru a rezolva problema vom folosi tot funcția MAX dar într-un format diferit decât cel cunoscut de majoritatea:

=MAX((A2:A6="București")*(F2:F6))

în care: A2:A6 este blocul de căsuțe pe care se află condiția, F2:F6 este blocul de căsuțe pentru care trebuie să obținem valoarea.

FOARTE IMPORTANT: după ce am scris formula este esențial să apăsăm combinația de taste: <CTRL>+<SHIFT>+<ENTER> în loc de <Enter> simplu.

După ce apăsăm combinația de taste de mai sus, funcția scrisă se transformă în:

{=MAX((A2:A6="București")*(F2:F6))}

Această transformare prin adăugarea acoladelor este specifică funcțiilor pentru grupuri de date sau blocuri de celule. Foarte interesantă este logica funcției dacă facem evaluarea formulei.

MaxIF în Excel - Evaluare formula

Dacă dorim să adăugăm criterii suplimentare, fiecare se adaugă cu simbolul asterix (*). De exemplu, dacă dorim să aflăm valoarea maximă a valorilor vândute clienților din București pentru produsul Biscuiți Obișnuiți trebuie să scriem formula:

=MAX((A2:A6="București")*(C2:C6="Biscuiți Obișnuiți")*(F2:F6))

urmată de apăsarea combinației de taste <Ctrl>+<Shift>+<Enter>

SumIF-ul dacă nu știți să-l utilizați puteți să-l înlocuiți cu un SUM() simplu care să respecte aceeași sintaxă. Exemplu: Dorim să însumăm valoarea tranzacțiilor pentru clienti din localitatea Iasi. Formula va fi: =SUM((A2:A6="Iasi")*(F2:F6)), urmată de <Ctrl>+<Shift>+<Enter>.

Succes și spor la calculat!

 

Sursa de inspirație: http://www.dailydoseofexcel.com/archives/2004/06/29/maxif-minif-functions/

Blog la WordPress.com.

SUS ↑