Omplir cel·les amb el valor de la cel·la superior

 Perfectament explicat dins:

https://excelyvba.com/rellenar-celdas-vacias-con-el-valor-de-la-de-arriba/

Recordatori:

F5 / Especial / Celdas en blanco

Omplim la primera cel·la buida amb el valor de la superior.

CTRL INTRO

Suma de rangs discontinus en Excel o LibreOffice

 Vull sumar sols els valors dins els quadrats grocs (resultat 200,6)

Al rang superior l'anomeno alfa

Al rang inferior, beta

Solució errònia  =SUMA(alfa:beta) resultat = 220,6  Està incloent els valors amb fons de color blanc.

Solució correcta  =SUMA(alfa;beta) resultat = 200,6  No inclou els valors amb fons de color blanc.

Comparar dues columnes en Excel

 En Access és molt més fàcil, però si ho has de fer en Excel, caldrà fer servir una matriu:

Amb aquesta fórmula compararem A1:A21 i B1:B21 

=SI.ERROR(INDICE($A$1:$A$21;K.ESIMO.MAYOR(SI.ERROR(COINCIDIR(B1:B21;$A$1:$A$21;0);"");FILA()));"")

Abans d'aplicar la fórmula cal seleccionar el rang on l'aplicarem. Tot seguit (Molt important) polsem CTRL+MAYS+INTRO:

El resultat:

Obtenir el nom del full en una cel·la Excel (Office 365)

Amb Office365, la fórmula més fàcil seria:

=TEXTODESPUES(CELDA("nombrearchivo";A1);"]")

Si descomposem la fórmula ens dona:

=TEXTODESPUES(   PARÀMETRE-1  ;"]")

Per un paràmetre ens dona les dades després del caràcter entre cometes.

El Paràmetre 1:

=CELDA("nombrearchivo";A1)

Ens dona la ruta del fitxer consultant des d'A1.

És important determinar la cel·la, ja que si copies la fórmula de forma simultània en molts fulls, es pot embolicar si no indiques la cel·la.

El problema seria que et donaria el mateix resultat per totes les fulles del llibre, és a dir, un desastre.

Unir diferents fulls Excel en un únic full (2)

Una mica millorat fent servir constants per no oblidar-nos de dimensionar el número de fulls afectats i el número de línies a tractar. En aquest cas treballem amb 13 pàgines i la pàgina que té més línies té unes 30.

El codi:

Option Explicit
Dim MyNumPage As String
Dim MyRange As String
Dim NumeracioMyNumPage As Byte
Dim NumeracioMyRange  As String
Dim i As Byte
'Aqui declarem com a constant el número pàgines a tractar
'############################
Const TotalPage As Byte = 13
'Aqui declarem com a constant el número de línies a tractar
'############################
Const TotalLines As Byte = 30

Sub CopiarYPegar()

'Començarem a la segona línia: -(30-2) = -28
NumeracioMyRange = -(TotalLines - 2)
i = 1
For i = 1 To TotalPage
    NumeracioMyNumPage = i
        If i < 10 Then
            MyNumPage = "Page00" & NumeracioMyNumPage
        Else
            MyNumPage = "Page0" & NumeracioMyNumPage
        End If
    Parche_01
Next i
End Sub

Sub Parche_01()
    Sheets(MyNumPage).Select
    ActiveSheet.Range("A2:P" & TotalLines).Select
    Selection.Copy
    Sheets("Hoja1").Select
    NumeracioMyRange = NumeracioMyRange + TotalLines
    MyRange = "B" & NumeracioMyRange
    Range(MyRange).Select
    ActiveSheet.Paste
End Sub

#####################

Cal destacar la forma de fer referéncia a un rang fent servir una constant:

    ActiveSheet.Range("A2:P" & TotalLines).Select

Unir diferents fulls Excel en un únic full

 He fet una importació de dades des de PDF i se m'han carregat 13 fulls.

Cada full s'ha creat com a Page001, Page002, Page003.... fins a Page013

Vull carregar unir totes les dades dins Hoja 1

Cada pàgina de dades té menys de 30 línies.

