Telefonszámunk: 1-472-0679

Képlet egy részének kicserélése

Kezdőlap Fórumok Excel témák Képlet egy részének kicserélése

7 bejegyzés megtekintése - 1-7 / 7
  • Szerző
    Bejegyzés
  • #1683
    roznar
    Felhasználó

      Sziasztok!

      Több ezer képletben szeretném kicserélni a hivatkozásokat indirekt hivatkozásra. Pl. A1 helyett legyen INDIREKT(„A1”), A2 helyett pedig INDIREKT(„A2”). Én ezt csak 2 lépésben tudnám megoldani, mert az A1, A2, stb. részeket nem cserélhetem ki mindenütt egyformára. Amikor azonban az első idézőjelig megpróbálom végrehajtani a cserét, nem engedi, mert hibás lesz a képlet. Van valamilyen megoldás?

      #1684
      roznar
      Felhasználó

        Hiába helyettesítem a ki nem cserélendő részt ??-lel vagy *-gal, mert akkor az új szöveget is kérdőjelre vagy csillagra írja át.

        #1685
        horvimi
        Adminisztrátor

          Szia!
          Egyik probléma generálja a következőt! 🙂
          Mindenesetre nagyon inspiráló kérdéseket teszel fel, de még állom a sarat! 🙂
          Először el akartam passzolni, de végül ezzel is elvoltam egy darabig, és az egyik ötlet beválni látszik.
          Most csak arra hozom a példát, hogy az A oszlopra akarsz hivatkozni INDIREKT-el.
          Angol 2013 előtt ülök most, a magyar megfelelőket kell használnod.
          Lépésenként megyünk:
          1. B1-be írd ezt
          =CHAR(34)&ADDRESS(ROW(A1);COLUMN(A1);4)&CHAR(34)
          A KARAKTER(34) az idézőjel kódja. A CÍM függvény visszaadja egy cella címét. belül a SOR és OSZLOP függvényeket használtam a sor sé oszlop tényezőkhöz, a 4-es pedig azt kéri, hogy relatív, tehát A1 formában adja vissza.
          ha ezt beírod a B’-be, akkor „A1” lesz az eredmény.
          Húzd le az egész oszlopra.

          2. C1-be írd ezt:
          ="=INDIRECT("&B1&")"
          Ez szövegesen képzi az INDIREKT függvényt, belefűzve az előbb összerakott címet. Az eredménye ez lesz: =INDIRECT(„A1”). Viszont nem számol, mert ez még csak szöveg.
          Húzd le az egész oszlopra.
          (Ha a képletnek még van további része, akkor azt szövegesen hozzáfűzheted még itt)

          3. A C oszlopot saját magára tedd vissza értékként.
          Továbbra is szöveg marad, de már nem képlet, tulajdonképpen működnie kellene. Ezt ki tudod próbálni, ha a C1-en nyomsz egy F2-t, majd egy ENTERT, azonnal jó lesz. De ezt több ezer soron macerás lenne, ezért:

          4. jelöld ki a C oszlopot, majd nyomd meg az ALT-F11-et, hogy átlépj a Visual Basic szerkesztőbe.
          Ezután nyomj Ctrl-G-t, hogy megnyíljon a parancssor. (Immediate Window). Rendezd úgy az ablakokat, hogy mögötte lásd az excel felületen még mindig kijelölt C oszlopot.

          5. Az Immediate ablakba írd be:
          selection.formula=selection.formula

          Ez eljátssza, mintha minden cellán nyomtál volna egy F2+ENTER-t.

          6. A segéd B oszlopot ezután törölheted.

          Nekem ez így sikerült.

          Csatolom a minta fájlt. Ebben először a D oszlopba lemásoltam a C oszlop képletét, és a 3. ponttól azon csináltam meg, hogy megmaradjon az eredeti.

          Imre

          • A hozzászólás módosításra került: 9 years, 2 months telt el-horvimi.
          Attachments:
          You must be logged in to view attached files.
          #1689
          roznar
          Felhasználó

            A második pontig mindent értek. Az F2-re azonban nálam nem történik semmi, a Visual Basic-ben pedig nem tudom, hogy mit csináljak, miután beírtam azt az utasítást, így ott sem történik semmi. A D oszlopban minden sorban megmarad az A1. Biztos, hogy nem jól csinálok valamit. A következő problémám pedig valószínűleg akkor fog jönni, amikor majd megpróbálom ezt az egészet a saját képletemre alkalmazni, amelyekben az A1 több helyen is szerepel, meg ráadásul B1 is van benne, amit szintén indirekté kell alakítani.

            =HA(INDIREKT(„A1″)=””;””;HA(INDIREKT(„A1”)=INDIREKT(„B1″);”osztatlan”;Órarend!A$4))

            Az remélem nem lesz pluszban zavaró, hogy ezek a cellák ráadásul nem is ugyanazon a lapon szerepelnek, tehát igazából nem is A1-ről, meg B1-ről, hanem Órarend!A1-ről, ill. Órarend!B1-ről van szó.

            #1690
            horvimi
            Adminisztrátor

              Mondanék azért valamit. Az INDIREKT függvény un. VOLATILE típusú. Ez azt jelenti, hogy nem csak akkor számolódik újra, amikor valamelyik forrás cellája változik, hanem mindig, amikor bármely cella megváltozik.

              Tehát ha több ezer sorod van, és ebben soronként 3 INDIREKT, valószínűleg drasztikusan lassú lesz a fájl.

              Hogy miért pont ezt a megoldást választod, azt neked kell tudni, de nem igazán optimális modell. Persze, lehet, hogy makrózni kellene, de ezt most hagyjuk.

              Visszatérve a problémára:Ha mindent úgy csinálsz, mint ahogy leírtam, működnie kellene, de Én is sejtettem, hogy lesz itt még valami.
              Küldd el a fájlt, jelöld meg az átalakítandó oszlopot, és megküzdök vele, majd beszámolok.
              horvimi[qkac]gmail

              #1691
              horvimi
              Adminisztrátor

                Szia!

                Kicsit tovább tanulmányoztam a kérdést, és sikerült egy egyszerűbb, és nagyszerűbb megoldást találni.

                Eljátszogattam azzal, hogy mi lenne, ha a cellacímet is két darabból raknánk össze. Az egyik darab ugye az oszlop, ez állandó, és a sor a másik, aminek pedig lefelé húzva növekvőnek kellene lennie.
                Többféleképpen lehet lefelé húzva növekvő számsort előállítani, de nekünk most az a verzió kell, aminek csak a kezdő sorszámát kell megadni, és utána már működik, és nem függ a többi cellától, azok másolásától vagy mozgatásától.

                Előkészület
                —————————–
                Ha egy cellába beírod, hogy
                =SOROK($1:1)
                akkor 1-et kapsz. ha húzod lefelé, akkor növekszik. A függvény a megkapott tartomány sorainak számát adja vissza. Az érdekes az, hogy most két teljes sort adtunk meg neki, de az elsőt lekötöttük, azaz mindig 1, a másik viszont követi a lehúzás irányát. Tehát a második cellában már az lesz, hogy =SOROK($1:2), a harmadikban =SOROK($1:3), stb…

                Mindez az INDIREKT-el
                —————————————
                =INDIREKT("A"&SOROK($1:1))
                Ez az A1-re fog hivatkozni INDIREKT módon ugyanazon a lapon. Vontathatod az A1-et, nem fogja követni.
                Ha húzod lefelé, akkor viszont A2, A3, stb-re hivatkozik ugyanúgy
                Ha nem az első sorban lévővel akarod kezdeni, mint a Te példádban, ahol a B7-el kezdesz, ot az első képletben ez kell:
                =INDIREKT("B"&SOROK($1:7))
                Azaz egytől hetes sorig a sorok száma lesz a kezdet, ha lehúzod, akkor jön a 8,9,stb.

                Mindez másik lapra
                ———————————-
                =INDIREKT("Órarend!B"&SOROK($1:7))

                #1692
                roznar
                Felhasználó

                  Szia!

                  Ez a megoldás tényleg jobb, mint amit előzőleg javasoltál, bár nekem már az is bevált. Akkor azt javasoltad, hogy a hivatkozás helyére CÍM függvénnyel írjam be hivatkozott cellát, valahogy így:
                  =INDIREKT(„Órarend!”&CÍM(SOR(B1);OSZLOP(B1);4))
                  A CÍM függvény a hivatkozást szövegként írja be, így az INDEREKT-ben megfelelően működik.Furcsa módon azt vettem azonban észre, hogy ha a más lapra utaló részt elhagyom, tehát a képlet saját lapjára próbálok hivatkozni, a hivatkozás nem viselkedik indirektként.
                  =INDIREKT(CÍM(SOR(B1);OSZLOP(B1);4))
                  A fenti hivatkozás tehát nem működik indirektként annak ellenére, a cím függvény elvileg szövegként adja meg az értékét, akárcsak az idézőjelben megadott rész, amit most elhagytunk. Hogy ez mért van így, azt ugyan azóta sem értem, de azért megoldottam úgy, hogy beiktattam egy plusz lapot, ahonnan mint „más” lapra tudok hivatkozni, onnan pedig egyszerűen átveszem az adatokat. Egy kicsit macerás, de működik. A legutóbb javasolt megoldásod nyilvánvalóan egyszerűbb, bár – úgy látom – itt is annak köszönhető a működés, hogy a képlet egy része („A”) eredetileg is szövegként van megadva. Itt azonban ezt a szöveges rész el sem lehet hagyni, akármelyik lapra is kívánok hivatkozni.

                  Még egyszer is nagyon köszönöm a segítségedet. A feladatomat még az előző javaslatoddal megoldottam, és most már nem írom át, bármennyire is egyszerűbb a második változat.

                7 bejegyzés megtekintése - 1-7 / 7
                • Be kell jelentkezni a hozzászóláshoz.