photosdematures.com

Llista de les 10 millors fórmules i funcions avançades d'Excel

Podeu descarregar aquesta plantilla de fórmules avançades d'Excel aquí: plantilla de fórmules avançades d'Excel

# 1 - Fórmula VLOOKUP a Excel

Aquesta funció excel avançada és una de les fórmules més utilitzades a Excel. Es deu principalment a la simplicitat d’aquesta fórmula i a la seva aplicació a la recerca d’un determinat valor d’altres taules que té una variable comuna en aquestes taules. Suposem que teniu dues taules que contenen detalls sobre el nom i el nom dels empleats d’una empresa, amb la identificació de l’empleat com a columna principal. Voleu obtenir el sou de la taula B de la taula A.

Podeu utilitzar VLOOKUP com es mostra a continuació.

Resultarà a la taula següent quan apliquem aquesta fórmula Excel avançada en altres cel·les de la columna Salari dels empleats.

Arrossegueu la fórmula a la resta de cel·les.

Hi ha tres grans delimitacions de VLOOKUP:

1. No podeu tenir cap columna principal a la dreta de la columna per a la qual vulgueu omplir el valor d'una altra taula. En aquest cas, la columna Salari dels empleats no pot estar abans de la identificació dels empleats.
2. En el cas de valors duplicats a la columna primària de la taula B, el primer valor s'omplirà a la cel·la.
3. Si inseriu una nova columna a la base de dades (Ex. Inseriu una nova columna abans del salari dels empleats a la taula B), la sortida de la fórmula pot ser diferent en funció de la posició que heu esmentat a la fórmula (en el cas anterior, la sortida estaria en blanc)

# 2 - Fórmula INDEX a Excel

Aquesta fórmula Excel avançada s’utilitza per obtenir el valor d’una cel·la en una taula determinada especificant el nombre de files, columnes o totes dues. Ex. Per obtenir el nom d'un empleat a la cinquena observació, a continuació es mostren les dades.

Podem utilitzar la fórmula Excel avançada a continuació:

Es pot utilitzar la mateixa fórmula INDEX per obtenir valor al llarg de la fila. Quan s’utilitzen els números de fila i de columna, la sintaxi s’assemblaria a una cosa així:

La fórmula anterior retornaria "Rajesh Ved".

Nota: Si inseriu una altra fila a les dades de la cinquena fila, la fórmula retornaria "Chandan Kale". Per tant, la sortida dependrà dels canvis que es produeixin a la taula de dades al llarg del temps.

# 3 - Fórmula MATCH a Excel

Aquesta fórmula avançada d'Excel retorna el número de fila o columna quan hi ha una coincidència de determinada cadena o número en l'interval donat. A l'exemple següent, intentem trobar la posició de "Rajesh Ved" a la columna Nom de l'empleat.

La fórmula seria la següent:

La funció MATCH tornaria 5 com a valor.

El tercer argument s'utilitza per a la coincidència exacta. També podeu utilitzar +1 i -1 segons els vostres requisits.

Nota: Es pot combinar INDEX i MATCH per superar la limitació de VLOOKUP.

# 4 - Fórmula IF I a Excel

Hi ha molts casos en què cal crear banderes basades en algunes restriccions. Tots coneixem la sintaxi bàsica d’IF. Utilitzem aquesta funció avançada Excel IF per crear un camp nou basat en algunes restriccions en un camp ja existent. Però, què passa si hem de tenir en compte diverses columnes en compte mentre es crea un senyalador. Ex. En el cas següent, volem marcar tots els empleats que tinguin un salari superior a 50 K però un identificador d’empleat superior a 3.

Utilitzaríem IF I en aquests casos. A continuació, trobareu la captura de pantalla del mateix.

Tornaria el resultat com a 0.

Podem tenir moltes condicions o restriccions per crear un indicador basat en diverses columnes mitjançant AND.

# 5 - Fórmula SI O en Excel

De la mateixa manera, també podem utilitzar la funció OR en excel en lloc de I si només necessitem complir una de les condicions de molts.

En els casos anteriors, si alguna de les condicions es compleix, tindrem la cel·la emplenada com a una altra 0. També podem substituir 1 o 0 per algunes cadenes amb cometes dobles ("").

# 6 - Fórmula SUMIF a Excel

En algunes anàlisis, és possible que hagueu de filtrar algunes observacions quan apliqueu la funció suma o recompte. En aquests casos, aquesta funció avançada SUMIF excel en Excel està al nostre rescat. Filtra totes les observacions en funció de certes condicions donades en aquesta avançada fórmula Excel i les resumeix. Ex. Què passa si volem conèixer la suma dels sous només dels empleats que tinguin un DNI superior a 3.

Aplicant la fórmula SUMIFS:

La Fórmula retorna els resultats com a 322000.

També podem comptar el nombre d’empleats de l’organització que tinguin un identificador d’empleat superior a 3 quan fem servir COUNTIF en lloc de SUMIF.

# 7: CONCATENEU la fórmula a Excel

Aquesta funció avançada d’excel és una de les fórmules que es poden utilitzar amb diverses variants. Aquesta fórmula Excel avançada ens ajuda a unir diverses cadenes de text en una cadena de text. Per exemple, si volem mostrar l’identificador de l’empleat i el nom de l’empleat en una sola columna.

Podem utilitzar aquesta fórmula CONCATENAT només per fer-ho.

La fórmula anterior donarà lloc a "1Aman Gupta".

Podem tenir una variant més posant un sol guionet entre ID i NAME. Ex. CONCATENAR (B3, ”-“, C3) donarà lloc a “1-Aman Gupta”. També ho podem fer servir a VLOOKUP quan LOOKUP en valor Excel és una barreja de més d’una variable.

# 8 - Fórmula ESQUERRA, MIDA i DRETA a Excel

Podem utilitzar aquesta fórmula Excel avançada si volem extreure una determinada subcadena d’una cadena determinada. Les fórmules esmentades es podrien utilitzar en funció dels nostres requisits. Ex. Si volem extreure els primers 5 caràcters del nom de l’empleat, podem utilitzar la fórmula ESQUERRA en excel amb el nom de la columna i el segon paràmetre com a 5.

La sortida es dóna a continuació:

L’aplicació de la fórmula RIGHT a Excel també és la mateixa, és només que estaríem veient el caràcter d’una dreta de la cadena. Tanmateix, en el cas d’una funció MID en excel, hem de donar la posició inicial de la cadena de text requerida i la longitud de la cadena.

# 9 - Fórmula OFFSET a Excel

Aquesta avançada funció Excel amb una combinació d'altres funcions com SUM o AVERAGE podria ser útil per donar un toc dinàmic als càlculs. Es fa servir millor en els casos en què inserim files contínues en una base de dades existent. OFFSET Excel ens proporciona un interval on hem d’esmentar la cel·la de referència, el nombre de files i les columnes. Ex. Si volem calcular la mitjana dels primers 5 empleats de l’empresa on tenim un salari d’empleats ordenat per identificació d’empleat, podem fer el següent. El càlcul següent sempre ens donarà assalariats.

• Ens donarà la suma dels sous dels primers 5 empleats.

# 10 - Fórmula TRIM a Excel

Aquesta fórmula Excel avançada s’utilitza per netejar els espais poc importants del text. Ex. Si volem eliminar espais al principi d’un nom, el podem utilitzar utilitzant la funció TRIM en excel com es mostra a continuació:

El resultat resultant seria "Chandan Kale" sense cap espai abans que Chandan.