EXCEL-Přiřazení hodnot s dupl. Vyřešeno

[saabturboclub]

Ahoj,
v příloze mám vzorec, který mi v určeném sloupci vyhledá určenou hodnotu a k ní přiřadí zas danou hodnotu z jiného sloupce. Šel by vzorec upravit tak, aby v případě duplicitních položek hledání mi k té Xté duplicitní přiřadil xtou duplicitní položku? = hodnotu 12 mám v hledání 2x k první mi přiřadí hodnotu z první nalezené a k druhé mi přiřadí hodnotu z druhé nalezené. Momentálně mi přiřadí k dupl.hodnotám hodnoty z první nalezené. CHCI UPRAVIT VZOREC, ZDA PŮJDE, NECHCI VE VBA, protože při 15000 položek se VBA děsně kouše. Sice jsem našel podobné témata, ale řešení jen ve VBA.

Díky Pavel

[Reklama]

[navstevnik]

Pokud je procedura spravne navrzena, neni duvod ke "kousani".
Bohuzel bez VBA se neobejdes, ale pouziti VBA lze omezit na minimum.
Pokud je vyskyt hodnoty ve sloupci F2:Fxx jedinecny, hleda v bloku A2:Cyy funkce listu, pro vyskyt 2 a vice je pouzita uzivatelska funkce (UDF):

Kód: Vybrat vše

Option Explicit

Function VyhledatNPoradi(Co As Variant, N As Integer, Kde As Range, Sl As Byte) As Variant
  Dim FCll As Range, i As Integer
  i = 0
  VyhledatNPoradi = vbNullString
  For Each FCll In Kde.Cells
    If FCll.Value = Co Then
      i = i + 1
      If i = N Then
        VyhledatNPoradi = FCll.Offset(0, Sl - 1).Value
        Exit For
      End If
    End If
  Next FCll
  Set FCll = Nothing
End Function

V G2 je vzorec (kopiruj po radcich):

Kód: Vybrat vše

=KDYŽ(COUNTIF($F$2:$F2;F2)=1;SVYHLEDAT(F2;$A$2:$C$44;3;NEPRAVDA);VyhledatNPoradi(F2;COUNTIF($F$2:$F2;F2);$A$2:$A$44;3))

COUNTIF($F$2:$F2;F2) urcuje poradi vyskytu
Ve vzorci si osetri pripad, ze hodnota z Fxx se ve sloupci A2:Ayy nevyskytuje.

Doplneno:
Nize je UDF, ktera je rychlejsi, vhodna pro velky pocet polozek (prohledavanou oblast zadavat vcetne hlavickoveho radku):

Kód: Vybrat vše

Option Explicit

