Como conxelar unha cela nunha fórmula de Excel

Moitas veces, os usuarios enfróntanse á necesidade de fixar unha cela nunha fórmula. Por exemplo, ocorre nas situacións nas que se quere copiar unha fórmula, pero para que a ligazón non se mova cara arriba e abaixo o mesmo número de celas que se copiou desde a súa localización orixinal.

Neste caso, pode corrixir a referencia da cela en Excel. E isto pódese facer de varias maneiras á vez. Vexamos máis de cerca como conseguir este obxectivo.

Que é unha ligazón de Excel

A folla está formada por células. Cada un deles contén información específica. Outras celas poden usalo nos cálculos. Pero como entenden de onde obter os datos? Axúdalles a facer ligazóns.

Cada ligazón designa unha cela cunha letra e un número. Unha letra representa unha columna e un número representa unha fila. 

Hai tres tipos de ligazóns: absolutas, relativas e mixtas. O segundo está configurado por defecto. Unha referencia absoluta é aquela que ten un enderezo fixo tanto dunha columna como dunha columna. En consecuencia, mixto é aquel no que se fixa unha columna ou unha fila separadas.

O 1 método

Para gardar os enderezos de columna e fila, siga estes pasos:

1. Fai clic na cela que contén a fórmula.
2. Fai clic na barra de fórmulas para a cela que necesitamos.
3. Prema F4.

Como consecuencia, a referencia da cela cambiará a absoluta. Pódese recoñecer polo característico signo de dólar. Por exemplo, se fai clic na cela B2 e despois fai clic en F4, a ligazón terá este aspecto: $B$2.

Que significa o signo de dólar antes da parte do enderezo por cela?

1. Se se coloca diante dunha letra, indica que a referencia da columna segue sendo a mesma, sen importar onde se moveu a fórmula.
2. Se o signo de dólar está diante do número, indica que a cadea está fixada. 

O 2 método

Este método é case o mesmo que o anterior, só tes que premer F4 dúas veces. por exemplo, se tivésemos a cela B2, despois pasará a ser B$2. En palabras sinxelas, deste xeito conseguimos arranxar a liña. Neste caso, a letra da columna cambiará.

É moi cómodo, por exemplo, nas táboas nas que precisa mostrar o contido da segunda cela dende a parte superior na cela inferior. En lugar de facer tal fórmula moitas veces, basta con arranxar a fila e deixar que a columna cambie.

O 3 método

Este é completamente o mesmo que o método anterior, só tes que premer a tecla F4 tres veces. Entón só a referencia á columna será absoluta e a fila permanecerá fixa.

O 4 método

Supoñamos que temos unha referencia absoluta a unha cela, pero aquí foi necesario facela relativa. Para iso, preme a tecla F4 tantas veces que non hai signos $ na ligazón. Entón converterase en relativo e, cando mova ou copie a fórmula, tanto o enderezo da columna como o da fila cambiarán.

Fixación de celas para unha ampla gama

Vemos que os métodos anteriores non presentan ningunha dificultade para realizar. Pero as tarefas son específicas. E, por exemplo, que facer se temos varias ducias de fórmulas á vez, as ligazóns nas que hai que converterlas en absolutas. 

Desafortunadamente, os métodos estándar de Excel non lograrán este obxectivo. Para iso, necesitas usar un complemento especial chamado VBA-Excel. Contén moitas funcións adicionais que che permiten realizar tarefas comúns con Excel moito máis rápido.

Inclúe máis de cen funcións definidas polo usuario e 25 macros diferentes, e tamén se actualiza regularmente. Permíteche mellorar o traballo con case calquera aspecto:

1. Células.
2. macro.
3. Funcións de distintos tipos.
4. Ligazóns e matrices.

En particular, este complemento permítelle corrixir ligazóns nunha gran cantidade de fórmulas á vez. Para iso, debes realizar os seguintes pasos:

1. Seleccione un intervalo.
2. Abra a pestana VBA-Excel que aparecerá despois da instalación. 
3. Abre o menú "Funcións", onde se atopa a opción "Bloquear fórmulas".
   

   

4. A continuación, aparecerá un cadro de diálogo no que cómpre especificar o parámetro necesario. Este complemento permítelle fixar unha columna e unha columna por separado, xuntos, e tamén eliminar unha fixación xa existente cun paquete. Despois de seleccionar o parámetro necesario mediante o botón de opción correspondente, cómpre confirmar as súas accións facendo clic en "Aceptar".

Exemplo

Poñamos un exemplo para que quede máis claro. Digamos que temos información que describe o custo dos bens, a súa cantidade total e os ingresos por vendas. E estamos ante a tarefa de facer que a táboa, en función da cantidade e do custo, determine automaticamente canto diñeiro conseguimos gañar sen deducir perdas.

No noso exemplo, para iso cómpre introducir a fórmula =B2*C2. É ben sinxelo, como podes ver. É moi sinxelo usar o seu exemplo para describir como se pode corrixir o enderezo dunha cela ou a súa columna ou fila individual. 

