ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ¦h±ø¥ó­pºâ¥æ©ö©ú²Ó

[µo°Ý] ¦h±ø¥ó­pºâ¥æ©ö©ú²Ó

¦U¦ì«e½ú¦n!
¸ê®Æ®w¬°©ú²Ó±b¡A¨Ì¥æ©ö©ú²Ó¤¤ªº¥æ©ö¹ï¶H.¬ì¥Ø.¤é´Á.±ø¥ó­pºâ¬Û¹ïÀ³¤§¥æ©ö±¡§Î¡A
¡°©ú²Ó±bA.Rµ¥Äæ¦ì¸ê°T¤£¥¿½T...¦]½d¨Ò¬O¿z¿ï¼´¥Xªº¡C(¸Ó¸ê®Æ¤£»Ý¥Î¨ì)
¦³¥ý¸Õ°µ¤@ª©¡A¦ý¹J¨ì°ÝÃD¬O¡A­Y¥æ©ö¹ï¶H¤£¦P«h»Ý¹ï¨C­Ó¥æ©ö¹ï¶H¥H¦U§Oªºµ{¦¡½X³B²z¡A¥H¤Î¸ê®Æ­Y¤£¥ý¸g±Æ§Ç¤]·|¥X¿ù¡A
·Q½Ð°Ý¬O§_¦³¤ñ¸û«K±¶ªº°µªk¶Ü...?
»Ý¨D¦p¤U:sheets(¥æ©ö©ú²Ó)
¦¬¤J²bÃB(N)=¾P³f¦¬¤J(H)-¤W´Á¦Û½Õ(I)+¥»´Á¦Û½Õ(J)-°h¦^/§éÅý(L)¡C
Àç·~¦¨¥»(Q)=5¶}ÀY¤§¶O¥Î¡C(«D¥þ³¡)¡C
Àç·~¶O¥Î(S)=6¶}ÀY¤§¶O¥Î¡C(«D¥þ³¡)¡C
¶i³f(X)=1215+1218+5132(¤£§t¤J®w¥¼½Ð´Ú¤Î¦b³~)¡C
¤W¤ë¼È¦ô(V)=1215+5132+1218¤J®w¥¼½Ð´Ú¤Î¦b³~¶U¤è¨R¾P¼Æ¡C
´Á¥½¼È¦ô(AB)=1215+5132+1218¤J®w¥¼½Ð´Ú¤Î¦b³~¡C
©T¸êÁÊ(AG)=©T©w¸ê²£¬ì¥Ø­É¤è¼W¥[¼Æ¡C
©T¸ê°â(AI)=©T©w¸ê²£¬ì¥Ø¶U¤è¼W¥[¼Æ¡C
¥Ø«e³£¼È¤£¦Ò¼{­ì¹ô¼Æ¡A¦]¬°¥~¹ô³£¥u¶ñ¦bºK­n¦Ó¤w¡A¤£ª¾¹D¦p¦ó³B²z..
ªþ¤WªþÀÉ..¦b³Ò·Ð¯àªÌ¥X¤â²¤Æ¤F¡A·PÁ¤£ºÉ!

TEXT¥æ©ö²Î­pGM_20230430.zip (136.42 KB)

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-12-15 10:46 ½s¿è

¦^´_ 1# shuo1125

³o­ÓÃD¥Ø

½T¹ê¦³¥¿³Wªº§@ªk  ¦Ó¥B ¥u¯à¥Î³o­Ó°µªk
¬Ý¨ì§Aªº¥N½X«á §Ú»{¬°§A¸Ó¨«¦V³o¤è¦V

³oºØ ¤£¦P¦hºØ x ¦h¦¸ x ¦h±ø¥ó x ¦hªí x ¦h¿z¿ï  °ò¥»¤W´N¥u¯à«ü±æSQL  ´N¥u¯à«ü±æSQL  ´N¥u¯à«ü±æSQL
«Ü­«­n»¡¤T¦¸ §O®³VBA¸Õ
°ò¥»¤W¤½¥q°£¤F±MªùªºMIS °ò¥»¤W¤£¥i¯àÅý«D±M·~¤H¤h¶}±Ò¸ê®Æ®w¾Þ§@

©Ò¥H§Ú«Øij
³Ì¨Î¤è¦¡
¦Û¤v¦w¸ËSQL server + SSMS (¦w¸Ë ¾Þ§@ ¨Ï¥Î ¤]¥i¥H¥sGPT±Ð)
±N³oµ§¸ê®Æª½±µ¶×¤J©Î¶K¤WSQL  ©ÎªÌ¤j¶qªº¸Ü  ª½±µ¸òmis®³.bakÀÉ .csv .xlsxÀÉ®×

µM«áGPT4+SQL»yªk ¥sGPTÀ°¥X¥N½X 9¦¨³£¯à¬í±þ(¤£¹L°ò¦­n¦Û¾Ç¼v¤ù¦Ü¤Ö40¤p®É ¦ý«OÃÒ¤£Á«)
§Aªº°ÝÃDÀ³¸Ó¥i¥H¦b3~10¤ÀÄÁ¤º¸Ñ¨M  ¦Ó¥B¬Ý©¹«á³oºØ¬dªí¿z¿ï¦³¦h¤Ö°ÝÃD³£¯à§Ö³t¸Ñ¨M


­Y­n¥ÎVBA °ò¥»¤W ¤]¬Oª½±µ½Õ¥Îadodb.connect SQL¥N½X(¦ý¥\¯à´Ý¯Ê »Ý¦UºØªá¦¡)
¦ý³o­Ó¤è¦V ºô¤W·|·dªº¤H °ò¥»¤W¤£·|«ÝµÛ ¥L­Ì·|ª½±µÂà¦Vª½±µ¨Ï¥ÎSQL
·Q¬dVBA+SQL °ò¥»¤W¬d¨ìªº¸ê®Æ«D±`¤Ö ¤Ï¦ÓGPT¸ê®Æ¦h  ¾ã¦X°_¨Ó¦³¾÷·|¨Ï¥Î
´Nºâ°ÝGPT ¤]¥u¯àµ¹§A6¦¨µª®× 4¦¨¦Û¤v¸É

¦ý§Ú¬Ý¤F¬Ý¸ê®Æ  ¤]°µ¤£¤F
­ì¦]¬OGªí¤¤ 5.6¶}ÀYªº ¨S¦³¥bµ§Mn¸ê®Æ (§Ú³Ð¤F°²¸ê®Æ)
Mªí¤¤ 5.6¶}ÀYªº ¤]¨S¥bµ§  µLªk´ú¸Õ

§Ú·|¹Á¸Õ ¬O¦]¬°¥¿¦b½m²ß¹ï¥I«D¥¿³W¤Æ®æ¦¡ªº ªá¦¡SQL»yªk
»Ý¶}¤@­Ó¤u§@ªí1 ¦A°õ¦æ (­n§â§A­ì¥»ªº¥N½X¥þ³¡ºb±¼)
¦ý¤u§@ªí1¨S¦³¥ô¦ó¸ê®Æ

Sub t1()
i = Split("Provider=Microsoft.,Jet.OLEDB.4,.0;Extended Properties=Excel ,8,.0;Data Source=", ",")
If Application.Version > 12 Then i(1) = "ACE.OLEDB.12": i(3) = 12
Set cn = CreateObject("adodb.connection"): cn.Open Join(i, "") & ThisWorkbook.FullName

q = "select [DATA_§Ç],[DATA_¤ë],[DATA_¾lÃB] from [G©ú²Ó±b$A1:T] where "
q = q & " [DATA_§Ç] like '5__'  and  [DATA_ºK­n] like '%mn%' "
Set rs = cn.Execute(q)

Sheets("¤u§@ªí1").[A2].CopyFromRecordset rs
Sheets("¤u§@ªí1").[a1:d1] = Split("DATA_§Ç,DATA_¤ë,DATA_¾lÃB,Á`¾lÃB", ",")