No vull tocar la primera línia de Hoja1 perquè alli vull escriure una capçalera.

No vull tocar la primera columna de Hoja1 perquè allí vull tenir una columna d'ordre (1,2,3,....)

L'opció CONSOLIDAR té certes restriccions, així que ho faré amb VBA:

Option Explicit

Dim MyNumPage As String

Dim MyRange As String

Dim NumeracioMyNumPage As Byte

Dim NumeracioMyRange  As String

Dim i As Byte

Sub CopiarYPegar()

NumeracioMyRange = -28

i = 1

For i = 1 To 13

    NumeracioMyNumPage = i

        If i < 10 Then

            MyNumPage = "Page00" & NumeracioMyNumPage

        Else

            MyNumPage = "Page0" & NumeracioMyNumPage

        End If

    Parche_01

Next i

End Sub

Sub Parche_01()

    Sheets(MyNumPage).Select

    ActiveSheet.Range("A2:P30").Select

    Selection.Copy

    Sheets("Hoja1").Select

    NumeracioMyRange = NumeracioMyRange + 30

    MyRange = "B" & NumeracioMyRange

    Range(MyRange).Select

    ActiveSheet.Paste

End Sub

Funció DESREF en Excel o de com fer TRANSPONER a lo bèstia

 

Càlcul lletra DNI / NIE amb funció en Excel

Una solució eficaç dins www.hojasdecalculoexcel.com

http://www.hojasdecalculoexcel.com/2008/04/calcular-la-letra-del-nif-o-del-dni.html

Sols he afegit la declaració "Dim Lista_de_letras as String" ja que tinc activat el Option Explicit.

Dins un mòdul escrius aquest codi:

Function LetradelNIF(NIF As Long)

'Controlamos que no sea un número mayor de 8 cifras

If NIF = 0 Or NIF > 99999999 Then

letradelNif = "NIF incorrecto"

Else

'Tenemos una constante que contiene las posibles letras que puede tener un NIF

Dim Lista_de_letras as String

Lista_de_letras = "TRWAGMYFPDXBNJZSQVHLCKE"

'Cogemos el NIF, lo dividimos entre 23 y nos quedamos con el resto. Luego

'al resto le sumamos 1, y obtenemos de la lista de letras, la que corresponda

'con ese número de NIF

letradelNif = Mid(Lista_de_letras, (NIF Mod 23) + 1, 1)

End If

End Function

Pel NIE sols cal canviar la X inicial per 0, la Y per 1 i la Z per 2.

Obtenir el valor de la darrera cel·la d'una columna

 

Fem servir la fórmula 

=BUSCAR(MAX(D:D)+1;(D:D))

per trobar la darrera cel·la de la columna D

Funció SI amb dates en EXCEL

 Vull que si la data és superior al 15/10/2021 informi NOVA i si és anterior, informi ANTIGA.