Por suposto, neste exemplo, podes tentar arrastrar a fórmula cara abaixo usando o marcador de enchemento automático, pero neste caso, as celas cambiaranse automaticamente. Entón, na cela D3 haberá outra fórmula, onde os números serán substituídos, respectivamente, por 3. Ademais, segundo o esquema - D4 - a fórmula terá a forma = B4 * C4, D5 - do mesmo xeito, pero co número 5 e así por diante.

Se é necesario (na maioría dos casos resulta), entón non hai problemas. Pero se precisa corrixir a fórmula nunha cela para que non cambie ao arrastrar, será algo máis difícil. 

Supoñamos que necesitamos determinar os ingresos en dólares. Poñémolo na cela B7. Imos poñernos un pouco nostálxicos e indicar o custo de 35 rublos por dólar. En consecuencia, para determinar os ingresos en dólares, é necesario dividir a cantidade en rublos polo tipo de cambio do dólar.

Aquí tes como se ve no noso exemplo.

Se nós, do mesmo xeito que a versión anterior, intentamos prescribir unha fórmula, entón fallaremos. Do mesmo xeito, a fórmula cambiará á adecuada. No noso exemplo, será así: =E3*B8. Dende aquí podemos ver. que a primeira parte da fórmula se converteu en E3, e propuxémonos esta tarefa, pero non necesitamos cambiar a segunda parte da fórmula a B8. Polo tanto, necesitamos converter a referencia nunha absoluta. Podes facelo sen premer a tecla F4, só poñendo un signo de dólar.

Despois de convertermos a referencia á segunda cela nunha absoluta, quedou protexida dos cambios. Agora podes arrastralo con seguridade usando o controlador de enchemento automático. Todos os datos fixos seguirán sendo os mesmos, independentemente da posición da fórmula, e os datos non comprometidos cambiarán de forma flexible. En todas as celas, os ingresos en rublos descritos nesta liña dividiranse polo mesmo tipo de cambio do dólar.

A fórmula en si será así:

=D2/$B$7

Atención! Indicamos dous signos de dólar. Deste xeito, mostrámoslle ao programa que hai que arranxar tanto a columna como a fila.

Referencias de celas en macros

Unha macro é unha subrutina que permite automatizar accións. A diferenza da funcionalidade estándar de Excel, unha macro permítelle configurar inmediatamente unha cela específica e realizar determinadas accións en só unhas poucas liñas de código. Útil para o procesamento por lotes de información, por exemplo, se non hai forma de instalar complementos (por exemplo, se utiliza un ordenador da empresa, non un persoal).

Primeiro cómpre entender que o concepto clave dunha macro son os obxectos que poden conter outros obxectos. O obxecto Workbooks é o responsable do libro electrónico (é dicir, do documento). Inclúe o obxecto Follas, que é unha colección de todas as follas dun documento aberto. 

En consecuencia, as celas son un obxecto Cells. Contén todas as celas dunha determinada folla.

Cada obxecto está cualificado con argumentos entre parénteses. No caso das celas, referencianse nesta orde. O número de fila aparece primeiro, seguido do número da columna ou da letra (ambos os formatos son aceptables).

Por exemplo, unha liña de código que contén unha referencia á cela C5 sería así:

Cadernos de traballo ("Book2.xlsm"). Follas ("Lista2"). Celas (5, 3)

Cadernos de traballo ("Book2.xlsm"). Follas ("Lista2"). Celas (5, "C")

Tamén pode acceder a unha cela mediante un obxecto Ordenado. En xeral, preténdese dar unha referencia a un rango (cuxos elementos, por certo, tamén poden ser absolutos ou relativos), pero simplemente pódese dar un nome de cela, no mesmo formato que nun documento de Excel.

Neste caso, a liña terá este aspecto.

Cadernos de traballo ("Libro2.xlsm"). Follas ("Lista2"). Rango ("C5")

Pode parecer que esta opción é máis conveniente, pero a vantaxe das dúas primeiras opcións é que se poden usar variables entre corchetes e dar unha ligazón que xa non é absoluta, senón algo así como unha relativa, que dependerá dos resultados de os cálculos.

Así, as macros poden usarse de forma eficaz nos programas. De feito, todas as referencias a celas ou intervalos aquí serán absolutas e, polo tanto, tamén se poden corrixir con elas. É certo, non é tan conveniente. O uso de macros pode ser útil cando se escriben programas complexos cun gran número de pasos no algoritmo. En xeral, a forma estándar de usar referencias absolutas ou relativas é moito máis conveniente. 

Conclusións

Descubrimos o que é unha referencia de cela, como funciona, para que serve. Entendemos a diferenza entre as referencias absolutas e as relativas e descubrimos o que hai que facer para converter un tipo noutro (en palabras sinxelas, corrixir o enderezo ou desfixo). Descubrimos como podes facelo inmediatamente cun gran número de valores. Agora tes a flexibilidade para usar esta función nas situacións correctas.