Acest articol face parte dintr-o serie de articole dedicate implementării de algoritmi clasici în Microsoft Excel 365.

Sursa de inspirație este site-ul: https://app.codility.com/programmers/lessons/1-iterations/binary_gap/

Doar puțină teorie

Iterațiile sau structurile de control clasice includ:

IF cu sintaxa pseudocod: dacă condiție atunci acțiune_adevăr altfel acțiune_fals
FOR cu sintaxa pseudocod: for variabilă in interval_de_valori acțiune
WHILE cu sintaxa pseudocod: while condiție acțiune

În Excel în schimb nu există nici FOR nici WHILE implementată ceea ce face oarecum diferită abordarea. O simulare a unui FOR este întâlnită uneori prin utilizarea funcției SEQUENCE() combinată cu funcția SCAN() sau REDUCE(). De exemplu dacă am avea un pseudocod de tipul: pentru fiecare număr de la 1 la 9 adunați numărul cu suma numerelor anterioare. Exemplu:

Problema supusă rezolvării este BinaryGap

Aceasta presupune transformarea unui număr zecimal în număr binar după care trebuie determinate numărul maxim de secvențe de 0 care apar în numărul binar, urmate de valoarea 1.

În exemplu este prezentat numărul 9 care în binar este 1001 și care are un număr de 2 de 0 consecutivi urmați de valoarea 1. În numărul 32 care este în binar: 100000 numărul de 0 nu este urmat de 1 ceea ce înseamnă că rezultatul va fi 0.

Problema de pe Codility oferă un set de numere de test [1..2,147,483,647]. Doar că în Excel ultimul număr zecimal care poate fi transformat în binar este numărul 511, de la 512 încolo obținem eroarea #NUM!

În imagine propuneri de rezolvare.

Menționez că localizarea pe calculatorul meu este localizarea în română ceea ce face ca separatorii de formule să fie ; (punct și virgulă) nu , (virgula).

Ca să transform numărul în binar folosesc funcția DEC2BIN(). Funcția din celula B2 este: =DEC2BIN(A2)

Dacă aș dori să văd câți de 0 sunt în total în număr în D2 am introdus funcția LEN() pentru a determina lungimea șirului rezultat în binar și SUBSTITUTE() pentru a înlocui numerele de 1 cu nimic. Funcția din D2 este: =LEN(B2)-LEN(SUBSTITUTE(B2;”0″;””)).

Dacă ar trebui să determin doar numărul maxim de 0 consecutivi atunci folosesc în E2 funcția: =MAX(SCAN(0;MID(B2;SEQUENCE(LEN(B2));1); LAMBDA(a;v; IF(v=”1″; 0; a+1))))

În care:

SCAN() scanează un array de valori rezultat prin descompunerea valoare cu valoare a numărului binar din B2 pe baza lungimii sale SEQUENCE(LEN(B2)). Funcția SCAN() are nevoie de o funcție LAMBDA pentru calculul valorii finale. În Lambda avem variabilele a – acumulator, și v- valoarea curentă. Dacă valoarea curentă este 1 ca text, pentru vă provine din MID(), atunci acumulatorul va lua valoarea 0, altfel va lua valoarea iterată cu 1. La final aplic un MAX() pentru a determina exact care este cea mai mare secvență de valori 0.

Pentru numărul 401 care în binar este 110010001 rezultatul SCAN() este:

În exemplul lui 401 voi obține valoarea finală 3.

Dacă dorim să răspundem corect la problemă și să numărăm doar valorile 0 care sunt urmate de 1 atunci formula se complică puțin.

În F2 am introdus o funcție LET() ca să pot scrie mai puține caractere. Scopul în algoritmi este să ai un cod cât mai optim și poate mai puține caractere într-o formulă / cod.

=LET(bloc; MID(B2;SEQUENCE(LEN(B2));1); zu; IF(RIGHT(B2)=”0″;0;1); cate; MAX(SCAN(0; bloc; LAMBDA(a;v; IF(v=”1″; 0; a+1)))); conc; CONCAT(zu;”-„;cate); IF(zu=0;0;cate))

În această funcție am introdus variabila zu pentru a determina dacă la finalul șirului este sau nu valoarea 1. Această abordare are în schimb o eroare de logică pentru că nu tratează excepțiile în care avem totuși valori 0 urmate de 1 în numărul binar și se finalizează cu valoarea 1. Exemplu este numărul 486 care se termină cu 0 dar are 2 de 0 în interior urmați de valoarea 1.