El primer que fariem seria =si(A2>15/10/2021;"nova";antiga")... però no xuta. Les dates van al seu rotllo.

La solució és 

=SI(A2>=FECHANUMERO("15/10/2021");"NOVA";"ANTIGA")

Si tens la configuració en català, fes servir la funció VALDATA

Símbol de la mitjana aritmètica en Excel

Amb Insertar / Símbolo / Font: Ms Reference Sans Serif / Subconjunto: Área de uso privado

Tag: Insertar símbolo media aritmética en Excel

Canviar el nom de les fulles excel

Explicat molt bé a http://www.todoexcel.com/foro-excel/macros/cambiar-nombre-hojas-segun-una-lista-t15384.html

En l'exemple el full actiu és Hoja4 i començarà a canviar des de la fulla 1 fins a la 3

A la fulla 1 li canviarà el nom pel que possi en Hoja4.A2

A la fulla 2 li canviarà el nom pel que possi en Hoja4.A3

A la fulla 3 li canviarà el nom pel que possi en Hoja4.A4

El resultat serà que Hoja1 es dirà Alfa, Hoja2 serà Beta i Hoja3 quedarà com a Gamma.

El codi que cal escriure dins un mòdul:

Sub CambiarNombre()

Dim i As Integer

For i = 1 To 3

Sheets(i).Name = Sheets("Hoja4").Range("A1").Offset(i, 0).Value

Next i

End Sub

Unir diferents llibres de Excel en un de sol

Join different excel files

Unir diferentes libros excel en uno solo

Hi ha un llibre de macros que molt fàcilment uneix diferents llibres excel en un de sol:

http://analisisydecision.es/trucos-excel-unir-varios-excel-en-uno/#comment-17985

De moment el full no captura el nom del llibre, però es molt útil.

Esta força ben explicat.

Funció SI en excel amb anidacions

L'objectiu és determinar a quin quadrant pertany un angle. Caldrà doncs aplicar un SI (o if) amb tres nivells. El quart no és necessari ja que serà el resultant de no aplicar les anteriors condicions.
En el nostre cas els angles van de 0 a 180 graus pels quadrants 1 i 2 i de 0 a -180 pels quadrants quatre i tres. En l'exemple queda més clar.






















La funció aplicada al valor en A2 és

=SI((A2>=0)*Y(A2<=90);1;SI((A2>90)*Y(A2<=180);2;SI((A2<0)*Y(A2>=-90);4;3)))

El resultat:

Però l'important d'aquesta entrada és explicar la formula EXCEL d'anidacions de if.

De fet és tan simple com mantenir la coneguda estructura =SI(CONDICIO;ResultatA;ResultatB)

El truc està en ampliar poc a poc els resultats. Amb una mica de paciència aquest gràfic penso que ho deixa bastant clar. Cal fixarse que sols afegim el símbol = la primera vegada.

Per afegir el Y que uneix dos condicions fem servir * abans de la Y.

En qualsevol cas, això és matar mosques a canonades. La gràcia del si (o del if) es que evalua fins que troba un resultat. Es a dir, si en el primer "pis" troba una solució, ja no continua. per tant, la forma correcta seria:

=SI(A2>90;2;SI(A2>0;1;SI(A2<-90;3;SI(A2<0;4;0))))

En aquest exemple millorat, si el angle és 0 ho marquem com a quadrant 0.

No funciona seno en Excel

Doncs sí, sí que funciona, el que pasa es que ho han fet de la forma més rocambolesca possible.

Si agafem qualsevol taula impresa de senos i cosenos i mirem, per exemple el valor de 25º tenim que ha de ser:

SENO 25º = 0,4226

COSENO 25º = 0,9063

En Excel, si en la casella A1 tenim 25 i fem

=SENO(25) o =SENO(A1) ens dona -0,1323

la solució és

=SENO(RADIANES(25)) o =SENO(RADIANES(A1))

i ens dona 0,4226

El mateix pel que fa al coseno.

No puc executar una macro del llibre personal

He gravat una macro en el llibre personal, però no puc accedir...

La causa. Estava deshabilitat.

La solució (trobada a http://grupos.emagister.com/debate/macros_en_libro_personal_no_funcionan_en_los_demas_libros/6610-232039) consisteix en habilitar l'accès a aquest llibre:

Botó Office / Opciones de Excel / Complementos / Administrar / Elementos deshabilitados /Habilitar

Macros en Excel

Deuria ser bastant simple i, de fet ho és, però els de Excel treballan incansablement per fer semblar difícil el que és fàcil...

Creem una macro gravant-la i el primer problema és on la deixem.

a) Si la deixem en el llibre actual ens dirà que no és un llibre de macros. Per fer-ho no pot ser xlsx. Ha de ser xlsm. En qualsevol cas, si recarreguem el full amb dades noves esborrarem la macro. Xungo.

b) En un altre llibre (també ha de ser xlsm). Es una opció.. per executar la macro ens caldrà que estiguin oberts el nostre xlsx i aquest altre xlsm.

c) Jo diria que és la correcta. La deixem en un llibre personal de macros. Aquest llibre queda ubicat en una ruta en concret però no cal que estigui obert per executar sobre un xlsx qualsevol. El llibre personal de macros presenta l'extensió xlsb.

