{"id":2807,"date":"2009-02-21T09:00:00","date_gmt":"2009-02-21T01:00:00","guid":{"rendered":"http:\/\/www.neolee.net\/?p=2807"},"modified":"2025-02-13T18:01:29","modified_gmt":"2025-02-13T10:01:29","slug":"data-excel-%e7%94%a8%e7%a8%8b%e5%ba%8f%e9%ab%98%e6%95%88%e5%88%b6%e8%a1%a8%ef%bc%88%e8%a4%87%e9%9b%9c%e6%b8%ac%e7%ae%97%e5%8f%8a%e8%aa%bf%e8%a9%a6%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.neolee.net\/?p=2807","title":{"rendered":"Excel \u7528\u7a0b\u5e8f\u9ad8\u6548\u5236\u8868\uff08\u8907\u96dc\u6e2c\u7b97\u53ca\u8abf\u8a66\uff09"},"content":{"rendered":"\r\n<p>\u5728\u5236\u5b9a\u50f9\u8868\uff08\u5305\u62ec\u6295\u8cc7\u8868\uff09\u904e\u7a0b\u4e2d\u9700\u8981\u8907\u96dc\u8a08\u7b97\u4ee5\u53ca\u4e0d\u65b7\u8abf\u6574\u8a08\u7b97\u7d50\u679c\uff0c\u4ee5\u671f\u9054\u5230\u6700\u4f73\u6548\u679c\uff0c\u9762\u5c0d\u6210\u5343\u4e0a\u842c\u500b\u6578\u5b57\uff0c\u591a\u500b\u689d\u4ef6\u8a2d\u7f6e\uff0c\u591a\u500b\u516c\u5f0f\u5f15\u7528\uff0c\u62c9\u8868\u8a08\u7b97\u662f\u4f4e\u7d1a\u904b\u7528\uff0c\u4e14\u6613\u51fa\u932f\uff0c\u6700\u9ad8\u6548\uff0c\u6700\u5b89\u5168\u7684\u8fa6\u6cd5\u5c31\u662f\u7528\u7a0b\u5e8f\u914d\u5408\u8a08\u7b97\uff0c\u9019\u662f\u6211\u5beb\u7684excel VB\u7a0b\u5e8f\u4ee3\u78bc\uff0c\u9644\u6e90\u78bc\uff0cexcel\u6587\u4ef6\u4e0d\u63d0\u4f9b\uff0c\u611f\u8208\u8da3\u7684\u81ea\u5df1\u5617\u8a66\uff0c\u4f46\u81f3\u5c11\u8981\u6703\u4f7f\u7528excel\u7684\u5b8f\u529f\u80fd\u3002<\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"330\" class=\"wp-image-2808\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData-1024x330.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData-1024x330.jpg 1024w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData-300x97.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData-768x248.jpg 768w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData-1536x496.jpg 1536w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData.jpg 1683w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"671\" height=\"382\" class=\"wp-image-2809\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData02.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData02.jpg 671w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData02-300x171.jpg 300w\" sizes=\"auto, (max-width: 671px) 100vw, 671px\" \/><\/figure>\r\n\r\n\r\n\r\n<p><!--more--><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"231\" class=\"wp-image-2810\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData03-1024x231.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData03-1024x231.jpg 1024w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData03-300x68.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData03-768x173.jpg 768w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData03.jpg 1399w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"977\" height=\"569\" class=\"wp-image-2811\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData04.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData04.jpg 977w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData04-300x175.jpg 300w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData04-768x447.jpg 768w\" sizes=\"auto, (max-width: 977px) 100vw, 977px\" \/><\/figure>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"822\" height=\"944\" class=\"wp-image-2812\" src=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData05.jpg\" alt=\"\" srcset=\"https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData05.jpg 822w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData05-261x300.jpg 261w, https:\/\/www.neolee.net\/wp-content\/uploads\/2020\/07\/MakingPriceData05-768x882.jpg 768w\" sizes=\"auto, (max-width: 822px) 100vw, 822px\" \/><\/figure>\r\n\r\n\r\n\r\n<p><textarea style=\"margin: 2px; padding: 2px; background-color: #ffffe1; border: 1px solid; font-size: 9pt;\" cols=\"60\" name=\"\" rows=\"10\">\r\nSub direction()\r\n\r\nDim temp\r\n\r\nFor i = 4 To 195\r\n\r\ntemp = Range(&#8220;f&#8221; &amp; i)\r\n\r\nSelect Case temp\r\n\r\nCase &#8220;A1&#8221;, &#8220;F1&#8221;, &#8220;A2&#8221;, &#8220;F2&#8221;, &#8220;G1&#8221;, &#8220;G2&#8221;\r\ntemp = &#8220;\u5317&#8221;\r\n\r\nCase &#8220;C1&#8221;\r\ntemp = &#8220;\u897f&#8221;\r\n\r\nCase &#8220;B2&#8221;, &#8220;E1&#8221;\r\ntemp = &#8220;\u897f\u5357&#8221;\r\n\r\nCase &#8220;B1&#8221;, &#8220;E2&#8221;\r\ntemp = &#8220;\u4e1c\u5357&#8221;\r\n\r\nCase &#8220;C2&#8221;\r\ntemp = &#8220;\u4e1c&#8221;\r\n\r\nCase &#8220;D1&#8221;, &#8220;D2&#8221;\r\ntemp = &#8220;\u5357&#8221;\r\nEnd Select\r\n\r\n\r\n\r\nRange(&#8220;J&#8221; &amp; i) = temp\r\n\r\nRange(&#8220;M&#8221; &amp; i) = DirectionPrice(temp)\r\n\r\nNext\r\n\r\nEnd Sub\r\nSub FloorPrice()\r\nDim SFloor, EFloor, MFloor, PFloor, pricetemp\r\n\r\nMFloor = 7\r\n\r\nFor i = 4 To 195\r\n\r\nPFloor = CLng(Range(&#8220;C&#8221; &amp; i))\r\n\r\nSelect Case PFloor\r\n\r\n    Case (PFloor = MFloor)\r\n\r\n        pricetemp = 0\r\n    Case Else\r\n\r\n        pricetemp = (PFloor &#8211; MFloor) * 30\r\n\r\nEnd Select\r\n\r\nIf PFloor = 4 Then pricetemp = pricetemp &#8211; 20 &#8216;4\u697c\u8c03\u6574\u548c3\u697c\u4e00\u6837\r\n\r\nIf PFloor &gt; MFloor + 1 Then pricetemp = pricetemp + 20 &#8216;8\u697c\u4ee5\u4e0a\u4ef7\u683c\u5fae\u8c03\r\n\r\nIf PFloor = 13 Then pricetemp = pricetemp &#8211; 20 &#8216;\u9876\u697c\u4ef7\u683c\u5fae\u8c03\r\n\r\nRange(&#8220;L&#8221; &amp; i) = pricetemp\r\n\r\n\r\nNext\r\n\r\n\r\nEnd Sub\r\nSub ResidencePrice()\r\nDim pResidence, pricetemp\r\n\r\nFor i = 4 To 195\r\n\r\npResidence = Range(&#8220;F&#8221; &amp; i)\r\n\r\nSelect Case pResidence\r\n\r\n    Case &#8220;D1&#8221;, &#8220;D2&#8221;\r\n\r\n        pricetemp = 1.1\r\n    Case &#8220;C1&#8221;, &#8220;C2&#8221;\r\n        pricetemp = 0.95\r\n    Case &#8220;E1&#8221;, &#8220;E2&#8221;\r\n        pricetemp = 1.05\r\n        Case &#8220;B1&#8221;, &#8220;B2&#8221;\r\n        pricetemp = 1.025\r\n    Case Else\r\n        pricetemp = 1\r\n\r\nEnd Select\r\n\r\n\r\nRange(&#8220;K&#8221; &amp; i) = pricetemp\r\n\r\n\r\nNext\r\nEnd Sub\r\n\r\nSub JGPrice()\r\nDim pBuiding, pResidence, pricetemp\r\n\r\n\r\n\r\nFor i = 4 To 195\r\n\r\npBuiding = Range(&#8220;B&#8221; &amp; i)\r\n\r\npResidence = Range(&#8220;F&#8221; &amp; i)\r\n\r\n\r\nIf pBuiding = &#8220;A&#8221; Then\r\n\r\nSelect Case pResidence\r\n\r\n    Case &#8220;F1&#8221;, &#8220;C1&#8221;, &#8220;E1&#8221;\r\n\r\n        pricetemp = 98.85\r\n   Case &#8220;D1&#8221;\r\n   \r\n       pricetemp = 65\r\n       \r\n    Case Else\r\n\r\n        pricetemp = 0\r\n\r\nEnd Select\r\nEnd If\r\n\r\nIf pBuiding = &#8220;B&#8221; Then\r\n\r\nSelect Case pResidence\r\n\r\n    Case &#8220;F2&#8221;, &#8220;E2&#8221;, &#8220;C2&#8221;\r\n\r\n        pricetemp = 98.85\r\n        \r\n     Case &#8220;D2&#8221;\r\n   \r\n       pricetemp = 65\r\n    Case Else\r\n\r\n        pricetemp = 0\r\n\r\nEnd Select\r\nEnd If\r\n\r\n\r\nRange(&#8220;N&#8221; &amp; i) = pricetemp\r\n\r\n\r\nNext\r\nEnd Sub\r\n\r\n\r\nFunction DirectionPrice(direction)\r\n\r\nDim pricetemp\r\n\r\nSelect Case direction\r\n\r\nCase &#8220;\u5317&#8221;\r\npricetemp = 0\r\n\r\nCase &#8220;\u4e1c\u5317&#8221;, &#8220;\u897f\u5317&#8221;\r\npricetemp = 25\r\n\r\nCase &#8220;\u4e1c&#8221;, &#8220;\u897f&#8221;\r\npricetemp = 50\r\n\r\nCase &#8220;\u897f\u5357&#8221;, &#8220;\u4e1c\u5357&#8221;\r\npricetemp = 75\r\n\r\nCase &#8220;\u5357&#8221;\r\npricetemp = 100\r\n\r\nEnd Select\r\n\r\nDirectionPrice = pricetemp\r\n\r\nEnd Function\r\n\r\n\r\nSub find2()\r\nCall findcancel\r\nDim floor, rownum\r\n\r\nFor i = 4 To 195\r\nfloor = Range(&#8220;C&#8221; &amp; i)\r\nIf (floor Mod 2 = 1) Then\r\n\r\n\r\nRows(i).EntireRow.Hidden = True\r\n\r\nEnd If\r\n\r\nNext\r\nEnd Sub\r\nSub find1()\r\nCall findcancel\r\nDim floor\r\n\r\nFor i = 4 To 195\r\nfloor = Range(&#8220;C&#8221; &amp; i)\r\nIf (floor Mod 2 = 0) Then\r\n\r\n\r\nRows(i).EntireRow.Hidden = True\r\n\r\n\r\nEnd If\r\n\r\nNext\r\nEnd Sub\r\nSub findcancel()\r\n\r\nRows(&#8220;4:195&#8221;).EntireRow.Hidden = False\r\n\r\n\r\nEnd Sub\r\n\r\n\r\n\r\n<\/textarea> <br \/><br \/><textarea style=\"margin: 2px; padding: 2px; background-color: #ffffe1; border: 1px solid; font-size: 9pt;\" cols=\"60\" name=\"\" rows=\"10\">Sub Hide()\r\n&#8216;\r\n&#8216; Hide Macro\r\n&#8216; \u5b8f\u7531 ELooNG \u5f55\u5236\uff0c\u65f6\u95f4: 2009-2-21\r\n&#8216;\r\n\r\n&#8216;\r\n    Range(&#8220;AL:AL,AN:AN,AP:AP,AR:AR,AT:AT,AV:AV,AX:AX,AZ:AZ&#8221;).Select\r\n    Selection.EntireColumn.Hidden = True\r\n    Range(&#8220;aj239&#8221;).Select\r\nEnd Sub\r\n\r\nSub View()\r\n\r\n    Columns(&#8220;AJ:BA&#8221;).Select\r\n    Selection.EntireColumn.Hidden = False\r\n    Range(&#8220;aj239&#8221;).Select\r\nEnd Sub\r\n\r\n\r\n\r\n<\/textarea><\/p>\r\n","protected":false},"excerpt":{"rendered":"<p>\u5728\u5236\u5b9a\u50f9\u8868\uff08\u5305\u62ec\u6295\u8cc7\u8868\uff09\u904e\u7a0b\u4e2d\u9700\u8981\u8907\u96dc\u8a08\u7b97\u4ee5\u53ca\u4e0d\u65b7\u8abf\u6574\u8a08\u7b97\u7d50\u679c\uff0c\u4ee5\u671f\u9054\u5230\u6700\u4f73\u6548\u679c\uff0c\u9762\u5c0d\u6210\u5343\u4e0a\u842c\u500b\u6578\u5b57\uff0c\u591a\u500b\u689d\u4ef6\u8a2d\u7f6e\uff0c\u591a\u500b\u516c\u5f0f\u5f15\u7528\uff0c\u62c9\u8868\u8a08\u7b97\u662f\u4f4e\u7d1a\u904b\u7528\uff0c\u4e14\u6613\u51fa\u932f\uff0c\u6700\u9ad8\u6548\uff0c\u6700\u5b89\u5168\u7684\u8fa6\u6cd5\u5c31\u662f\u7528\u7a0b\u5e8f\u914d\u5408\u8a08\u7b97\uff0c\u9019\u662f\u6211\u5beb\u7684excel VB\u7a0b\u5e8f\u4ee3\u78bc\uff0c\u9644\u6e90\u78bc\uff0cexcel\u6587\u4ef6\u4e0d\u63d0\u4f9b\uff0c\u611f\u8208\u8da3\u7684\u81ea\u5df1\u5617\u8a66\uff0c\u4f46\u81f3\u5c11\u8981\u6703\u4f7f\u7528excel\u7684\u5b8f\u529f\u80fd\u3002<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[18,16],"tags":[],"class_list":["post-2807","post","type-post","status-publish","format-standard","hentry","category-property","category-cyberworld"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts\/2807","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2807"}],"version-history":[{"count":0,"href":"https:\/\/www.neolee.net\/index.php?rest_route=\/wp\/v2\/posts\/2807\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2807"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2807"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.neolee.net\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2807"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}