Új hozzászólás Aktív témák

• Fferi50 Topikgazda

  #54972

  #54971 ny.janos

  ápr. 24., 22:48:09

  Új Válasz

  Új hozzászólás

  Összes hozzászólása itt Válaszok az összes hozzászólására itt Válaszok erre a hozzászólásra

  

  Fferi50

  Topikgazda

  #54971 ny.janos

  #54971 ny.janos

  A felvetés jogos. Megkérdeztem. Dinamikus segédtartományt javasol minden oszlopra szűrővel külön-külön. Nem tudom szerinte ez miért annyival jobb, mint a PQ-vel külön beolvasni az oszlopokat.

  Copilot
  Igen – az Excel 365 dinamikus tömbfüggvényeivel ma már van elegánsabb, „profibb” megoldás, és pontosan arra a problémára, amit leírsz:
  dinamikus adatérvényesítés, üres elemek nélkül, PQ‑frissítésre automatikusan reagálva, makró nélkül, és anélkül, hogy oszloponként külön PQ‑lekérdezést kellene csinálnod.
  Az alábbi megoldás kifejezetten O365-re optimalizált, és szerintem a mostani tervednél tisztább.
  A probléma lényege – miért jelenik meg az üres elem?
  Amikor:
  PQ‑vel betöltesz egy több oszlopos táblát,
  az oszlopok hossza eltérő,
  majd fix téglalapként hozol létre névvel ellátott tartományokat,
  akkor az Excel a teljes oszlophosszt veszi figyelembe, és ahol elfogynak az értékek, ott üres cellákat is listáz, az adatérvényesítés pedig:
  ezeket felveszi a listába,
  és az első elem (gyakran az üres) lesz az aktív.
  Ez klasszikus korlátja a „régi” névtartomány-logikának.
  A modern (365‑ös) megoldás alapelve
  Ne a nyers PQ‑táblát add meg adatérvényesítés forrásának,
  hanem:
  minden oszlopra hozz létre egy dinamikus, „szűrt” segédtartományt
  ami:
  csak a nem üres értékeket tartalmazza
  automatikusan bővül/szűkül
  és erre a dinamikus tömbre hivatkozik az adatérvényesítés
  Ehhez nem kell VBA, sem oszloponként külön PQ.
  Konkrét, jól bevált megoldás
  Kiindulás
  Tegyük fel, hogy PQ‑vel betöltöd a táblát:
  neve: PQ_Lista
  oszlopok fejlécei:
  Termek, Szolgaltatas, Regio, stb.
  a lista egy másik fájlban van → ez nem gond
  1️⃣ Dinamikus segédtartomány létrehozása (FILTER)
  Hozz létre egy segéd munkalapot (pl. „DV”), és minden oszlophoz 1 képletet.
  Példa – Termek oszlop:
  Excel=FILTER(PQ_Lista[Termek]; PQ_Lista[Termek]<>"" )További vonalak megjelenítése
  Ez:
  eldob minden üres cellát
  dinamikusan követi:
  a PQ frissítést
  az elemszám változását
  csak valódi listát ad vissza
  👉 Ez tömbként kifolyik lefelé (spill).
  Ugyanezt megcsinálod a többi oszlopra is:
  Excel=FILTER(PQ_Lista[Szolgaltatas]; PQ_Lista[Szolgaltatas]<>"")=FILTER(PQ_Lista[Regio]; PQ_Lista[Regio]<>"")További vonalak megjelenítése
  2️⃣ Név hozzárendelése a kifolyó tartományhoz
  Most jön a kulcsfontosságú rész.
  Ha a képlet pl. a DV!A2 cellában van, akkor automatikusan létrejön egy
  kifolyó tartomány: DV!A2#
  Képletek → Névkezelő → Új
  Név:
  Plain TextTermek_listaTovábbi vonalak megjelenítése
  Hivatkozás:
  Plain Text=DV!A2#További vonalak megjelenítése
  ⚠️ A # NAGYON fontos – ez jelzi a teljes dinamikus tömböt.
  Ugyanezt megteszed a többi oszlopra is.
  3️⃣ Adatérvényesítés beállítása
  Most már az adatérvényesítésnél nem táblát, nem oszlopot, hanem NEVET használsz:
  Adat → Adatérvényesítés
  Engedélyez: Lista
  Forrás:
  Excel=Termek_lista``További vonalak megjelenítése
  ✅ Eredmény:
  ❌ nincs üres elem
  ✅ mindig az első valós érték az aktív
  🔄 PQ frissítés után automatikusan frissül
  📁 több fájlban is újrahasználható
  Extra finomítás (ha kell)
  Ha egy oszlopban duplikátumok is lehetnek:
  Excel=SORT(UNIQUE(FILTER(PQ_Lista[Termek]; PQ_Lista[Termek]<>"")))További vonalak megjelenítése
  Ha az első elem biztosan ne legyen üres még hibánál sem:
  Excel=IFERROR(FILTER(...); "")További vonalak megjelenítése
  Összefoglalás – „best practice” O365-ben
  ➡️ PQ = adatforrás
  ➡️ FILTER = üzleti lista
  ➡️ Spill + név = adatérvényesítés
  Ez ma az Excel 365‑ös, makrómentes, jövőbiztos megoldás erre az esetre.
  Ha szeretnéd,
  – mutatok egy oszlopra teljes mintafájlt,
  – vagy segítek strukturálni a segédlapot, hogy sok oszlopnál is átlátható legyen.

  PQ-vel is megoldható, hogy csak a kapcsos zárójelben lévő szám legyen cserélendő az egyes oszlopok beolvasásához:
  = Table.SelectRows(Table.SelectColumns(LegorduloListak,Table.ColumnNames(LegorduloListak){3}), each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))

  Szia!
  Bizonyos feltételek esetén nem kell ilyen bonyolult módon gondolkodni. A feltétel pedig annyi, hogy a választéklisták oszlopai ne tartalmazzanak egyéb értékeket. Ebben az esetben a névkezelőben dinamikus tartományokat az OFFSET (ELTOLÁS) és a COUNTA (DARAB2) függvényekkel tudod létrehozni így:
  Név: Beszerzés_tárgya
  Hivatkozásba:
  =OFFSET(Munka1!$A$1,1,0,COUNTA(Munka1!$A:$A)-1,1)
  Ezt végigcsinálod minden oszlopra.
  Ezután minden névtartományod dinamikus lesz.
  MEGJEGYZÉS: Lehet más érték is a választékok oszlopában, ha elhatárolható a választék tartománytól.
  Pl. ha a választékok az első 50 sorban lehetnek, akkor a hivatkozás képlete az alábbiak szerint módosul:
  =OFFSET(Munka1!$A$1,1,0,COUNTA(Munka1!$A$1:$A$50)-1,1)
  Az adatérvényesítésnél természetesen a létrehozott neveket kell megadnod.
  Remélem, hasznos lesz számodra amit írtam.
  Üdv.

Új hozzászólás Aktív témák