Però si volem afegir una nova macro, on el trobem? Cercant -amb les condicions habituals- no el trobarem. Es troba dins
DISC:\Documents and Settings\USUARI\Datos de programa\Microsoft\Excel\XLSTART

on DISC és C o E o ... i USUARI el teu nom d'usuari.

Exemple:



Un cop localitzat, si l'obrim veurem que no hi ha cap full obert. Si anem a Programador/Macros i la seleccionem, al polsar sobre modificar ens dona un missatge críptic:

"No se puede modificar una macro que se encuentra en un libro oculto. Muestre el libro con el comando Mostrar."

Evidentment, del comandament Mostrar ni una pista. Però encara hi ha esperança.

Si anem a Programador/Visual Basic ens apareixerà una estructura d'arbre. Polsem sobre el mòdul i accedirem al codi de la macro:

Transponer en una única columna

El que volem es passar d'això:






a això:

Es a dir volem obtenir en una columna aquest resultats:
=A2
=B2
=C2
=D2
=E2
=A3
=B3
=C3
=D3
=E3
=A4
=B4

etc, etc.

Si arrastrem des de la primera fòrmula fins la cinquena (es a dir des de =A2 fins =E2) la primera cel·la contindrà =A7 en lloc de l'esperada =A3. Idees del tipus =A(2+1) no funcionen.

No hi ha cap forma (a nivell d'usuari) de fer servir una mena de variable dins la fòrmula de referència.

L'opció TRANSPONER manté la capçalera en la primera columna, però el que volem es tenir-ho en una única columna.

Una possible solució és montar una seqüència d'aquest tipus:

i finalment fer servir la funció INDIRECTO contra el resultat calculat.

La funció INDIRECTO ens donarà com a resultat les dades que hi han en la casilla corresponent. En aquest cas:

=INDIRECTO(D11) farà que obtinguem el que hagi en A2 i així consecutivament.

Modificar un rang en Excel 2007

El pitjor del cas es que li han canviat el nom. El que abans era un rang ara és un "Nombre". Per tant, no us baralleu cercant com modificar un rang. El que cal fer és modificar un "Nombre".

Primer, com definir-ho:

Anem a Formulas i polsem l'opcio "Administrador de nombres"













A partir d'aqui, com sempre... botó Nuevo per afegir, botó Eliminar per eliminar i sobre tot, botó Editar -polsant sobre el rang que ens interessi- per canviar-ho.

Sols cal fer esment a que per acceptar el canvi caldrà polsar el botonet d'abaix a l'esquerra:

Obrir excel en dues finestres independents

Obrim un Word i desprès un altre i, cap problema, cadascú està en una finestra diferent.
Fem el mateix amb Excel i s'ens obre en la mateixa finestra....

Solució trobada a http://www.informatica-hoy.com.ar/trucos-consejos-computadora/Como-abrir-documentos-de-Excel-en-ventanas-distintas.php per xlsx:

- Aprieta [Windows] + [R]: se abrirá un cuadro con la función EJECUTAR - Escribe REGEDIT y pulsa intro

Per xlsx - Despliega la clave HKEY_CLASSES_ROOT \ Excel. Sheet.12 \ Shell \ Open - Suprime la clave DDEEXEC haciendo un clic arriba con el botón derecho del mouse y eligiendo Suprimir - Entre en la clave COMMAND - Observa la presencia de un valor "(por defecto)" y de un valor "command" - Haz clic con el botón derecho en el valor "command" y elige "Suprimir" - Haz doble clic sobre el valor "(por defecto)" - Añade un espacio y después "%1" (con las comillas, como aquí) al final de línea para que el dato se parezca a: "C:\Program Files\Microsoft Office\Office12\EXCEL.EXE" /e "%1"

Si no volem tocar el registre, un usuari, en la mateixa pàgina ofereix una solució provisional però molt fàcil:

Obrim un Excel amb l'opció Nuevo o encara més fàcil Inicio-Excel
Fem el mateix amb un altre.

Ara tenim dos llibres Excel oberts sense res.

En el primer polsem la rodona Office i obrir. Escollim un full amb dates.
Fem el mateix amb el segon.

Ja tenim dos llibres Excels oberts de forma independent.