q1 = "select sum(DATA_¾lÃB) as sumX from ( " & q
q1 = q1 & " )  "
Sheets("¤u§@ªí1").[D2].CopyFromRecordset cn.Execute(q1)

'...
End Sub

·íµM§A·Q­n¥Î¨ä¥L¸ê®Æ®w¤]¥i¥H  ¤Ï¥¿³£¦³¥~±¾¹Ï§Î¤Æ¤¶­±
mysql sqlserver ptsql
TEXT¥æ©ö²Î­pGM_20230430.zip (137.09 KB)

TOP

¦^´_ 2# singo1232001
¼y¤j¦n~
¦³¬Ý¹L§A¤À¨ÉªºSQL¼gªk¡A§A¤]¬O¼ö¤ß¤H¤h¡A
ºô¤W°w¹ïVBA¤Þ¥ÎADODBªº½d¨Ò½T¹ê«Ü¤Ö...
¦ý©|¥¼ªá®É¶¡¥h²z¸Ñ¥D¦]¬O...­YVB§Ú³£©|¥¼²z¸Ñ³z¹ý¤ÎÀ³¥Î«á¡A´N¥h¾Ç²ß¨ä¥L»â°ì·Pı·|§ó¦Y¤O....
«á¨Ó§ÚÁÙ¬Oª½±µ¤À¶}¼gµ{§Ç¤Î¤Þ¥Î¦h¦r¨å¡A¼È®É¯à¸Ñ¨M°ÝÃD«K¦n¡A
(¦ý³o¯ÊÂI´N¬O·í¥X²{·s«¬ºA¥æ©ö¡A´N­n¦A½Õ¾ã¥N½X¡C)
¦]¤½¥q¨t²Î¨ä¹ê¬O¥ÎSQL¶}µoªº¡A©Ò¥H§Ú¤j·§ª¾¹D¨ä¹ê¥HSQL½Õ¥Î¬O³Ì¨Î¸Ñ¡AµL©`µ{«×¦³­­¥u¯àº¥¶i¾Ç²ß¤F...
·PÁÂÃöª`¦¹©«¨Ã´£¨Ñ¤è¦V¡AÁÂÁ±z!!!

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-12-15 18:35 ½s¿è