Function VyhledatNPoradi(Co As Variant, N As Integer, Kde As Range, Sl As Byte) As Variant
  Dim FCll As Range, i As Integer
  VyhledatNPoradi = vbNullString
  Set FCll = Kde.Find(Co, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
  If Not FCll Is Nothing Then
    Set Kde = FCll.Resize(Kde(Kde.Rows.Count).Row - FCll.Row, 1).Offset(1, 0)
    i = 1
    For Each FCll In Kde.Cells
      If FCll.Value = Co Then
        i = i + 1
        If i = N Then
          VyhledatNPoradi = FCll.Offset(0, Sl - 1).Value
          Exit For
        End If
      End If
    Next FCll
  End If
  Set FCll = Nothing
End Function

Kód: Vybrat vše

=KDYŽ(COUNTIF($F$2:$F2;F2)=1;SVYHLEDAT(F2;$A$2:$C$44;3;NEPRAVDA);VyhledatNPoradi(F2;COUNTIF($F$2:$F2;F2);$A$1:$A$41;3))

[saabturboclub]

dobře, vyzkouším

--- Doplnění předchozího příspěvku (27 Pro 2010 03:46) ---

Ahoj,
tak do hlavního souboru a funguje i rychlé.... dík

[saabturboclub]

Ahoj,
ještě bych se chtěl zeptat jaké by bylo nejlepší řešení, když bych přiřazení hodnot bral jako první denní proces a jako další proces (jednou týdně třeba pomocí tlačítka) by byl ten, že by akorát proběhla kontrola stejných ID (list1) [stačí jen ID,u kterých není přiřazena žádná hodnota (měla by být buňka prázdná, ale aktuálně přiřazuje hodnotu 0) ] a pokud by našel k hledanému ID také hodnotu (list odeslané), tak by hodnotu doplnil (stejný proces jak je uvedeno) a o nově doplněných hodnotách mně nějak informoval (souhr doplněných, zvýraznit...).
Hodnoty ID v obou listech se po vložení nemění a doplňují jen o nové ID a v sešitu odeslané se ID po vložení nemění a max.doplňují hodnoty k přiřazení. V příkladě se ID co jsou označeny šedivě nemají žádné hodnoty a až časem jsou hodnoty doplněny, v originálu mám cca. 19000 id kde jednou týdně kontroluji zda ID co nemají přiřazené hodnoty již nemají nově nějakou přiřazenou. Pokud by ID nemělo mít žádnou hodnotu a zbytečně je pořád nekontroloval, značím aspoň hodnotou "1".
Proceduru co mi byla k tématu doplněna použiju k prvotnímu vložení hodnot, tuto bych používal už jen jako kontrolu.
SNAD JE TO TROCHU CHÁPAVÉ, občas se jednoduché věci hůře popisují.

Díky

přikládám upravený excel s doplněním prvotního přiřazení hodnot i s duplicitou...

[navstevnik]

V zadani bylo pozadovano reseni pokud mozno bez procedur VBA. Takze byly pouzity funkce listu s nebytne nutnou uzivatelskou funkci (UDF).
Pokud je zapnuto automaticke prepocitavani listu, zmena na listu odeslane je v bunkach listu1 obsahujicich vzorce automaticky vykonana, takze nemuze nastat stav, kdy na listu odeslane by byla hodnota ve sloupci "HODNOTY k přiřazení" a na listu1 ve sloupci "přiřazení hodnot" by byla prazdna bunka nebo 0. Takze nejake jednotydenni kontroly s prirazenim hodnoty a vypisem techto hodnot je za daneho stavu neproveditelna - neni co kontrolovat. Nanejvys je mozne vypsat ID bez prirazene hodnoty, je tedy pozadovano toto?
Dale uvaha o oznaceni "Pokud by ID nemělo mít žádnou hodnotu a zbytečně je pořád nekontroloval, značím aspoň hodnotou "1"." vede k odstraneni prislusneho vzorce a nahrazeni "1".
PS.: Pripadny pozadavek na zajisteni "měla by být buňka prázdná, ale aktuálně přiřazuje hodnotu 0" vyzaduje doplneni vzorcu (v predchozi odpovedi jsem tuto potrebu avizoval)

Kód: Vybrat vše

=KDYŽ(COUNTIF($F$2:$F2;F2)=1;KDYŽ(SVYHLEDAT(F2;odeslané!$B$2:$X$15972;23;NEPRAVDA)=0;"";SVYHLEDAT(F2;odeslané!$B$2:$X$15972;23;NEPRAVDA));VyhledatNPoradi(F2;COUNTIF($F$2:$F2;F2);odeslané!$B$1:$B$15972;23))

, coz ve vysledku vede k prodlouzeni prepoctu listu.
Doplneno:
Na listu odeslane je vhodne vytvorit dynamicke pojmenovane oblasti:
HodnotySVyhl: =POSUN(odeslané!$B$1;1;0;POČET2(odeslané!$B:$B)-1;23)
HodnotyUDF: =POSUN(odeslané!$B$1;0;0;POČET2(odeslané!$B:$B);1)
a ve vzorci na listu1 na ne odkazovat:

Kód: Vybrat vše

=KDYŽ(COUNTIF($F$2:$F2;F2)=1;KDYŽ(SVYHLEDAT(F2;HodnotySVyhl;23;NEPRAVDA)=0;"";SVYHLEDAT(F2;HodnotySVyhl;23;NEPRAVDA));VyhledatNPoradi(F2;COUNTIF($F$2:$F2;F2);HodnotyUDF;23))

doplnene hodnoty na listu odeslane jsou automaticky zahrnuty, na listu 1 staci jen kopirovat vzorec do novych radku.

[saabturboclub]

ahoj, podívám se na to a zkusím....řešení klidně nemusí být podobné jak uváděný příklad, to je 1.proces a tento druhý může být i jinak řešený, jen mně nenapadlo jak.... jsem si říkal, jestli by nepomohlo to, že by ze zadání kontroloval pouze ID co mají prázdné přidělené hodnoty ze seznamem s celkovým všech ID.

[saabturboclub]

Ahoj, tak je to ok, s tím značením "1" jsem myslel že by mohlo pomoc k urychlení,ale co jsem prostudoval kód tak tak vidím, že je vše řešeno jinak a mnohem lépe, než mně napadlo. Dalo by se ještě vyřešit to, že bych potom třeba po stisku tlačitka mně z buňek, kde by doplnil nějaké číslo (není prázdná) smazal excel vzorec z buňky? Že by pak jen zůstal vzorec v prázdných buňkách? A to pro případ, když by mi někdo umazal zdrojová data, která se přenáší do buňěk? Dalo by se to nějak vyřešit?
A když už řeším tento soubor, dá se vepsat vba kód, který by vkládal kopírované buňky jen jako "hodnoty"? Při použítí ctrl+c ? Aby mi uživatel při kopírování podkladů z jiné tabulky nekopíroval i jejich formátování? Nebo je lepší řešit tak, že vba kód by třeba určité sloupce automaticky převáděl jen na "hodnoty" ? Uživatelé, co špatně vkládají data a nepoužívají vložit jako hodnoty, tak překopírovávají v sešitu nastavené podmínky formátování a ve finále místo 5-ti podmínek mi vzniká hromada duplicitních podmínek jen pro určité oblasti:-(

[navstevnik]

Prosim, upresni:

... třeba po stisku tlačitka mně z buňek, kde by doplnil nějaké číslo (není prázdná) smazal excel vzorec z buňky? Že by pak jen zůstal vzorec v prázdných buňkách? A to pro případ, když by mi někdo umazal zdrojová data, která se přenáší do buňěk?

,
nejak se v tom nemohu vyznat.

Moznost omezit Ctrl+v, nabidku Vlozit , kopirovani tazenim mysi umoznuje (rozsah omezeni zredukuj na vkladani, pocesti MsgBox):
http://www.vbaexpress.com/kb/getarticle.php?kb_id=373
pripadne take zde:
http://www.vbaexpress.com/forum/archive ... 19391.html
http://www.mrexcel.com/forum/showthread.php?t=487905
http://stackoverflow.com/questions/1347 ... s-in-excel

[saabturboclub]

Ahoj, myslel jsem tu druhou část tak, že mám na listu nastavené asi 15podmínek formátování a podle výběru stavu buňky se řádek patřičně zabarvuje. Protože soubor používá více lidí a do souboru pod sebe nahrávají data, tak se může stát, že něco špatně nakopírují a použijí pro přesun o řádek třeba CTRL+X a vloží kam patří (třeba jen o řádek pošunou). Tím se mi podmínka formátování rozhodí, vytvoří se duplicitní ale určená jen na tuto vyjmutou oblast. Funkci na ctrl+x jsem zakázal a myslel, že to tím vyřeším, ale pořád dokážou dělat v sešitu i za pomocí ctrl+c / v = ve finále mám 15 podmínek formátování a k tomu dalších xx hybridních jen pro určité kousky, podle toho, jak to tam opravují.
Jediné nejlepší řešení je vše co chtějí vkládat znovu už z jakéhokolik důvodu, odkudkoliv nebo opravovat, tak při použití vložit jinak/hodnoty se nestane aby nakopírovali cokoliv jiného, než data z buňky.

[navstevnik]

Nez slozite resit nasledky chyb je lepsi chybam jednoduse predchazet.
Omezit primy pristup uzivatelu k datum (uzamknout list a povolit zmeny na listu pouze prostrednictvim VBA, napr.:

Kód: Vybrat vše

Private Sub Workbook_Open()
  Me.Worksheets("list1").Protect Password:="MyPsw", UserInterfaceOnly:=True
End Sub

) a doplnovani dat ci jine aktivity resit ve VBA prostrednictvim formulare(u). Na pokyn k ulozeni - tlacitko - overit data a pokud jsou OK, ulozit, jinak vyzvat k oprave.

[saabturboclub]

Ahoj,
když by ještě nastala možnost, že hledané ID se vůbec v "odeslaném" listu nenachází, tak v tomto případě by do buňky nenapsal "#NENÍ_K_DISPOZICI" ale třeba nic nenapsal? Případně napsal to, co bych si sám zadal?

[navstevnik]

Text, ktery ma byt vlozen do bunky v pripade nenalezeni ID na listu odeslane, vloz napr. do bunky List1!J1 (pri pouziti jine bunky uprav odkaz ve vzorci: =KDYŽ(COUNTIF(HodnotyUDF;F2)=0;$J$1;....) a do List1!G2 vloz upraveny vzorec:

Kód: Vybrat vše

=KDYŽ(COUNTIF(HodnotyUDF;F2)=0;$J$1;KDYŽ(COUNTIF($F$2:$F2;F2)=1;KDYŽ(SVYHLEDAT(F2;HodnotySVyhl;23;NEPRAVDA)=0;"";SVYHLEDAT(F2;HodnotySVyhl;23;NEPRAVDA));VyhledatNPoradi(F2;COUNTIF($F$2:$F2;F2);HodnotyUDF;23)))

a kopiruj dole dle potreby.