Macro de Excel: Principio general de la serie de valores -- excel campo con vba camp Relacionados El problema

Excel Macro: general principle of processing series of values


-1
vote

problema

Español

Supongamos que los datos de origen se parecen a la columna datos . Estas son una serie de algunos valores separados por algún número de ceros (el separador puede ser cualquiera, cero en este caso se indica para simplificar la descripción del problema).

Se requiere para cada secuencia continua (serie) encontrar el valor máximo y marcar la serie completa con este valor. El resultado esperado se muestra en la columna max .

      static List listFilterCustom(List list , int age){         static bool filterByAge(ListElement element){             /* pAge = getAgeOfPerson(element); */             return pAge == age;         }         return listFilter(list , filterByAge);      } 1  

¿Cómo se resuelven las tareas similares con VBA?

gracias

DAVID

Original en ingles

Let's assume that the source data looks like the Data column. These are a series of some values separated by some number of zeros (the separator can be any, zero in this case is indicated to simplify the description of the problem).

It is required for each continuous sequence (series) to find the maximum value and mark the entire series with this value. The expected result is shown in the column Max.

Data    Max 0       0 0       0 0       0 2       9 5       9 9       9 6       9 0       0 1       1 0       0 5       8 8       8 0       0 0       0 

How are similar tasks solved with VBA?

Thanks

David

     
 
 

Lista de respuestas

1
 
vote

Me lo acercaría así

  1. Obtenga una referencia al rango de origen
  2. Copia los datos a una variante de matriz
  3. Crear una matriz de salida del mismo tamaño, inicialice a los valores predeterminados
  4. bucle que matriz
  5. Para cada fila, en cambio de separador a datos, anote la fila y restablece una variable máxima
  6. Mientras que la fila contiene datos, actualice max
  7. En el cambio de los datos al separador, actualice la matriz de salida con el valor máximo, desde la primera fila notada a la última fila de datos
  8. maneja los casos de primera / última fila.
  9. Después del bucle es una matriz de salida completa en el rango requerido
 

I'd approach it like this

  1. Get a reference to the source range
  2. Copy the data to a Variant Array
  3. Create an output array of same size, initialize to default values
  4. Loop that array
  5. For each row, on change from Separator to Data, note the row and reset a Max variable
  6. While row contains data, update Max
  7. On change from Data to Separator, update output array with Max value, from noted first row to last data row
  8. Handle first/last row cases.
  9. After loop is complete place output array at required range
 
 
0
 
vote

El algoritmo más óptimo para procesar tales secuencias es la división de la tarea en dos pases: durante la primera pasada, las secuencias se extraen de todo el conjunto de datos original y la información sobre ellos se almacena en una matriz auxiliar; Durante la segunda pasada, se realizan acciones para cada serie.

La matriz auxiliar puede ser de cualquier estructura. Por ejemplo, puede almacenar información sobre el inicio de la secuencia (el número de fila en el que comenzó la secuencia) y su longitud (o el número de fila donde se interrumpió la secuencia). Este método de almacenamiento puede guardar en gran medida la memoria, lo que es importante al procesar conjuntos muy grandes de datos de origen, pero complica enormemente el primer algoritmo de paso.

Si los recursos disponibles permiten, entonces una matriz de la misma dimensión que el conjunto de datos original se utiliza como una matriz auxiliar.

Dependiendo del tipo de secuencia y el problema que se debe resolver, se selecciona el tipo de matriz auxiliar. Por ejemplo, si solo necesita seleccionar (resaltar, colorear) secuencias en el conjunto de datos original utilizando el formato condicional, luego es suficiente una matriz booleana, en la que cada elemento simplemente muestra "pertenece / no pertenece a la serie".

Se utilizan matrices de enteros para tareas como las que se describe en la pregunta. Cada elemento contendrá 0 (no pertenece a la serie) o el número ordinal del elemento en la serie. Para el ejemplo de la pregunta, el código

  aTemp(<first element>) = IIf(<condition aData(<first element>) NOT in sequence>, 0, 1) For i = <second element of aData> To <last element of aData>     If <condition aData(<element i>) NOT in sequence> Then         aTemp(i) = 0     Else         aTemp(i) = aTemp(i - 1) + 1     End If Next i   

formará una matriz de "índices"

  Data    Index     0   0     0   0     0   0     2   1     5   2     9   3     6   4     0   0     1   1     0   0     5   1     8   2     0   0     0   0   

Para la tarea descrita, <condition aData(<element i>) NOT in sequence> es solo una comparación con cero. En otros casos, la condición puede ser más compleja, incluido el análisis de los valores de las células adyacentes. Pero el principio permanece los mismos valores enteros se acumulan en la matriz de índice.

La segunda pasada se realiza en orden inverso, desde la parte inferior hasta la parte superior. Si estudiamos cuidadosamente el ejemplo de índices, queda claro que solo aquellos valores de origen para los cuales hay un índice mayor que uno están sujetos a procesamiento, para el índice 0, el valor del resultado será el mismo que en la matriz original (es decir, , también 0), para el índice 1 (secuencia de un elemento) es exactamente igual.

Cuando se mueve de abajo hacia arriba, primero tropezamos con los valores máximos de los índices (2 para la última secuencia, 4 para la primera secuencia). Esto le permite acortar la búsqueda simplemente saltando sobre la siguiente secuencia, saltando el número de elementos que se encuentran en el índice.

Para organizar dicho pase, no podemos usar el bucle para el bucle, usaremos el bucle while

      i = <last index>     While i >= <first index>         If aTemp(i) < 2 Then             i = i - 1   ' Jusm move up to one element         Else ' We know i (number of row with end of sequence)  ' and aTemp(i) (count of elements in this sequence).  ' So, we can calculate start of sequence as i - aTemp(i) + 1             <your code to process one sequence, aData array values from i - aTemp(i) + 1 to i>             i = i - aTemp(i) - 1         End If     Wend   

Preste atención a la línea i = i - aTemp(i) - 1 - Saltamos toda la longitud de la secuencia y una línea más alta. En otras palabras, ya que sabemos que el elemento antes de que la serie actual no sea una serie (este es uno de los separadores de la serie), luego lo salimos sin procesamiento.

La decisión final se puede formalizar en forma de un UDF. Acepte los datos de origen como una gama de hojas de trabajo en lugar de una matriz de valores celulares. Esta técnica le permite analizar no solo los datos, sino también su diseño, por ejemplo, el color del fondo o la fuente. Esto es conveniente cuando las secuencias en el rango original ya se han marcado manualmente (o por algún programa) y la información sobre las secuencias está contenida en el diseño de las celdas. En este caso, este no es el caso, por lo que uno de los primeros operadores de la función será obtener valores de un rango en una matriz.

Ponerlo todo junto, el código para resolver el problema de la muestra de la pregunta podría parecer esto:

  Function getMaxInSeries(aDataRange As Range) As Variant Dim aData As Variant Dim lB As Long, uB As Long Dim aTemp() As Integer Dim i As Long, j As Long Dim vMax As Variant     aData = aDataRange.Resize(, 1).Value     lB = LBound(aData)     uB = UBound(aData)     ReDim aTemp(lB To uB)     aTemp(lB) = IIf(aData(lB, 1) = 0, 0, 1)     For i = lB + 1 To uB         If aData(i, 1) = 0 Then             aTemp(i) = 0         Else             aTemp(i) = aTemp(i - 1) + 1         End If     Next i     i = uB     While i >= lB         If aTemp(i) < 2 Then             i = i - 1         Else             vMax = aData(i, 1)             For j = i - aTemp(i) + 1 To i - 1 ' No need compare with last element, we start with this value                 If vMax < aData(j, 1) Then vMax = aData(j, 1)             Next j             For j = i - aTemp(i) + 1 To i                 aData(j, 1) = vMax             Next j             i = i - aTemp(i) - 1         End If     Wend     getMaxInSeries = aData End Function   

getmaxinseriesdemo.gif

 

The most optimal algorithm for processing such sequences is the division of the task into two passes: during the first pass, sequences are extracted from the entire original data set and information about them is stored in an auxiliary array; during the second pass, actions are performed for each series.

The auxiliary array can be of any structure. For example, it can store information about the beginning of the sequence (the row number in which the sequence began) and its length (or the row number where the sequence was interrupted). This storage method can greatly save memory, which is important when processing very large sets of source data, but greatly complicates the first pass algorithm.

If the available resources allow, then an array of the same dimension as the original dataset is used as an auxiliary array.

Depending on the type of sequence and the problem to be solved, the type of the auxiliary array is selected. For example, if you just need to select (highlight, colorize) sequences in the original dataset using conditional formatting, then a boolean array is enough, in which each element simply shows "belongs / does not belong to the series".

Integer arrays are used for tasks like the one described in the question. Each element will contain either 0 (does not belong to the series) or the ordinal number of the element in the series. For the example from the question, the code

aTemp(<first element>) = IIf(<condition aData(<first element>) NOT in sequence>, 0, 1) For i = <second element of aData> To <last element of aData>     If <condition aData(<element i>) NOT in sequence> Then         aTemp(i) = 0     Else         aTemp(i) = aTemp(i - 1) + 1     End If Next i 

will form such an array of "indices"

Data    Index     0   0     0   0     0   0     2   1     5   2     9   3     6   4     0   0     1   1     0   0     5   1     8   2     0   0     0   0 

For the described task, <condition aData(<element i>) NOT in sequence> is just a comparison with zero. In other cases, the condition can be more complex, including the analysis of the values of adjacent cells. But the principle remains the same - integer values are accumulated in the Index array.

The second pass is done in reverse order, from bottom to top. If we carefully study the example of indexes, it becomes clear that only those source values for which there is an index greater than one are subject to processing - for index 0, the result value will be the same as in the original array (that is, also 0), for index 1 (one-element sequence) is exactly the same.

When moving from bottom to top, we will first stumble upon the maximum values of the indices (2 for the last sequence, 4 for the first sequence). This allows you to shorten the lookup by simply jumping over the next sequence, skipping the number of items found in the Index.

To organize such a pass, we cannot use the FOR loop, we will use the WHILE loop

    i = <last index>     While i >= <first index>         If aTemp(i) < 2 Then             i = i - 1   ' Jusm move up to one element         Else ' We know i (number of row with end of sequence)  ' and aTemp(i) (count of elements in this sequence).  ' So, we can calculate start of sequence as i - aTemp(i) + 1             <your code to process one sequence, aData array values from i - aTemp(i) + 1 to i>             i = i - aTemp(i) - 1         End If     Wend 

Pay attention to the line i = i - aTemp(i) - 1 - we jump up the entire length of the sequence and one more line higher. In other words, since we know that the element before the current series is not a series (this is one of the series separators), then we skip it without processing.

The final decision can be formalized in the form of a UDF. Accept the source data as a worksheet range rather than an array of cell values. This technique allows you to analyze not only the data, but also their design, for example, the color of the background or font. This is convenient when the sequences in the original range have already been marked up manually (or by some program) and information about the sequences is contained in the design of the cells. In this case, this is not the case, so one of the first operators of the function will be to get values from a range into an array.

Putting it all together, the code for solving the sample problem from the question might look like this:

Function getMaxInSeries(aDataRange As Range) As Variant Dim aData As Variant Dim lB As Long, uB As Long Dim aTemp() As Integer Dim i As Long, j As Long Dim vMax As Variant     aData = aDataRange.Resize(, 1).Value     lB = LBound(aData)     uB = UBound(aData)     ReDim aTemp(lB To uB)     aTemp(lB) = IIf(aData(lB, 1) = 0, 0, 1)     For i = lB + 1 To uB         If aData(i, 1) = 0 Then             aTemp(i) = 0         Else             aTemp(i) = aTemp(i - 1) + 1         End If     Next i     i = uB     While i >= lB         If aTemp(i) < 2 Then             i = i - 1         Else             vMax = aData(i, 1)             For j = i - aTemp(i) + 1 To i - 1 ' No need compare with last element, we start with this value                 If vMax < aData(j, 1) Then vMax = aData(j, 1)             Next j             For j = i - aTemp(i) + 1 To i                 aData(j, 1) = vMax             Next j             i = i - aTemp(i) - 1         End If     Wend     getMaxInSeries = aData End Function 

getMaxInSeriesDemo.gif

 
 

Relacionados problema

1  ¿Llamar a SSL habilitado servicios de MSEXCEL?  ( Calling ssl enabled services from msexcel ) 
Tengo servicio web desplegado en Web Sphere y este servicio es SSL habilitado. Tengo que llamar a este servicio de MS Excel. Estoy usando mssoaplib.SoapClient...

0  Excel Macro Runtime Error 428 en Excel 2003  ( Excel macro runtime error 428 in excel 2003 ) 
He creado una plantilla XLT Excel que funciona bien en Excel 2007 en el modo de compatibilidad y no muestra errores en la comprobación de compatibilidad. La p...

3  ¿Cómo creo una solicitud ejecutable para un procedimiento macro de Excel?  ( How do i create an executable application for a excel macro procedure ) 
Tengo dos hojas de cálculo ("antiguo" y "nuevo") y una macro de script vb. La macro se ejecuta en uno ("nuevo"), hace una comparación de los contenidos de la ...

3  ¿Cuáles son los consentimientos y los contras de las matrices pasadas contra las matrices globales en Excel VBA?  ( What are pros cons of passed arrays vs global arrays in excel vba ) 
OK, 2do intento de escribir una pregunta de desbordamiento de una pila, así que perdóname si esto parece familiar. Estoy reescribiendo una macro de Excel qu...

2  Cómo crear la tabla de contenidos (TOC) a través de Macro  ( How to create table of contents toc via macro ) 
Necesito crear la tabla de contenidos después de Mailmerge por Macro, ya que Word no admite la actualización del campo TOC de los campos TC. ¿Es posible aut...

1  Llamando a una función complementaria desde VBA de Excel  ( Calling a add in function from excels vba ) 
Estoy usando un complemento de Excel para un Erlangs: http://abstractmicro.com/erlang/helppages/ref-erlbblockage.htm Intento llamar a la función ERLANG-B d...

0  Cómo seleccionar un subconjunto de una tabla con Excel VBA en la forma de seleccionar -> desde -> donde  ( How to select a subset of a table with excel vba in the manner of select from ) 
Disculpas es esta es la pregunta clásica de Newbie, pero no he podido encontrar una respuesta concreta. Soy completamente nuevo para Excel VBA y de programa...

3  Añadiendo DataPoint a Excel Chart con VBA  ( Adding datapoint to excel chart with vba ) 
Tengo un objeto de carta, con 3 series. La serie está obteniendo los valores Y de los rangos C1: C10, D1: D10 y E1: E10. El valor depende de los valores en A1...

3  Acelerar una macro de Excel?  ( Speed up an excel macro ) 
En este momento tengo una macro PopulateYearlyValues , pero me parece que está tomando demasiado tiempo Sub PopulateYearlyValues(ByVal Month As Range) ...

0  Regresando C # listas de un servicio web a Excel VBA  ( Returning c sharp lists from a web service to excel vba ) 
Este es un largo - disculpas ... ¿Hay una forma confiable de devolución de listas de C # a VBA a través de un servicio web? Tengo un servicio web que de...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos