Mesterséges inteligencia API költségek
Kategória: Excel emelt szint
Feladat leírása
Egy szoftverfejlesztő cégnél dolgozol, ahol a belső rendszerekbe integrálták a legújabb AI modelleket (GPT-4, Claude 3 Opus, Gemini Ultra, stb.). A pénzügyi osztály azonban jelezte, hogy a hó végi számla gyanúsan magas, és sok a "felesleges" vagy hibás hívás.
A feladatod, hogy a szerverek által generált nyers naplófájlokból (requests_2024.txt) és az aktuális árlistából (arlista_v2.csv) egy átlátható, dinamikusan számoló elemzést készíts Excelben. A requests_2024.txt tartalmazza a hívások időbélyegét, a modell azonosítóját (ami gyakran tartalmaz felesleges verziószámokat), a bemeneti (prompt) és kimeneti (completion) tokenek számát, valamint a szerver válaszkódját (status code).
Mivel a modellek árazása eltérő (input és output árak 1 millió tokenre vetítve dollárban), pontos számításokra van szükség. A vezetőség azt is látni akarja, mely napszakokban a legaktívabbak a felhasználók, és mennyi pénz folyt el hibás kérésekre.
Tudnivalók a megoldáshoz:
-
Törekedj arra, hogy a táblázatod akkor is működjön, ha az adatbázis sora bővül!
-
Ahol lehet, egyetlen képletet használj, ami másolható!
-
Segédszámításokat az Elemzés munkalapon a Q oszloptól jobbra végezhetsz, de ezeket ne töröld ki!
-
Ha egy részfeladat nem sikerül, a ráépülő feladatoknál dolgozz egy általad beírt fiktív adattal!
Részfeladatok
1. Töltsd be a 𝚛𝚎𝚚𝚞𝚎𝚜𝚝𝚜_𝟸𝟶𝟸𝟺.𝚝𝚡𝚝 tabulátorokkal tagolt, UTF-8 kódolású szövegfájlt egy új munkafüzetbe! A munkalap neve legyen 𝗟𝗼𝗴, és az adatokat az A1-es cellától kezdve helyezd el.
2. Ugyanebbe a munkafüzetbe, egy új, 𝗔𝗿𝗮𝗸 nevű munkalapra importáld be az 𝚊𝚛𝚕𝚒𝚜𝚝𝚊_𝚟𝟸.𝚌𝚜𝚟 pontosvesszővel tagolt állományt! Az importálás során figyelj arra, hogy az árak tizedesvesszővel jelenjenek meg helyesen (a forrásban pont lehet az elválasztó).
3. A 𝗟𝗼𝗴 munkalapon szúrj be két új sort a táblázat tetejére! Az A1-es cellába írd be: „AI Költségelemzés - 2024”, és formázd Címsor 1 stílussal vagy 16-os betűmérettel, félkövéren.
4. A harmadik sorban lévő oszlopfejléceket (ID, Timestamp, Model_Full, Input, Output, Status, Latency_ms) igazítsd középre, legyen félkövér a betűtípus, és állíts be szürke háttérszínt. Rögzítsd az ablakot úgy, hogy görgetésnél az első 3 sor mindig látható maradjon.
5. A B oszlopban található időbélyegek (pl. 2024-05-12T14:30:00) szövegként érkeztek. Alakítsd át őket valódi Excel dátum-idő formátumra! Ha szükséges, használj segédoszlopot vagy a Szövegből oszlopok funkciót, de a végeredmény a B oszlopban legyen, „éééé.hh.nn óó:pp” formátumban.
6. A C oszlopban a 𝙼𝚘𝚍𝚎𝚕_𝙵𝚞𝚕𝚕 mező tartalmazza a modell nevét és verzióját egyben (pl. „gpt-4-0613” vagy „claude-3-opus-20240229”). A H oszlopban, 𝗧𝗶𝘀𝘇𝘁𝗮_𝗡𝗲𝘃 fejléccel hozz létre egy képletet, amely levágja az utolsó kötőjelet és az utána lévő verziószámot, és csak a modell tiszta nevét adja vissza (pl. „gpt-4” vagy „claude-3-opus”).
7. Az I oszlopban Szolgáltató fejléccel határozd meg a modell gyártóját! Ha a tiszta név tartalmazza a „gpt” szövegrészletet, akkor „OpenAI”, ha a „claude” szót, akkor „Anthropic”, minden más esetben „Egyéb” jelenjen meg.
8. A J oszlopban 𝗜𝗻𝗽𝘂𝘁_Ár ($) néven keresd ki az 𝗔𝗿𝗮𝗸 munkalapról az adott modellhez tartozó input egységárat (1M tokenre). Használj hibakezelést: ha a modell nem található az árlistában, a cella értéke 0 legyen, ne hibaüzenet!
9. A K oszlopban 𝗢𝘂𝘁𝗽𝘂𝘁_Ár ($) néven végezd el ugyanezt a keresést a kimeneti (output) árakra vonatkozóan is.
10. Az L oszlopban Költség néven számítsd ki az adott sor teljes költségét dollárban! A számítás képlete: (Input tokenek * Input ár + Output tokenek * Output ár) / 1 000 000. Az eredményt formázd pénznemként ($), 4 tizedesjegy pontossággal.
11. Az M oszlopban 𝗩𝗮𝗹𝗶𝗱 fejléccel hozz létre egy logikai vizsgálatot! A kérés csak akkor számít érvényesnek (IGAZ), ha a Status kód 200, ÉS a válaszidő (Latency_ms) kevesebb, mint 5000 ms. Minden más esetben HAMIS legyen.
12. Feltételes formázással állítsd be, hogy a 𝗟𝗼𝗴 munkalapon azok a sorok, ahol a Status kód nem 200 (tehát hiba történt), halványpiros kitöltéssel és sötétpiros betűszínnel jelenjenek meg a teljes táblázat szélességében (A:M oszlopok).
13. Az N oszlopban 𝗡𝗮𝗽𝘀𝘇𝗮𝗸 néven kategorizáld a hívásokat a B oszlop (Időbélyeg) alapján! Ha az időpont 06:00 és 18:00 közé esik (a határokat is beleértve), akkor „Nappal”, egyébként „Éjszaka” legyen a cella tartalma.
14. Szúrj be egy új munkalapot Összesítés néven! Készíts ide egy statisztikát, amely megmutatja, hogy Szolgáltatónként (OpenAI, Anthropic, Egyéb) mennyi volt a teljes költség és mennyi volt az átlagos válaszidő.
15. Az Összesítés munkalap E1-es cellájába írj be egy tetszőleges modelltípust (pl. „gpt-4”). Az E2-es cellában adatbázis-függvény segítségével (pl. 𝙰𝙱.𝚂𝚉𝚄𝙼 vagy 𝙰𝙱.𝙳𝙰𝚁𝙰𝙱) határozd meg, hogy az adott modellből hány darab *sikeres* (Status=200) kérés érkezett.
16. Számítsd ki az Összesítés munkalapon (tetszőleges üres cellában), hogy mekkora volt a legnagyobb egyszeri költség, amit egy *Éjszakai* műszakban történt hívás generált. Használj tömbképletet vagy 𝙼𝙰𝚇𝙷𝙰 függvényt!
17. Készíts egy kimutatást (Pivot táblát) egy új munkalapra 𝗣𝗶𝘃𝗼𝘁 néven! A kimutatás sorai a 𝚂𝚣𝚘𝚕𝚐á𝚕𝚝𝚊𝚝ó𝚔 és azon belül a 𝚃𝚒𝚜𝚣𝚝𝚊_𝙽𝚎𝚟-ek legyenek. Az értékek mezőben jelenítsd meg az 𝙸𝚗𝚙𝚞𝚝 és 𝙾𝚞𝚝𝚙𝚞𝚝 tokenek összegét.
18. Szűrd a kimutatást úgy, hogy csak a 2024. májusi adatok látszódjanak (használhatsz idővonalat vagy szeletelőt is, ha a forrásadatot táblázattá alakítottad, vagy szűrheted a mezőlistában is).
19. Az Összesítés munkalapon készíts egy kombinált diagramot a 14-es pontban kiszámolt adatokból! A költségek csoportosított oszlopdiagramon jelenjenek meg (bal oldali tengely), az átlagos válaszidő pedig vonaldiagramon (jobb oldali, másodlagos tengely).
20. A diagram címe legyen „Költség és Performancia”. A jelmagyarázatot helyezd alulra, és az oszlopok adatfeliratait kapcsold be.
21. Állítsd be a 𝗟𝗼𝗴 munkalap nyomtatási képét úgy, hogy fekvő tájolású legyen, és minden oszlop férjen ki egy oldal szélességben. Az ismétlődő sorok fent az 1-3. sorok legyenek minden oldalon.
22. Védd le az 𝗔𝗿𝗮𝗸 munkalapot jelszó nélkül úgy, hogy a felhasználó ne tudja módosítani az árakat, de a cellákat ki tudja jelölni.