¦^´_ 3# shuo1125


±q§Ú¥Ø«eªºÆ[¹î ´N·í«e§Aªº¥\¤O °ò¥»ºN¨ìªùÂe«Ü°÷¤F
­Y¨S¥[¼s«× ¾¨¦­¸õ¨ìSQL ¥Ñ©ó§Aªº·~°È©Ê½è ¾Ç²ßVBA À³¸Ó·|§ó¦Y¤O ¥¿±`À³¸Ó¬O·|¥¢±Ñ
·íµM¦¹ÃD¥Î3~4ºû°}¦C ¯à¸Ñ ©Î¦r¨å´O®M ¨Ò¦p ar(0)(1)(2)(3) ³oÃþ°}¦C (¦ý­n½ÆÅç´ú¸Õµª®× ·|¬O¤@­Ó§óÄY­«ªº°ÝÃD SQL¯à½ÆÅ祿½T©Ê)
¹ï¤ñª½±µ¸õ¨ìSQL ªº±¡ªp ­Y¨t²Î¶q¤Æ ¤j·§¬O¦p¤Uªí
-------------------------
¦³GPTªº±¡ªp¤U ¥´5¤À
¹ï¤ñ §tSQL ,³æ¾avba
»´ÃP  4,1  (¥ÎGPT¶W¯Å©G»y ¸ò ÅÞ¿è¤À¸Ñ )  GPT¶W©G: ¥Î¥Í¬¡¥Í°Ê¨Æ±¡»¡©úÅÞ¿è¬yµ{ ¨ÃÁ|¨â¨Ò       GPTÅÞ¿è¤À¸Ñ:±N¥N½XÅÞ¿è¬yµ{¤À¸Ñ¨Ã¸Ô²Ó»¡©ú
²³æ  5,2  (GPTµL¸£¥Íµª®× ¥ô§g¿ï¤­ºØ¤£¦P¤f¨ý¸Ñµª)
¹ê¥Î  5,1  (1¤À¬O¯uªºµLªk ¸Ó¯S®íÃD«¬­­¨î)
¾Çµ{  5.2  (¥u¥]¬A¸ê®Æµ²ºcªº³¡¤À ¨ä¥LÃþ«¬¤£ºÞ,SQL¦³GPT¾Ç²ß®É¶¡¬ù3­Ó¤ë,VBA­Y¥u¦³¸ê®Æµ²ºc¤ÀªR³¡¤À ¤j·§1¦~ ³£³Ì©éªº±¡ªp¤U)

¨SGPTªº±¡ªp¤U
»´ÃP  2,3 (¤@¶}©lªº¾Ç²ßªº·K´a·P,¸ò¤@¶}©l¾Ç°}¦C¦r¨å®t¤£¦hªº·Pı)
²³æ  3,2 (¨S¤H±Ð ªá¦¡ÃD¤£·|¸Ñ)
¹ê¥Î  3,1 (¨S¤H±Ð ªá¦¡ÃD¤£·|¸Ñ,¦ý§AªºÃD«¬¦bSQL¤¤¬O°ò¦ÃD,¤j·§¾Ç15¤Ñ(20~30¤p®É)´N·|ªº¨ººØ)
¾Çµ{  2,2  (¾Ç²ß®É¶¡¬ù1¦~ ¨S¤H¸ÑÃD)
----------------------
³Ì«á¨ú¨M½Ä¤£½Ä¦Ó¤w
²¦³º ¦¹ÃD¥Îvbaµw¤W¦³¸Ñ  ¤@ÃD¬ù­n¸Ñ3~4H  ¦ý±ø¥ó·|ÀH®É­×§ï ¥i¯à¦³100ºØ«¢©i¹p¯S
­YSQL ´N¬O3¤ÀÄÁªº¨Æ±¡
¤j·§¬O³o¼Ë ¶È¦@°Ñ¦Ò

¥t¥~ ¸ê®Æ¤ÀªR ¸ò±z­Ì¤½¥qªºSQL¨t²Î µLÃö
¥ô¦ó¸ê®Æ³£¥i¥H¦Û«ØSQL¤ÀªR ¥¿±`¨Ó»¡
À³¸Ó¬O¤½¥q¦øªA¾¹­ì¥»ªº¤@­Ó¨t²Î  
§A¥ÎµêÀÀ¾÷©ÎªÌ¥Î­Ó¤H¹q¸£ Äé¤@­Ó¥t¥~­Ó¤H¥ÎªºSQL ¨âªÌµLÃö ¿W¥ß  §O¥Îaccess ¤Ó¦h­­¨î

TOP

¦^´_ 4# singo1232001
¼y¤j¦n~
À³¸Ó»¡·í®É·|¶}©l±µÄ²VB¬O¬°Â²¤Æ§@·~¡A¦]¤½¥qµLªkÀH·N¨Ï¥Î¨ä¥L³nÅé¤Îµ{¦¡¡A©Ò¥H¯à¥Îªº¤]¥u¦³XL...
¦ý¤]¦p¦P§A»¡¦]·~°È©Ê½èÃö«Y¡A¦b­è¤Jªù§¹¥þ¤£À´ªº±¡ªp¤U¬Ý¤F¬ÛÃö®ÑÄy¤Îºô¤W½d¨ÒÁÙ¬OµLªk¹B¥Î¦b¤u§@¤W¡A
±oª¾¦¹½×¾Â¤W¨Ó´£°Ý«áÀò±o­ã¤jªºÀ°§U¡A¤@¶}©l·íµM¥u¬O·Q¸Ñ¨M°ÝÃD¡AºCºC¥h²z¸Ñ«á¤~µo²{...
­ì¨Ó§Ú¬O±o¨ì¦h»ò¼F®`ªº°ª¤âªºÀ°§U¡Aº¥º¥¦a¤~³v¨B³v¶µªº¥h¾Ç²ß¡A·íµM­ã¤jªºÅÞ¿è¸ò§Þ¥©«D±`¤H©Ò¤Î.....
¤£¹LÀHµÛ¦h¬Ý°ª¤âªºµ{§Ç¤Î½s½X¡A±q³s°j°é¬O¤°»ò³£¤£ª¾¹D¡A¤]¨ü¨ì²³¦h¤HªºÀ°§U¡A¶}©l¯à¤j·§½Õ¾A¥N½X²©öªº¹B¥Î¦b¤u§@¤W¡A
ÁöµM½T¹ê·f¤£¤WÃä¡A¦ýÁÙ¬O§Æ±æ¯àºCºC§ó²`¤J¤F¸ÑVB¡A´Nºâ¥¢±Ñ¤]µL§«¡A²¦³º¬O¦Û¤v¾Ç²ß¥Î¡C
-------------------------------------------
¤½¥q¨Ï¥Î¦Û¥D¶}µoªºSQL§@¬°ERP¨t²Î¡A¦ý°]°È¤è­±ªº¾ã¦X¸û¤Ö¡A³o¤]¬O¬°¤°»ò·|·Q­nºÉ¥i¯à§Q¥ÎVB´î¤Ö¤â¤u§@·~¡A
¦Ó¤§©Ò¥H·|»¡³Ì¨Î¸Ñ¬O¦]¬°¬Ý¹L¸ê°T¼gªº½sµ{ª½±µ¯à½Õ¥Î¸ê®Æ®w±a¥X©Ò»Ý¸ê°T¡C
©Ò¥H§A«Øij§Úª½±µ¾Ç²ßSQL¬O©È§Ú¨«¤Ó¦hÅs¸ô:)
·|¦A©âªÅ¥h¾Ç²ß~
-------------------------------------------
±z¨¯³Ò¦a¤ÀªR§Q¹ú¡A¼ö±¡ªº´£¨Ñ«Øij©M«ü¾É¡A½×¾Â¯uªº¥Rº¡¼ö¤ß±M·~ªº¤H¤h¡C
·PÁ±z¤Î¤j®a!!!!

TOP

¥»©«³Ì«á¥Ñ jackyq ©ó 2023-12-16 23:27 ½s¿è

¦^´_ 5# shuo1125

§A¼gªkªºÀuÂI´N¬O ¥¦¯à¼È®É¸Ñ¿U¬Ü¤§«æ
³o«Ü­«­n  «Ü¦h®É­Ô ¸Ñ¿U¬Ü¤§«æ¤ñ¤°³£­«­n
¯ÊÂI¬O ¥¦¬O¤@ºØ¬µ¼u¦¡¼gªk  ¥H«á100%·|¤ÞÃz   
¤ÞÃz¤é´N¬O§Acode ¬yµ{¥þ³¡­«·s§ï¼g¤§¤é

¦pªG«D­n¥Î VBA  §Ú¤£·|·Q¥Î¦r¨å
§Ú·|¥Î±Æ§Ç ¦ý§Ú¤£·|¥h¥Î EXCEL ¦Û±aªº¦hÄæ±Æ§Ç
§Ú·|¥h§Ë¤@­Ó¥Î VBA¹ê²{ªº¦hÄæ±Æ§Ç¨ç¼Æ
¦pªG§A¯à¦Û¤v§Ë¤@­Ó¦hÄæ±Æ§Ç¨ç¼Æ
§A¬ÝµÛ¨º­Ó§A¦Û¤v·d¥X¨Óªº¨ç¼Æ
§Aªº¸£¤l¸ÌÀ³¸Ó´N¦ÛµM·|¯B²{¥X «áÄò§¹¬ü¬yµ{¼gªk

¦³«Ü¦h®É­Ô ¬yµ{¼g¤£¦n ¬O¦]¬°¤Ö¤F¤@¨ÇÃöÁä©Ê¥\¯à¨ç¼Æ
©Ò¥H¥u¯à¥Î¼É¤O¦¡¬yµ{

TOP

¥»©«³Ì«á¥Ñ singo1232001 ©ó 2023-12-17 00:43 ½s¿è

¦^´_ 5# shuo1125


    ¤~·Q¨ì  ¥Ø«e³oÃDªº¥D­n°ÝÃD¬O¤§«eµ¹ªº"¸ê®Æ¦³°ÝÃD¦³¯Ê"
§Ú¥i¥H¥Îvba+SQL À°¦£¸Õ§@
¦ý°ÝÃD¬OÀɮפºªº¸ê®Æ¦³°ÝÃD  ©Ò¥H§Ú¤]¤£ª¾¹D­n«ç»ò§@
­n­«·sµ¹¹ïªº¸ê®Æ  ¤£µMÀ³¸Ó¦­¦³¤HÀ°¦£¸ÑÃD¤F ©ÎªÌ¸É¥R»¡©ú¤@¤U ¬O§_¦³­þ¨Ç¬yµ{¨S´£¨ì

¥t¥~ ¥i¥H¤@¦¸´£3~5ºØ ¤£¦Pªº¬d¸ß»Ý¨D  §Ú¥ÎadodbÀ³¸Ó¥i¥H°¨¤W¥Í¥X¨Ó

¨Ò¦p
²Ä1ÃD. 2023,1~12¤ë  ¼Ò½k¦WºÙ 5¶}ÀY Aªí
²Ä2ÃD. Aªí(2022,¬Y¤ë  «D¬Y¦WºÙ 6¶}ÀY )+Bªí(N¦~,N¤ë  ­n¬Y¦WºÙ 6¶}ÀY Nª÷ÃB°Ï¶¡) ¦³¬Û¦Pªº¤~ºâ  ©Î ª÷ÃB»~®t¤º ©Î¬Û¦P¤é´Á
...
...
¹³¤W­±³oÃþÃD¥Ø SQL °tGPT³£¬O¬í¸Ñ

TOP

¥u¯àµ¹­Ó"¼Òª©"³]­p¤è¦V, ²Î­pª÷ÃB¥i¯à­n¦Û¦æ¥h½T»{¤Î­×§ï, ¤j·§²qµÛ¼g//
Xl0000108.rar (70.16 KB)

TOP

¦^´_ 6# jackyq
J¤j¦n~
½T¹ê¬O¬°¤F¥ý§Ö³t±o¨ìµ²ªG¨Ó°µ...±zÁ¿±o¨S¿ù¡A·í¥æ©ö§ÎºA¶V¦hºûÅ@´N¶V¤£©ö¡A¨ìÀY¨Ó¥i¯à´N­n¥´±¼­«½m¤F...
·PÁµ¹¤F¥t¥~¤@­Ó¤è¦V¥h«ä¦Ò¡A¦ý¦]§Ú³s±`³W³£¼g¤£¦n¡A©Ò¥H¼È¤£¦Ò¼{¥H¦Û©w¸q¨Ó°µ...
ÁÂÁ§A!!

TOP

¦^´_ 7# singo1232001
¼y¤j¦n~
¦]°]°È¸ê®Æ¸û¬°±Ó·P...¬G¤j³¡¤À¥u¯à¼È®É¤W¶Ç¼ÒÀÀ¼Æ¾Ú¨Ó´£°Ý..
­Y±z·Q´ú¸Õ¸ê®Æ¡A¦³ªÅ§Ú¦b¾ã²z´ú¸Õ¸ê®Æ¤W¶Ç¡A
ÁÂÁ±z!!

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD