Home-theater-designers
Az Excel erőteljes. Ha sokat használja, valószínűleg már sok trükköt ismer a képletek vagy az automatikus formázás használatával, de Sejtek és Hatótávolság A VBA funkciói segítségével teljesen új szintre emelheti Excel -elemzését.
A VBA Cells and Range funkcióinak használatával az a probléma, hogy emelt szinten a legtöbb ember nehezen érti meg, hogyan működnek ezek a funkciók. Ezek használata nagyon zavaró lehet. Az alábbiakban bemutatjuk, hogyan használhatja őket olyan módon, amire valószínűleg nem is gondolt.
A sejtek funkciója
A Cells and Range funkciók megmondják a VBA szkriptjét hogy pontosan hol szeretné elérni a munkalapot, vagy elhelyezni az adatokat. A fő különbség a két cella között az, amire hivatkoznak.
Sejtek általában egyszerre egyetlen cellára hivatkoznak, míg Hatótávolság cellacsoportra utal egyszerre. Ennek a funkciónak a formátuma Sejtek (sor, oszlop) .
Ez a teljes lap minden egyes cellájára hivatkozik. Ez az egyetlen példa, ahol a Cells függvény nem hivatkozik egyetlen cellára:
Worksheets('Sheet1').CellsEz a felső sor bal oldali harmadik cellájára utal. C1 cella:
Worksheets('Sheet1').Cells(3)A következő kód a D15 cellára hivatkozik:
Worksheets('Sheet1').Cells(15,4)Ha szeretné, hivatkozhat a D15 cellára a „Cells (15,„ D ”)” kifejezéssel is-használhatja az oszlop betűjét.
Nagy rugalmasság van abban, hogy egy cellára hivatkozhat egy oszlop- és cellaszám használatával, különösen olyan szkriptekkel, amelyek képesek hurok keresztül nagyszámú cella (és számításokat végez rajtuk) nagyon gyorsan. Az alábbiakban részletesebben foglalkozunk ezzel.
A tartomány funkció
A Tartomány funkció sok tekintetben sokkal hatékonyabb, mint a Cells használata, mivel lehetővé teszi, hogy egyszerre egyetlen cellára vagy egy adott cellatartományra hivatkozzon. Nem akar átlépni egy Range függvényen, mert a cellák referenciái nem számok (kivéve, ha beágyazza a Cells függvényt).
Ennek a funkciónak a formátuma Tartomány (1. cella, 2. cella) . Minden cella betű-számmal jelölhető.
Nézzünk néhány példát.
hogyan lehet visszakapni a régi facebook üzeneteket
Itt a tartományfüggvény az A5 cellára hivatkozik:
Worksheets('Sheet1').Range('A5')Itt a tartományfüggvény az A1 és E20 közötti összes cellára hivatkozik:
Worksheets('Sheet1').Range('A1:E20')Amint fentebb említettük, nem kell szám-betű cella hozzárendelést használni. Valójában két Cella függvényt használhat egy Range függvényen belül a tartomány azonosítására a lapon, például:
With Worksheets('Sheet1')
.Range(.Cells(1, 1), _
.Cells(20, 5))
End With
A fenti kód ugyanarra a tartományra hivatkozik, mint a Tartomány ('A1: E20') függvény. A használat értéke az, hogy lehetővé teszi, hogy olyan cikket írjon, amely dinamikusan működik a tartományokkal ciklusok használatával.
Most, hogy megértette a Cells and Range funkciók formázását, nézzük meg, hogyan használhatja kreatívan ezeket a funkciókat a VBA -kódban.
Adatok feldolgozása cellák funkcióval
A Cells funkció akkor a leghasznosabb, ha van egy komplex képlet, amelyet több cellatartományban szeretne végrehajtani. Ezek a tartományok több lapon is létezhetnek.
Vegyünk egy egyszerű példát. Tegyük fel, hogy 11 fős értékesítési csapatot irányít, és minden hónapban meg szeretné tekinteni a teljesítményüket.
Neked talán van 1. lap amely nyomon követi eladásaik számát és értékesítési volumenüket.
Tovább 2. lap itt követheti nyomon a vállalat ügyfeleitől kapott visszajelzéseket az elmúlt 30 napban.
Ha a bónuszt az első lapon szeretné kiszámítani a két lap értékei alapján, akkor ennek több módja is van. Írhat egy képletet az első cellába, amely a két lap adatai alapján elvégzi a számítást, és húzza lefelé. Ez működni fog.
Ennek alternatívája lehet VBA -szkript létrehozása, amelyet vagy elindít a futtatáskor, amikor megnyitja a lapot, vagy egy parancsgomb indítja el a lapon, így szabályozhatja, mikor számítja ki. Lehet, hogy VBA -szkriptet használ, hogy minden értékesítési adatot lehívjon egy külső fájlból.
Akkor miért nem indítja el a bónusz oszlop számításait ugyanabban a szkriptben?
A sejtek működés közben működnek
Ha még soha nem írt VBA -t Excelben, engedélyeznie kell a Fejlesztő menüpontot. Ehhez lépjen a következőre: Fájl > Lehetőségek . Kattintson Szalag testreszabása . Végül válassza a Fejlesztő lehetőséget a bal oldali ablaktáblán, Hozzáadás a jobb oldali ablaktáblára, és győződjön meg arról, hogy a jelölőnégyzet be van jelölve.
Most, amikor rákattint rendben és menjen vissza a főlapra, látni fogja a Fejlesztő menüopciót.
Használhatja a Beszúrás menüpontot a parancsgomb beszúrásához, vagy csak kattintson Kód megtekintése elkezdeni a kódolást.
Ebben a példában a szkriptet a munkafüzet minden megnyitásakor lefuttatjuk. Ehhez csak kattintson a gombra Kód megtekintése a fejlesztői menüből, és illessze be a következő új funkciót a kódablakba.
Private Sub Workbook_Open()
End SubA kódablakod valahogy így fog kinézni.
Most már készen áll arra, hogy írja be a számítást a számításhoz. Egyetlen ciklus segítségével átlépheti mind a 11 alkalmazottat, és a Cells funkcióval behúzza a számításhoz szükséges három változót.
Ne feledje, hogy a Cells függvény sorokat és oszlopokat tartalmaz paraméterekként az egyes cellák azonosításához. Az „x” -et sorba állítjuk, egy szám használatával kérhetjük az egyes oszlopok adatait. A sorok száma az alkalmazottak száma, tehát ez 1 és 11 között lesz. Az oszlop azonosítója 2 lesz az Értékesítési szám, 3 az Értékesítési mennyiség és 2 a 2. lapon a Visszajelzési pontszám.
A végső számítás a következő százalékokat használja a teljes bónusz pontszám 100 százalékának összeadására. Az ideális eladási szám 50, az értékesítési mennyiség 50 000 USD és a visszajelzési pontszám 10.
- (Értékesítések száma/50) x 0,4
- (Értékesítési mennyiség/50 000) x 0,5
- (Visszajelzési pontszám/10) x 0,1
Ez az egyszerű megközelítés súlyozott bónuszt ad az értékesítési alkalmazottaknak. 50-es szám, 50 000 dolláros mennyiség és 10-es pontszám esetén-megkapják a hónap maximális bónuszát. Mindazonáltal minden, ami bármilyen szempontból tökéletes, csökkenti a bónuszt. Az ideálisnál jobb minden növeli a bónuszt.
Most nézzük meg, hogyan lehet mindezt a logikát lehúzni egy nagyon egyszerű, rövid VBA -szkriptben:
Private Sub Workbook_Open()
For x = 2 To 12
Worksheets('Sheet1').Cells(x, 4) = (Worksheets('Sheet1').Cells(x, 2).Value / 50) * 0.4 _
+ (Worksheets('Sheet1').Cells(x, 3).Value / 50000) * 0.5 _
+ (Worksheets('Sheet2').Cells(x, 2).Value / 10) * 0.1 _
Next x
End Sub
A szkript kimenete így fog kinézni.
hány követője van a tiktoknak élőben
Ha azt szeretné, hogy a Bónusz oszlop a tényleges dollár bónuszt jelenítse meg, nem pedig a százalékot, akkor megszorozhatja azt a maximális bónusz összeggel. Még jobb, ha ezt az összeget egy másik lapon lévő cellába helyezi, és hivatkozik a kódjában. Ez megkönnyíti az érték későbbi módosítását a kód szerkesztése nélkül.
A Cells funkció szépsége abban rejlik, hogy elég kreatív logikát építhet az adatok begyűjtésére sok sejt különböző lapokon, és végezzen néhányat elég összetett számítások velük.
A Cells funkció használatával mindenféle műveletet végrehajthat a cellákon --- például a cellák törlését, a betűtípus formázásának megváltoztatását és még sok mást.
Ha mindent fel szeretne fedezni, amit tehet, nézze meg a Microsoft MSDN oldal a Cells objektumhoz.
Cellák formázása tartományfüggvénnyel
A cellák egyenként történő ciklusozásához a Cells funkció tökéletes. De ha valamit egyszerre szeretne alkalmazni a cellák teljes körére, akkor a Range funkció sokkal hatékonyabb.
Ennek egyik felhasználási módja lehet egy cellatartomány szkript használatával történő formázása, ha bizonyos feltételek teljesülnek.
Tegyük fel például, hogy ha az összes értékesítési alkalmazott összes értékesítési volumene meghaladja a 400 000 dollárt, akkor a bónusz oszlop összes celláját zöld színnel kell kiemelni, jelezve, hogy a csapat extra csapatbónuszt szerzett.
Nézzük meg, hogyan lehet ezt megtenni IF nyilatkozat .
Private Sub Workbook_Open()
If Worksheets('Sheet1').Cells(13, 3).Value > 400000 Then
ActiveSheet.Range('D2:D12').Interior.ColorIndex = 4
End If
End SubHa ez fut, ha a cella túl van a csapatcélon, akkor a tartomány összes cellája zölddel lesz kitöltve.
Ez csak egy egyszerű példa arra a sok műveletre, amelyeket a Range funkció segítségével végezhet cellacsoportokon. Egyéb teendők:
- Vázlat alkalmazása a csoport körül
- Ellenőrizze a szöveg helyesírását cellatartományon belül
- A cellák törlése, másolása vagy kivágása
- Keressen egy tartományon a 'Find' módszerrel
- Sokkal több
Feltétlenül olvassa el a Microsoft MSDN oldal hogy a Range objektum láthassa az összes lehetőséget.
Vigye az Excel -t a következő szintre
Most, hogy megértette a cellák és a tartomány funkciók közötti különbségeket, itt az ideje, hogy a VBA -szkriptet a következő szintre emelje. Dann cikke a Számlálás és Hozzáadás funkciók használatáról az Excelben lehetővé teszi, hogy még fejlettebb szkripteket készítsen, amelyek nagyon gyorsan felhalmozhatják az összes adathalmaz értékét.
És ha még csak most kezdi használni a VBA programot Excelben, ne felejtse el, hogy fantasztikus dolgunk van bevezető útmutató az Excel VBA -hoz neked is.
Részvény Részvény Csipog Email Canon vs Nikon: Melyik a jobb márka?A Canon és a Nikon a két legnagyobb név a kameraiparban. De melyik márka kínálja a kamerák és objektívek jobb választékát?
Olvassa tovább Kapcsolódó témák- Termelékenység
- Programozás
- Visual Basic Programming
- Microsoft Excel
Ryan villamosmérnöki diplomát szerzett. 13 évet dolgozott az automatizálási mérnöki területen, 5 évet az informatikában, most pedig alkalmazásmérnök. A MakeUseOf korábbi ügyvezető szerkesztője, beszélt az adatok vizualizálásával foglalkozó országos konferenciákon, és szerepelt a nemzeti tévében és rádióban.
Továbbiak Ryan Dube -tólIratkozzon fel hírlevelünkre
Csatlakozz hírlevelünkhöz, ahol technikai tippeket, értékeléseket, ingyenes e -könyveket és exkluzív ajánlatokat találsz!
Feliratkozáshoz kattintson ide