Variantele cu emulare de FOR sunt interesante și pot duce la soluții destul de frumoase. Doar că în rezolvarea problemelor mai complexe de Excel important este să ai formule cât mai reduse ca dimensiune pentru a asigura un optim de execuție dar și pentru a face mai ușor depanarea formulelor. În acest sens propun o metodă de rezolvare fără iterații explicite.

Formula din B46 este: =MAX(LEN(DROP(TEXTSPLIT(B46;1);;-1)))

În care:

TEXTSPLIT() – împarte numărul binar în funcție de fiecare apariție a valorii 1 rezultând vectori cu număr de coloane diferite:

În cazul în care numărul splitat nu conține nici un 0 vom vedea o listă de celule vide.
În cazul în care în ultima coloană a fiecărui vector avem valoarea 0 înseamnă că secvența de 0 nu este urmată de valoarea 1.

DROP() – este utilizat pentru a elimina excepția când la final-ul vectorului avem valoarea 0. Elimină ultima celulă din rezultat.

LEN() – numără pentru fiecare celulă rămasă în vector cât de lungă este secvența de 0 rezultatul intermediar fiind de forma:

MAX() extrage din lista de valori valoarea maximă a numărului de 0 consecutivi urmați de valoarea 1.

În Google Spreadsheets nu există funcția TEXTSPLIT() dar există o funcție SPLIT() care nu funcționează asemănător cu cea din Excel.

Cam asta ar fi. Sper să fie util cuiva.

Zilele acestea sunt foarte aglomerate pe la serviciu. O mulțime de cerințe și problemuțe care trebuie rezolvate cât mai pe loc posibil. Dar între zeci de emailuri a apărut unul care mi-a atras atenția.

Păi putem să refuzăm noi un ajutor în legătură cu Excelul? Păi nu. Așa că lăsăm totul deoparte și ne apucăm de treabă.

Descriere problemei

Se dorește ca în fiecare dimineață, datele agregate dintr-un fișier Excel să fie trimise prin email la o anumită oră către o anumită adresă de distribuție. Soluțiile existente pe internet sunt în mare parte cu VBA și o serie de operațiuni manuale, care implică deschiderea fișierelor. La altele nu m-am uitat, dar cred că se pot face și cu ceva automatizări din Power Automate. Dar cum nu toată lumea are subscripție de cloud, m-am gândit la Task Scheduler-ul de Windows. Și cum nu sunt nici fan VBA nici prea multe despre programare nu știu, m-am decis să abordez problema din PowerShell pe care-l știu de pe vremea când eram administrator de rețea și actualmente îl folosesc în administrarea de SharePoint.

Propunerea de soluție

Propun un exemplu de fișier Excel care conține o funcție de generare aleatorie de numere:

Funcția =RANDBETWEEN(1;49) generează numere aleatorii în cazul meu între 1 și 49 iar în C3 fac suma lor. Orice celulă se modifică se schimbă și numerele și suma lor.

Scriptul de refresh silent a fișierului.

Acest script presupune că aveți deja instalat pachetul Microsoft Excel pe calculator. Cu acest script se deschide fișierul din calea specificată în secțiunea 2. După care accesează prima foaie de calcul (3) și execută refresh la date (4), salvează (5), închide fișierul (6) și Excelul (7) după care eliberează memoria de obiecte (8).

# Actualizare silent date fisier Excel
# Author: Valy Greavu 
# Date: 13.02.2024
# Se recomandă utilizarea în Task Scheduler pentru refresh fișiere din când în când la noile date.

# 1. Se crează o instanță Excel
$excel = New-Object -ComObject Excel.Application

# 2. Se deschide fișierul Excel. Atentie la calea fisierului.
$workbook = $excel.Workbooks.Open("P:\Temp\FisierExcel.xlsx")

# 3. Se activează prima foaie de lucru din Excel în care sunt datele care se actualizează. 
# Item(2) inseamna a doua foaie de lucru in ordine.
$worksheet = $workbook.Sheets.Item(1)
$worksheet.Activate()

# 4. Se realizează refresh la date din diferite surse sau se recalculează formulele și funcțiile
$workbook.RefreshAll()

# 5. Se salvează modificările
$workbook.Save()

# 6. Se închide fișierul Excel
$workbook.Close()

# 7. Se închide aplicația Excel creată la pasul 1.
$excel.Quit()

# 8. Se eliberează obiectele COM
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($worksheet) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excel) | Out-Null
Remove-Variable excel, workbook, worksheet

La fiecare rulare datele din Excel se reîmprospătează. O condiție importantă este ca acel fișier Excel să fie închis.

Scriptul de trimitere a datelor din Excel prin email

În acest script se preiau datele din fișierul Excel specificat la secțiunea 3. Înainte de preluare date ar trebui verificat dacă există sau nu instalat modulul ImportExcel în PowerShell. Dacă nu este se instalează cu linia comentată din secțiunea 1.

În acest script preiau valorile din celulele A2 și C3 (prima generată aleatoriu și suma tuturor valorilor). Specificul versiunii curente a modului Import-Excel este că trebuie să specificăm coloanele și liniile de start și de final în formatul R1C1.

În modelul meu numele foii de calcule este Sheet1.

# Preluare date din Excel și trimitere email
# Author: Valy Greavu 
# Date: 13.02.2024
# Se recomandă rulare script Refresh fisier Excel silent.ps1 înainte de trimitere email pentru actualizare date.
# Se recomandă utilizarea în Task Scheduler pentru trimitere email la o anumită dată.


# 1. Se instalează modulul Import-Excel dacă nu este deja instalat. 
# Operatiunea se realizeaza o singura data pe un calculator.
# În acest script linia de install este comentată

# Install-Module -Name ImportExcel -Scope CurrentUser -Force

# 2. Se importă modulul Import-Excel
Import-Module ImportExcel

# 3. Se specifică calea către fișierul Excel din care dorim preluarea datelor
$filePath = "P:\Temp\FisierExcel.xlsx"

# 4. Extrage valorile din anumite celule din foaia de calcul Sheet1 în acest exemplu celulele A2 și C3.
# Se pot extrage și blocuri de valori

$cellValue1 = (Import-Excel -Path $filePath -StartColumn 1 -StartRow 2 -NoHeader -EndRow 2 -EndColumn 1 -WorksheetName Sheet1).P1 # Celula A2
$cellValue2 = (Import-Excel -Path $filePath -StartColumn 3 -StartRow 3 -NoHeader -EndRow 3 -EndColumn 3 -WorksheetName Sheet1).P1 # Celula C3


# 5. Se construiește mesajul de email
# În acest exemplu trimit un email din Office 365 către Outlook.com

$subject = "Valori din Excel"
$body = "Buna ziua,`n`nValoarea din A2: $cellValue1`nSuma numerelor din C3: $cellValue2`n`nO zi buna!"
$to = "valy.greavu@outlook.com"
$emailAddress = "valy.greavu@feaa.uaic.ro"


# 6. Se specifică datele serverului SMTP al Outlook.com și portul
# Trebuie văzut la fiecare serviciu care sunt specificațiile de SMTP
# Trebuie văzut dacă serviciul utilizat suportă modern authentication (two factor authentication) pentru a compune credențialele de acces

$smtpServer = "smtp.office365.com"
$smtpPort = 587


# 7. În cazul în care nu automatizăm prin Task Scheduler putem folosi Prompt pentru parolă
$securePassword = Read-Host -Prompt "Introdu parola" -AsSecureString

# 7.1. Operatiune nerecomandata pentru ca salveaza parola in script in cazul in care dorim sa utilizam cu Task Scheduled dintr-un mediu securizat
# $password = "*************" # se trece parola în clear sau
# $credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailAddress, (ConvertTo-SecureString -String $password -AsPlainText -Force)

# 7.2. Crearea obiectului de tip credențiale de autentificare pentru serviciul de email specificat.
# O altă verrsiune non-auto este să folosim comanda Get-Credential
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $emailAddress, $securePassword


# 8. Trimiterea emailului. 
# În cazul în care avem mai multe adrese la care trebuie să trimitem putem să le importăm dintr-un CSV și să folosim foreach
Send-MailMessage -To $to -From $emailAddress -Subject $subject -Body $body -SmtpServer $smtpServer -Port $smtpPort -UseSsl -Credential $credential

# Final script

Problemele mai mari care pot apărea sunt legate de serviciul de email utilizat pentru trimiterea datelor și tipul de autentificare suportat de SMTP. În exemplul meu folosesc un cont de Office 365 pentru a trimite datele. Sunt mai multe metode de a obține acel $credential corect, în funcție de furnizorul de email. Unele sisteme suportă inclusiv two factor authetication.

Personal nu recomand secțiunea 7.1. de salvare a parolei direct în script, dar este o opțiune pentru automatizare mai ales prin Task Scheduler.