J'ai un tableau 5x5 avec des lettres A-Y dans des emplacements aléatoires. J'ai besoin de trouver où se trouve une lettre spécifique dans le tableau. J'ai pu obtenir une matrice 1x25 en utilisant search, match et isnumber qui avait un 1 à l'emplacement de la valeur souhaitée, mais je ne sais pas comment extraire cette position du tableau.
{a, b, c, d, e; f, g, h, i, j; k, l, m, n, o; p, q, r, s, t; u, v, w, x, y}
si je cherche le "d" je reviendrai
{0 0 0 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0}
Comment extraire que le 'd' était en quatrième position ou [1,4] du tableau d'origine? Dans le problème d'origine, les caractères ne sont pas dans un ordre spécifique. Ils sont totalement aléatoires.
Je fais cela dans une feuille de calcul Excel, donc j'essaie de trouver une formule qui fonctionnera plutôt que du code VBA. isnumber autour de la recherche pour obtenir la matrice 1x25
4 Réponses :
Hypothèses:
AGGREGATE est ma fonction de choix ici. Il effectuera des opérations de type tableau pour les formules 14 et 15. Il peut être configuré pour ignorer les erreurs, qui seront utilisées à notre avantage, et nous pouvons choisir où dans la liste des résultats nous voulons extraire les résultats. AGGREGATE prend la forme de:
FOR ROW AGGREGATE(15,6,ROW($C$2:$G$6)/($C$2:$G$6=$A$2),1)-ROW($C$2)+1 FOR COLUMN AGGREGATE(15,6,COLUMN($C$2:$G$6)/($C$2:$G$6=$A$2),1)-COLUMN($C$2)+1
La formule 15 triera la liste des résultats du plus petit au plus grand. La Formule 14 le fait du plus grand au plus petit. à condition que toutes les entrées soient uniques, peu importe laquelle des deux est utilisée.
L'option 6 ignorera toutes les erreurs dans la plage / le tableau
Pour la partie plage / tableau, créez une liste de tous les numéros de ligne / colonne de la feuille de calcul. Construisez, faites la formule une fois pour les lignes, puis répétez-la et passez aux colonnes. Divisez le numéro de ligne ou de colonne par un résultat VRAI / FAUX. dans ce cas, la plage / le tableau correspondra-t-il à votre terme de recherche. Lors de l'envoi de TRUE / FALSE via une opération mathématique (pas toutes les fonctions) TRUE devient 1 et FALSE devient 0. Diviser par 0 générera une erreur. seules les lignes contenant votre terme de recherche seront divisées par 1 en laissant le numéro de ligne inchangé. Étant donné que l'option 6 ignore toutes les erreurs, il vous restera une liste de numéros de ligne contenant votre terme de recherche. Tant que vos cellules sont toutes uniques, cela signifie que vous n'aurez qu'un seul numéro de ligne.
La partie paramètre pour les formules 14 et 15 est la position des résultats triés que vous voulez renvoyer. Dans ce cas, la première occurrence est souhaitée, elle doit donc être 1.
Le fait de rassembler ces informations nous indique que notre formule AGGREGATE devrait être quelque chose du genre:
FOR ROW AGGREGATE(15,6,ROW($C$2:$G$6)/($C$2:$G$6=$A$2),1) FOR COLUMN AGGREGATE(15,6,COLUMN($C$2:$G$6)/($C$2:$G$6=$A$2),1)
Maintenant, cela vous dira quelle colonne et quelle ligne de la feuille de calcul vous êtes dedans. Si votre matrice / tableau a démarré en A1, vous n'avez rien d'autre à faire. Cependant, cette solution supposait que votre matrice commençait en C2. Cela signifie que les numéros de ligne et de colonne de votre matrice ne correspondent pas aux numéros de ligne et de colonne de la feuille de calcul. Un ajustement mineur devra être effectué. Soustrayez simplement la position de départ de la matrice et ajoutez 1. Cet ajustement donnera à la formule la forme suivante:
AGGREGATE(Formula #, Ignore Option #, range/array, parameter)
Un For Next imbriqué semble approprié. Commentaires dans le code.
Sub finInArray()
Dim i As Long, j As Long, arr As Variant, z As String, bFoundIt As Boolean
'dimension array
ReDim arr(0 To 4, 0 To 4)
'populate array
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
arr(i, j) = Chr(65 + i + j)
Next j
Next i
'designate char to find
z = "H"
'find in array
For i = LBound(arr, 1) To UBound(arr, 1)
For j = LBound(arr, 2) To UBound(arr, 2)
If z = arr(i, j) Then
bFoundIt = True
'stop looping j
Exit For
End If
Next j
'if found, stop looping i
If bFoundIt Then Exit For
Next i
'was it found?
If i <= UBound(arr, 1) And j <= UBound(arr, 2) Then
Debug.Print z & " found at arr(" & i & ", " & j & ")"
End If
End Sub
J'aime le code VBA, mais je ne suis pas assez doué pour l'incorporer dans une feuille de calcul. J'avais besoin d'utiliser la formule encore et encore dans plusieurs endroits. Comment ferais-je cela avec VBA? Ce code peut-il être transformé en un appel de fonction ou quelque chose? Désolé pour mon manque de compétence pour utiliser cette réponse. Cela a l'air très bien pensé. Merci!
Cela pourrait facilement être converti en une fonction définie par l'utilisateur, mais il n'est pas clair sur le wat que vous voulez retourner et pour la plupart des buts et objectifs, un UDF ne renvoie qu'une seule valeur. Voulez-vous renvoyer la ligne ou la colonne ou un objet de plage dans la cellule contenant la valeur trouvée? Avez-vous affaire à une plage de cellules 5x5 sur la feuille de calcul ou à un tableau de variantes 5x5 en mémoire?
je traite une plage 5x5 de cellules dans une feuille de calcul. En fait, il s'agit d'un tableau 25x25 de cellules dans une feuille de calcul. Je peux le briser et le gérer 5x5 blocs. Ce que je veux retourner est une ligne et un col de l'emplacement où une lettre spécifique se trouve. Chaque cellule ne contient que 0 ou 1 lettre et chaque groupe 5x5 n'a jamais de répétition. A-Y sont représentés au plus une fois pour chaque bloc 5x5. Certaines cellules seront vides, mais aucune n'aura de valeurs en double et elles ne seront jamais triées dans un certain ordre.
Donc, je ne suis pas très intelligent sur l'utilisation de VBA pour travailler avec une feuille de calcul. J'aimerais comprendre comment appliquer le code VBA tel que vous l'avez fourni pour travailler avec les données de la feuille, le manipuler et renvoyer des valeurs. S'il y a un moyen de faire cela, je suis toute oreille.
J'aime l'agrégat, mais je ne connaissais pas cela auparavant. Merci !!
J'ai aussi trouvé une solution après avoir posté la question.
SUM((MMULT(--(ISNUMBER(SEARCH("D",data))),colfind_array)))
où rowfind_array est une matrice 1x5 avec 1 2 3 4 5 et data est le 5x5 matrice contenant des lettres ou des blancs. Ceci renvoie le numéro de ligne de la lettre, si elle est trouvée.
La recherche, isnumber et le "-" renvoient un tableau 5x5 avec tous les 0 et un 1 où l'élément recherché a été trouvé. En le multipliant par un tableau 5x1 ou 1x5 avant ou après le résultat, puis en additionnant le tableau final, on obtient un nombre 1-5 qui indique mon numéro de ligne (ou col).
Donc, le numéro de col change simplement le chercheur dimensions du tableau et placez-le après le tableau de données, au lieu d’avant.
SUM((MMULT((rowfind_array),--(ISNUMBER(SEARCH("D",data))))))
où colfind_array est une matrice 5x1 avec 1 2 3 4 5 et data est la matrice 5x5 avec l'une ou l'autre des lettres ou des blancs dedans. Cela renvoie le numéro de colonne de la lettre, s'il est trouvé.
Un simple = if (countif ("D", data) = 1, ... vérifie si la lettre est là pour commencer et J'ai tout terminé.
J'utilise ceci pour trouver les lignes et les cols des entrées sur un assistant de puzzle de sudoku 25x25. Si les lettres sont dans le bloc 5x5, il renvoie la ligne et le col pour éliminer les lignes et cols pour les cases sans réponse. Cela fonctionne plutôt bien!
Si vous avez d'autres moyens intelligents de résoudre ce problème, veuillez partager. Merci, Dan
J'ai fait une feuille de calcul Excel une fois qui a aidé avec le format 9X9 standard. J'avais le puzzle sur la feuille 1, puis sur la feuille 2, j'ai exécuté les trois chèques standard. J'ai utilisé countif pour voir combien de fois un nombre spécifique est apparu dans une colonne, une ligne ou une boîte. J'ai ensuite utilisé la mise en forme conditionnelle sur la feuille principale pour masquer le chiffre lorsqu'il ne s'est produit qu'une seule fois dans l'un des décomptes, mais j'ai fait changer l'entrée en rouge vif si cela se produisait plus d'une fois. Cela n'allait pas résoudre le casse-tête. Faites-vous simplement savoir quelles étaient les entrées possibles pour une boîte.
Ouais, j'en ai trouvé plusieurs pour les solveurs / aides de feuille 9x9 et même quelques pour les solveurs 16x16 feuilles, mais j'ai un livre de puzzles de feuilles 25x25 et je suis resté coincé. J'ai donc décidé d'étendre un assistant de feuille 9x9 que j'ai trouvé sur Internet et d'obtenir de l'aide pour résoudre 25x25 sans me donner complètement la réponse. Si je suis coincé, je peux juste avoir un petit coup de pouce. J'avais déjà fait des extensions pour créer un solveur de sudoku 16x16, mais il utilisait 0-9 et A-F, tandis que le 25x25 utilise A-Y. Cela a compliqué ma solution et m'a fait repenser les équations.
Juste pour donner des alternatives:
Pour obtenir la ligne: =SUMPRODUCT((A1:E5=B7)*ROW(A1:E5))
Pour obtenir la colonne: =SUMPRODUCT((A1:E5=B7)*COLUMN(A1:E5))
Votre tableau / matrice est-il dans des cellules individuelles dans Excel?
Oui, le tableau est que les éléments sont dans des cellules individuelles dans Excel et il n'y a jamais de répétitions.