Manera más eficiente de reducir un rango -- vba campo con excel camp codereview Relacionados El problema

More efficient way to narrow down a range


0
vote

problema

Español

Necesito obtener datos de una hoja de cálculo donde hay tres parámetros. Una ID de producto que es varias filas, dentro de las filas de ID del producto, hay otro conjunto de filas con el tipo de carga, dentro de ese conjunto de filas, hay una carga que quiero encontrar, que también puede abarcar múltiples filas. Si la carga se extiende varias filas, tomo el primero.

Tengo una función llamada New_Rng que lleva el objeto de hoja, el rango dentro de la hoja que se buscará, y la cadena para buscar, y devuelve el rango que abarca la primera y última aparición de la cadena de búsqueda.

Estoy haciendo varias llamadas para terminar con el valor que necesito, pero sé que no es eficiente.

Esto es lo que tengo ahora:

  dim fndRng as range dim shtz as new workshseet dim ctr as long      ctr = 4      set shtz = sheets("DATA")      Set fndRng = shtz .range(StartRange, shtz .Cells(LastRow,LastColumn))      If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, shtz.Cells(ctr, searchVal)      If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol1 - SchCol2))     If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, SVal2)     If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol2 - SchCol3 ))     If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, val3)     If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol3  - SchCol4))     If Not fndRng Is Nothing Then         For Each zell In fndRng             If zell.Row = fndRng.Row Then LoadVar(ctr, 0) = zell.value         Next zell     End If   

Sé que hay una mejor manera, pero no puedo entenderlo.

Original en ingles

I need to get data from a spreadsheet where there are three parameters. A product ID which is multiple rows, within the rows of product ID, there is another set of rows with the charge type, within that set of rows, there is a charge I want to find, which may also span multiple rows. If the charge does span multiple rows, I take the first one.

I have a function called New_Rng which takes the sheet object, the range within the sheet to be searched, and the string to look for, and returns the range which encompasses the first and last occurrence of the search string.

I'm doing multiple calls to wind up with the value that I need, but I know it's not efficient.

This is what I have now:

dim fndRng as range dim shtz as new workshseet dim ctr as long      ctr = 4      set shtz = sheets("DATA")      Set fndRng = shtz .range(StartRange, shtz .Cells(LastRow,LastColumn))      If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, shtz.Cells(ctr, searchVal)      If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol1 - SchCol2))     If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, SVal2)     If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol2 - SchCol3 ))     If Not fndRng Is Nothing Then Set fndRng = New_Rng(shtz, fndRng, val3)     If Not fndRng Is Nothing Then Set fndRng = fndRng.Offset(0, (SchCol3  - SchCol4))     If Not fndRng Is Nothing Then         For Each zell In fndRng             If zell.Row = fndRng.Row Then LoadVar(ctr, 0) = zell.value         Next zell     End If 

I know there's a better way, but I can't figure it out.

     
   
   

Lista de respuestas

0
 
vote
vote
La mejor respuesta
 

Hojas de trabajo tienen un CodeName3 Vista de propiedades ( F4 ) y el campo 99887776655544334 (el que se encuentra en la parte superior) se puede utilizar como el nombre de la hoja de trabajo. De esta manera, puede evitar Sheets("mySheet") y, en su lugar, solo use mySheet . Esto eliminará la variable shtz , que no nos dice nada.

Hablando de nombres de variables, intente darles nombres más significativos: hace que sea más fácil seguir lo que está sucediendo.

como este fragmento

  Sheet1.Cells(counter, searchVal))   

Puedo decir searchVal tiene que ser un número entero, pero si lo vi en cualquier otro lugar, creo que es una cadena.

También con sus columnas de búsqueda System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => { // Just loop. for(int i = 0; i < 100; i++) { Thread.Sleep(1000); Console.Write(floors.Count); } }); for (int i = 0; i < 100; i++) { Thread.Sleep(1000); floors.Add(i); } t.Wait(); 0 - Estoy seguro de que hay una mejor manera de hacerlo. Tal vez System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => { // Just loop. for(int i = 0; i < 100; i++) { Thread.Sleep(1000); Console.Write(floors.Count); } }); for (int i = 0; i < 100; i++) { Thread.Sleep(1000); floors.Add(i); } t.Wait(); 1 y tal.

Tengo que pensar su serie de System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => { // Just loop. for(int i = 0; i < 100; i++) { Thread.Sleep(1000); Console.Write(floors.Count); } }); for (int i = 0; i < 100; i++) { Thread.Sleep(1000); floors.Add(i); } t.Wait(); 2 es porque usted estaba recibiendo errores. También tengo que imaginar que estás pasando todos de estos valores al código anterior? ¿Todas las columnas y todas las cadenas de búsqueda?


Entonces, la forma más fácil de hacer que esto sea más eficiente es llevarlo a una matriz. En mi ejemplo, estoy devolviendo una cadena en lugar de un rango, porque asumo que eso es lo que buscas. De cualquier manera, es un ejemplo.

  System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => {             // Just loop.             for(int i = 0; i < 100; i++)             {                 Thread.Sleep(1000);                 Console.Write(floors.Count);             }         }); for (int i = 0; i < 100; i++) {     Thread.Sleep(1000);     floors.Add(i); } t.Wait(); 3  

Entonces, lo que está haciendo aquí es traer todo el rango de búsqueda a una matriz, pasando esa matriz a su función y coincide con todas de las cuerdas de búsqueda a la vez.

probablemente tendrá que construir en algún manejo de errores. Y si desea el rango en lugar de la cadena, aún puede traerlo todo en la matriz y volver donde el rango sería como un par de coordenadas

  System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => {             // Just loop.             for(int i = 0; i < 100; i++)             {                 Thread.Sleep(1000);                 Console.Write(floors.Count);             }         }); for (int i = 0; i < 100; i++) {     Thread.Sleep(1000);     floors.Add(i); } t.Wait(); 4  

Solo cambiarías las compensaciones que solía hacer coincidir tu diseño de hoja.


Así que estaba pensando en ello, y creo que la mejor manera de simplificar esto sería usar una función para obtener el índice de fila que coincide:

  System.Collections.Concurrent.ConcurrentBag<int> floors = new System.Collections.Concurrent.ConcurrentBag<int>(); Task t = Task.Factory.StartNew(() => {             // Just loop.             for(int i = 0; i < 100; i++)             {                 Thread.Sleep(1000);                 Console.Write(floors.Count);             }         }); for (int i = 0; i < 100; i++) {     Thread.Sleep(1000);     floors.Add(i); } t.Wait(); 5  

Solo devolvería el número de fila en la matriz, ajústelo por donde sea que haya comenzado su matriz en la hoja, y use para obtener la columna de destino que inicialmente buscó.

 

Worksheets have a CodeName property - View Properties window (F4) and the (Name) field (the one at the top) can be used as the worksheet name. This way you can avoid Sheets("mySheet") and instead just use mySheet. This will eliminate the shtz variable, which isn't telling us anything.

Speaking of variable names, try to give them more meaningful names - it makes it easier to follow what is happening.

Like this snippet

Sheet1.Cells(counter, searchVal)) 

I can tell searchVal has to be an integer, but if I saw it anywhere else, I'd think it's a string.

Also with your search columns being SchCol1 - I'm sure there's a better way to do that. Maybe productColumn and such.

I have to think your series of If Not Nothing is because you were getting errors. I also have to imagine you're passing all of these values to the above code? All the columns and all the search strings?


So, the easiest way to make this more efficient is to bring it into an array. In my example, I'm returning a string instead of a range, because I assume that's what you're after. Either way, it's an example.

Option Explicit Sub RichardU()     Dim lastRow As Long     Dim lastColumn As Long     Dim searchValues As Variant     Dim searchArray As Variant     Dim foundRange As Range     Dim startRange As Range      lastRow = 10     lastColumn = 4     Set startRange = Sheet1.Cells(1, 1)      searchValues = Sheet1.Range(Cells(1, 7), Cells(1, 10))     searchArray = Sheet1.Range(startRange, Sheet1.Cells(lastRow, lastColumn))      Set foundRange = FindInArray(searchArray, searchValues(1, 1), searchValues(1, 2), searchValues(1, 3))  End Sub  Function FindInArray(ByVal valueArray As Variant, ByVal firstString As String, ByVal secondString As String, ByVal thirdString As String) As String     Dim rowIndex As Long     With valueArray         For rowIndex = LBound(valueArray) To UBound(valueArray)             If valueArray(rowIndex, 1) = firstString And valueArray(rowIndex, 2) = secondString And valueArray(rowIndex, 3) = thirdString Then                 FindInArray = valueArray(rowIndex, 4)                 Exit Function             End If         Next     End With End Function 

So what you're doing here is bringing the entire search range into an array, passing that array to your function and matching all of the search strings at once.

You will probably have to build in some error handling. And if you want the range instead of the string, you can still bring it all into the array and return where the range would be as a pair of coordinates

Option Explicit Sub RichardU()     Dim lastRow As Long     Dim lastColumn As Long     Dim searchValues As Variant     Dim searchArray As Variant     Dim foundCoordinates As Variant     Dim foundRange As Range     Dim startRange As Range      lastRow = 10     lastColumn = 4     Set startRange = Sheet1.Cells(1, 1)      searchValues = Sheet1.Range(Cells(1, 7), Cells(1, 10))     searchArray = Sheet1.Range(startRange, Sheet1.Cells(lastRow, lastColumn))      foundCoordinates = FindInArray(searchArray, searchValues(1, 1), searchValues(1, 2), searchValues(1, 3))     Set foundRange = Sheet1.Range(Cells(foundCoordinates(1) + 1), Cells(foundCoordinates(1) + 1, foundCoordinates(2) + 2))  End Sub  Function FindInArray(ByVal valueArray As Variant, ByVal firstString As String, ByVal secondString As String, ByVal thirdString As String) As Variant     Dim rowIndex As Long     Dim foundCoordinates() As Long     ReDim foundCoordinates(1 To 2)     With valueArray         For rowIndex = LBound(valueArray) To UBound(valueArray)             If valueArray(rowIndex, 1) = firstString And valueArray(rowIndex, 2) = secondString And valueArray(rowIndex, 3) = thirdString Then                 foundCoordinates(1) = rowIndex                 foundCoordinates(2) = 4                 FindInArray = foundCoordinates                 Exit Function             End If         Next     End With End Function 

You'd just change the offsets I used to match your sheet layout.


So I was thinking about it, and I think the best way for you to simplify this would be to use a function to get the row index that matches -

Function FindArrayRow(ByVal valueArray As Variant, ByVal searchValues As Variant) As Long     Dim rowIndex As Long     With valueArray         For rowIndex = LBound(valueArray) To UBound(valueArray)             If valueArray(rowIndex, 1) = searchValues(0) And valueArray(rowIndex, 2) = searchValues(1) And valueArray(rowIndex, 3) = searchValues(2) Then                 FindArrayRow = rowIndex                 Exit Function             End If         Next     End With End Function 

You would just return the row number in the array, adjust it for wherever your array started on the sheet, and use it to get the target column that you were initially looking for.

 
 

Relacionados problema

6  Juego de serpientes - Dentro de la hoja de trabajo - Celdas como píxeles  ( Snake game within worksheet cells as pixels ) 
Desde mi intento bastante mediocre de hacer un juego de invasores del espacio, me topé con un caché de juegos de Visual Basic para solicitar juegos escritos p...

7  Recuperando datos de archivos en la carpeta  ( Retrieving data from files in the folder ) 
Estoy a usar el código VBA para simplemente recuperar ciertos datos de todos los archivos de Excel en cierta carpeta y pegarlo en la hoja de cálculo de trabaj...

5  Copiando datos de libros cerrados  ( Copying data from closed workbooks ) 
Soy un pasante en una empresa industrial en Brasil y sucede que estoy usando mucho en Excel. Acabo de empezar a jugar con VBA hace un par de días, ¡y me divie...

2  Importando datos en Excel  ( Importing data into excel ) 
¿Existe una forma más fácil de importar datos en una matriz de Excel u otra estructura de datos? He intentado investigar colecciones, pero he encontrado la D...

6  Fijación de archivos de reclamación médica a través del archivo de texto Lea / escribe  ( Fixing medical claim files through text file read write ) 
Con Gracias a @ Mat'Smug y @comintern por su aliento, aquí hay un programa que escribí para ayudar a mi equipo a reparar archivos de reclamación médica. El ...

3  Coloque el cálculo del costo del campamento de verano utilizando las hojas de Google  ( Summer camp cost calculation using google sheets ) 
Estoy haciendo una hoja que suma el costo total para un campamento de verano después del registro. Cada padre puede registrarse hasta cinco niños. Ya he hecho...

2  Búsqueda de pruebas de abstracción y unidad en mesa Excel  ( Abstracting and unit testing lookups in excel table ) 
Fondo Tengo una solución de VBA que utilizo para ingerir informes de texto de inversión y reformatearlos para su análisis en Excel. Funciona, pero las macro...

6  Crear tabla que enumera la visibilidad de la hoja de trabajo  ( Create table that lists worksheet visibility ) 
Siguiendo mi pregunta anterior crear Una tabla que enumera las macros en un libro de trabajo o hoja de trabajo Aquí está mi Sub para determinar la visibilid...

3  Comparando células de dos hojas de trabajo  ( Comparing cells of two worksheets ) 
Con el siguiente código, trato de comparar el contenido de dos hojas de trabajo. La primera columna contiene una clave que es única y las siguientes columnas ...

5  Excel a JSON Parser con http descargar  ( Excel to json parser with http download ) 
He estado trabajando en un proyecto ( enlace ) a Descargue una hoja de cálculo de Ransomware y propiedades conocidas y conviértase en JSON para que pueda cons...




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