«فرمت سفارشی اعداد» یا به اصطلاح Custom Number Format یکی از ابزارهای کاربردی اکسل برای نمایش اعداد محسوب می شود.
مطمئن هستم خیلی از دوستانی که با اکسل کار می کنند حداقل یک بار سر و کارشون به مشکل نمایش اعداد در اکسل و یا تغییر فرمت اعداد افتاده؛ بعضی ها هم که هر روز با این موضوع روبرو هستند.
کاربرد نرمافزار اکسل اون قدر گسترده هست که فکر میکنم هیچ مدیر پروژه یا کسی که در پروژه فعالیت می کند یک روز هم بدون این برنامه نتواند سر کند.
بعضی مواقع فکر میکنم که دنیای بدون اکسل چقدر سخته و چقدر محاسبات وقتگیر و پیچیده خواهد بود.
بااینحالی که بیش از ۱۵ سال است که با اکسل کار میکنم اگر هرروز یک مطلب جدید از اون یاد بگیرم باز هم تعجب نخواهم کرد، چون میدانم دنیای این برنامه اون قدر گسترده و وسیع است که حالا حالاها باید باهاش کار کرد.
آیا تا حالا با مشکل نوشتن اعداد منفی در اکسل مواجه شده اید و آیا این اعداد آن گونه که خواستید، نمایش داده شده اند؟
آیا با معظل نمایش اعداد علمی در اکسل روبرو شده اید؟
آیا تا کنون خواسته اید که فرمت پیش فرض اعداد را در اکسل تغییر دهید؟ اگر بخواهید آن را تغییر دهید چه کار می کنید؟
همه این مطالب به تغییر فرمت اعداد در اکسل بر می گردد و یه مدت پیش که به دنبال یک مطلب میگشتم اتفاقی به کتاب Excel® Dashboards and Reports, 2nd Edition از نویسنده پرآوازه اکسل John Walkenbach و Mike Alexander برخورد کردم که حیفم اومد اون رو با شما در میان نگذارم.
این مطلب در فصل دوم کتاب با عنوان ارتقا بخشیدن گزارشها از طریق فرمت سفارشی اعداد، قرار دارد که توصیه میکنم حتماً کتاب اصلی را مطالعه نمایید.
سعی میکنم حق مطلب رو تا آنجایی که امکان داره، برای دوستانی که در سمتهای مختلف پروژه و یا در سازمانهای پروژه محور فعالیت میکنند، ادا کنم.
(نکته: برای مطالعه بیشتر هم می توانید به لینک مفاهیم پایهای فرمت سفارشی اعداد مراجعه کنید.)
پ. ن.: اگر با برنامه نویسی اکسل آشنا باشید، شاید براتون جالب باشه که بدانید تیم وب سایت “مدیر پروژه” اخیرا نرم افزاری را توسعه داده به نام AutoWBS که می توانید آن را مطالعه کنید. در این صفحه چندین نکته کاربردی اکسل قرار دارد که منهای استفاده از خود برنامه می تواند برای علاقمندان اکسل بسیار کاربردی باشد.
برای اعمال فرمت سفارشی اعداد به روش زیر عمل کنید:
(البته ناگفته نماند این روشی که ارائه میشود محدود به انتخاب Number نمیشه و هر گزینهای که در لیست قرار داره رو میشه انتخاب کرد و سپس به مراحل بعدی رفت).
در شکل همانطور که نشان دادهشده است، تعداد اعشار صفر، استفاده از کاما برای جداکننده هزارگان و همچنین استفاده از پرانتز برای نمایش اعداد منفی استفاده شده است.
اکسل فرمت تنظیمشده در مرحله قبل را بهصورت دستور زبانی که موردنظر خود میباشد، نشان دهد.
از شکل مشخص است که دستور زبان فرمت انتخاب شده قبل بهصورت
#,##0_);(#,##0)
می باشد. از این فرمت نترسید چند لحظه آینده همه آنها را خودتان اصلاح خواهید نمود.
این دستورالعمل نوشتاری به اکسل میگوید که اعداد را چگونه نمایش دهد.
این دستورالعمل شامل فرمتهای مختلفی بوده که از طریق نقطه کاما (;) از هم تفکیک میگردند.
در شکل مذکور دو فرمت قرارگرفته که یکی سمت چپ نقطه کاما و دیگری سمت راست نقطه کاما قرار دادهشده است.
فرمتی که سمت چپ نقطه کاما قرار دارد برای اعداد مثبت اعمالشده و فرمتی که در سمت راست نقطه کاما قرار دارد برای اعداد منفی اعمال میشود؛ بنابراین در این مثال اعداد منفی با نمایش پرانتز در اطراف آن و اعداد مثبت بهصورت عادی نمایش داده میشود.
توجه کنید که علامت _) در انتهای فرمت مثبت به اکسل میگوید که به اندازه یک پرانتز در انتهای اعداد مثبت فاصله بیاندازد.
با این کار اعداد مثبت همتراز با اعداد منفی (که پرانتز در اطراف آن قرار دارد)، در زیر هم و شکیلتر نمایش داده میشود.
بهراحتی میتوان فرمت ارائهشده را اصلاح نمود. برای مثال فرمت زیر را امتحان کنید:
+#,##0;-#,##0
در مثال بالا اعداد مثبت با علامت مثبت در سمت چپ آن (با جداکننده هزارگان) و اعداد منفی با علامت منفی در سمت چپ آن (با جداکننده هزارگان) شبیه اعداد زیر نمایش داده میشود.
+1,200
-15,000,000
بهراحتی میتوان این فرمت را برای نشان دادن اعداد بهصورت درصد اصلاح کرد.
فرمت زیر را در کادری که قبلاً اشاره شد، وارد کنید:
+0%;-0%
این دستورالعمل نوشتاری، اعداد را بهصورت زیر نمایش میدهد:
+43%
-54%
میتوان زیباتر عمل نمود و دستورالعمل نوشتاری را بهصورت زیر وارد کرد:
0%_);(0%)
با این تغییرات اعداد قبلی به این صورت نمایش داده میشود:
45%
(54%)
درصورتیکه در دستورالعمل از نقطه کاما استفاده نکنید نشاندهنده این است که فقط یک فرمت را انتخاب کردهاید و این فرمت هم برای اعداد مثبت و هم اعداد منفی اعمال میشود.
ممنون
برای نمایش واضحتر اعداد بزرگ و گیج نکردن مخاطبان از نمایش سه رقم سمت راست هزارگان و شش رقم میلیون و … میتوان صرفه نظر کرد.
برای انجام این کار کافی است مراحل قبلی که برای فرمت سفارشی اعداد استفاده شد، انجام دهید.
در کادر مربوطه این فرمت را وارد کنید (به کامای آخر توجه کنید):
#,##0,
درصورتیکه بخواهید شش رقم آن حذف شود و اعداد به صورت ضریبی از میلیون نمایش داده شود، کافی است دو کاما اضافه نمود:
#,##0,,
به شکل زیر توجه کنید. در این شکل هزینه، درآمد و سود دیسیپلین مکانیکال در یک پروژه به نمایش در آمده است:
شکل بالا بهاندازه کافی گویاست ولی برای درک تفاوت این دو فرمت به تصویر زیر که مقایسه فرمت قبل و بعد از اعمال ضریب میلیارد برای هزینه دیسیپلین مکانیکال در یک پروژه است، توجه کنید:
پرواضح است که خواندن اعداد در حالت دوم برای کاربر، در مواقعی که واقعاً لازم نیست تمام ارقام با دقت زیاد نمایش داده شود بسیار راحتتر است.
نکته حائز اهمیت این است که اکسل تمام ارقام حذفشده را در محاسباتش در نظر میگیرد و فقط برای نمایش خروجی، ارقام را بر اساس فرمت در نظر گرفتهشده اصلاح میکند.
خیلی از مواقع دیدم که دوستان برای این کار (گرد کردن اعداد به ضرایب هزارگان) اعداد را بر ۱,۰۰۰ و یا ۱,۰۰۰,۰۰۰ و … تقسیم میکند.
این در حالی است که در هرجایی که بخواهند از این اعداد برای محاسباتشان استفاده کنند باید دوباره آن اعداد را به فرمت اولیه بازگردانند.
بهعبارتدیگر باید در هرجایی یکپارچگی محاسباتشان در اکسل را حفظ نمایند که میتواند بسیار وقتگیر و در بعضی مواقع باعث بروز اشتباه در محاسباتشان گردد.
در صورت نیاز میتوان گرد کردن اعداد به ضرایب هزار، میلیون و … را با اضافه کردن یک حرف یا کلمه به انتهای فرمت مشخصتر نمود:
#,##0, “k”
با این کار اعداد بدینصورت نمایش داده میشود
188k
318k
از این تکنیک برای نمایش اعداد مثبت و منفی نیز میتوان استفاده نمود:
#,##0, "k";(#,##0, "k")
بعد از اعمال این فرمت اعداد مثبت و منفی بدینصورت نمایش داده میشود:
188k
(318k)
همین منطق را اگر بخواهید برای ضریب میلیون استفاده کنید، کافی است از دو کاما استفاده کرده و عبارت موردنظر خود را به انتهای دستورالعمل اضافه نمایید.
#,##0.00,, “m”
با ترفند قبلی و با اضافه کردن دو رقم اعشار، وقتیکه اعداد به ضریب میلیون گرد میشوند، می توان با نمایش دو رقم اعشار دقت اعداد نمایش دادهشده را بالا برد.
بهعنوانمثال به شکل زیر توجه کنید که هزینه، درآمد و سود دیسیپلین سیویل به صورت ضریب میلیارد (نه رقم) گرد شده و برای بالا بردن دقت از دو رقم اعشار استفاده شده است:
مخصوصا برای مدیران داشتن یک عدد کلی ملاک است و نیازی نیست که تا ریال آخر اعداد ارائه شود.
پرواضح است برای ارائه گزارش مالی این اعداد باید تا ریال آخر ارائه گردد.
علاوه بر فرمت اعداد مثبت و منفی اکسل این قدرت را دارد که فرمت اعداد صفر نیز تغییر دهد.
کافی است یک کاما نقطه (;) دیگر اضافهشده و فرمت موردنظر خود را برای اعمال به عدد صفر در نظر بگیرید.
بهصورت پیشفرض هر فرمتی که بعد از نقطه کامای دوم در نظر گرفته میشود به عدد صفر اعمال میشود.
بهعنوانمثال در دستورالعمل زیر هرگاه در سلولی عدد صفر قرارگرفته باشد عبارت «n/a» نمایش داده میشود:
#,##0_);(#,##0); “n/a”
درصورتیکه بخواهید کلاً عدد صفر نمایش داد نشود میتوانید از این دستورالعمل استفاده کنید:
#,##0_);(#,##0);
همانطور که عنوان شد این فرمت فقط بر نمایش سلولها تأثیرگذار است و ماهیت خود اعداد را تغییر نمیدهد.
برای درک مطالب شکل زیر توجه کنید:
همانطور که در مطالب عنوان شد انواع متنوعی از فرمتها را میتوان برای نمایش اعداد استفاده نمود.
می تونا با کمی خلاقیت و آگاهی از دستورالعمل تنظیم آنها فرمتهای خارقالعادهای را تنظیم نمود.
در بخش دوم با نکات بیشتری از تنظیم فرمتهای سفارشی اعداد آشنا خواهید شد.
اگر به فرمتی برخوردید که جالب باشه و بتونه به دوستان کمک کنه همین جا مطالبتون را ارائه بدید.
با سلام. سوالی که دارم این هست که چطور میتوان چند عدد رو که در یک سلول در چند ردیف وارد کرده ایم، کاما دار کنیم؟
باسلام
هرچند سوال شما با فضای مطلب ارائه شده کمی فاصله دارد ولی خوشحال می شوم اگر بتوانم کمکی کنم.
چندین راه حل دارید؛ می توانید اگر اعداد با فاصله از همدیگر تفکیک شده از Replace استفاده کنید. همچنین می توانید از ابزار Text to Columns در تب Data ابتدا اعداد را از همدیگر تفکیک کرده و سپس با کاما و از طریق فرمول به هم بچسبانید. ابزارهای کمکی دیگه ای هم وجود دارد که فکر می کنم از طریق روش های ارائه شده فوق مشکلتان برطرف می شود.
پیروز باشید
برای تبدیل به میلیون ریال اعداد منفی هم میشه از فرمول زیر در در FORMAT CELL استفاده کرد
(,,###,#);,,###,#
سلام
به نظرم یه کاما اضافه گذاشتید و معمولا برای اعداد منفی (قسمت دوم فرمول) از پرانتز استفاده میشه و این درحالی است که شما در قسمت اول (برای اعداد مثبت) از پرانتز استفاده کردید.
شاید فرمت زیر رو اگر استفاده کنید بد نباشه
#,###;(#,###)
درود و خسته نباشید
میخوام این فرمت رو پیاده کنم
اعداد اگر زیر میلیون بودن خودشن رو نشون بده
اگر میلیون بودن M جلوشون نشون بده
اگر میلیارد بودن B جلوشون نشون بده
اگر منفی بودن فرمتشون قرمز با پرانتز باشه
اگر مثبت بودن سبز باشه
سلام دوست عزیز
این نوع فرمت را باید با Conditional Formatting پیاده سازی کنید. اگر با این قابلیت اکسل کار کرده باشید و با آموزشی که در این درس آمده ترکیب کنید، به راحتی می توانید این خواسته خود را پیاده سازی کنید.
پیروز باشید
سلام خسته نباشید در صورتی که بخوایم اعداد مثبت رو از اعداد منفی به صورت رنگ مشخص جدا کنیم از کدام فورمل استفاده میکنیم؟ممنون
با سلام
حقیقتش سوالتون رو درست متوجه نشدم
اگر منظورتون نشان دان اعداد مثبت با یک رنگ ولی اعداد منفی با رنگ دیگر هست که در پست دقیق توضیح داده شده است.
با سلام
بیش از یک میلیونو پتنصدهزار داده دارم چطور میتونم توی اکسل وارد کنم؟
آخه اکسل ۲ به توان ۲۰ (یعنی ۱۰۴۸۰۰۰) عدد داده میگیره
با سلام
ببینید با هر ابزاری که می خواهید استفاده کنید باید ببینید که آیا این ابزار توان پاسخگویی نیاز شما رو دارد یا خیر!
برای این مورد مثال شما اگر فقط می خواهید داده ها رو ذخیره کنید و کار دیگری ندارید توی صفحات (Sheet) مختلف بشکنید اما باید خدمتتان عرض کنم که به عنوان یک تجربه شخصی اگر بیش از صد هزار سطر (Record) لازم دارید و کار شما محاسبات بر روی این داده هاست باید از توان پایگاه داده استفاده کنید.
می توانید حتی از پایگاه داده به عنوان Data source در اکسل استفاده کنید و محاسبات خود را بر روی محدوده داده ای که دارید در اکسل انجام دهید (به عنوان یک ابزار کارا در این زمینه به Power Pivot مراجعه کنید).
به عنوان ابزاری که همه چی در اختیار شماست (هم صفحه فرم ورود اطلاعات، هم گزارش گیری و هم قابلیت های SQL) و تا حدودی کار شما را (حدود یک میلیون رکورد) جواب دهد اکسس خوبه ولی اگر کارهای حرفه ای می خواهید انجام دهید باید سراغ طرحی لایه ای بروید و قاعدتا برای لایه های مختلف هم باید سراغ ابزار های کاربردی مثل #C و MS Sql Server بروید.
پیروز باشید.
عالی بود.
همچنین مطالبی در مورد نوشتن فرمت نویسی آماده کردیم که می تونید در Exceltip.ir بخونید.
سلام؛
چطور میشه توی قسمت format cell فرمول رند رو تعریف کرد طوری که بشه برای همه سلول ها از فرمول زند استفاده کرد.
باسلام
لطفا سوالتون رو واضح تر بفرستید.
سلام؛
توی یک سلول از بخش decimal میشه تعداد رقم های بعد اعشار رو نشون میده مشخص کرد ولی برای رند کردن اعداد باید از تابع round استفاده کرد.
حالا چطور میشه توی قسمت format cell ، تابع round رو تعریف کرد طوری که نیاز نباشه برای هر سلول به صورت جدا جدا تابع round رو نوشت.
باسلام
چرا از فرمت های پیش فرض اکسل استفاده نمی کنید (منظورم از اون فرمت هایی است که از طریق Ribbon قابل دسترسی است)؟ دلیل خاصی دارد؟
سلام؛
اونا فقط اعداد رو به صورت گرد شده نشون میده و در محاسبات عدد گرد نشده رو حساب میکنه.
بله. این فرمت فقط نمایش اعداد رو تغییر می دهد. بر روی محاسبات تاثیری ندارد.
درسته ، به همین دلیله که من هم میخوام اگه بشه تابع Round رو روی سلول ها تعریف کنم که اون سلول هر مقداری که گرفت round بشه.
آیا راهی داره؟؟؟؟
با سلام
یک افزونه است به نام ASAP Utilities که در قسمت Numbers یک ابزار دارد به نام Round Numbers که کارش همینه که می خواهید.
پیرو باشید.
سلام. آیا می شود کاری کرد تا نمودار، اعداد یک محور را ۱۰۰ برابر نشان دهد؟ در واقع شما آموزش دادید اعداد یک محور را کوچک کند من میخواهم اعداد محور بزرگ شوند. سپاس
باسلام
در لینک زیر تمام فرمت هایی که برای اکسل امکان پذیر هست رو با مثال توضیح داده. لطفا به این لینک مراجعه کنید.
https://exceljet.net/custom-number-formats
سلام ببخشید اگه بخوام به اخر یک عدد ۶ رقمی ۴ تا صفر اضافه کنم چکار باید کنم مثلا به ۱۲۳۴۵۶ ۴ صفر اضافه شود و تبدیل به ۱۲۳۴۵۶۰۰۰۰ شود
باسلام
یادمون باشد که در اکسل ماهیت اعدادی که وارد می کنیم تغییر نمی کند ولی شکل آن را می توان تغییر داد.
با این حال برای راحتی می توان بدین صورت وارد کنید:
۱۲۳۵۶e4
اکسل به صورت اتوماتیک ۴ تا صفر اضافه می کند.
مشخص است که برای تعداد صفرهای بیشتر یا کمتر کافی است عدد پشت e را تغییر داد.
پیروز باشید.
با سلام و عرض خسته نباشید و تبریک سال نو
سوالی داشتم از خدمتتون
وقتی عددی رو وارد اکسل میکنم (تو هر فرمتی که باشه) تقسیم بر ۱۰۰ میکنه و نمایش میده
مثلا عدد ۱۲۳۴۵۶۷۸۹ رو ۱۲۳۴۵۶۷/۸۹ نمایش میده یا ۱ رو ۰/۰۰۱ نمایش میده
اگه پاسخ بدین ممنون میشم
با سلام
به این لینک مراجعه کنید مشکلتون خواهد حل شود.
لینک
پیروز باشید
اگر بخواهم هر عدد ۳ بار تکرار شود و بازه اعداد از ۲۰۰ تا ۱۰۰۰ باشد باید چکار کنم ؟
میشه سوالتون رو با یک مثال مطرح کنید؟
عرض سلام خدمت شما سوالم این است که در یک ستون اعداد مثلا از ۱ تا ۲۰ چگونه کاری کنیم که اعداد ۱ ۱ ۱ ۲ ۲ ۲ .. و به همین ترتیب زیر هم قرار گیرند به عبارتی ۳ بار تکرار شوند و به عدد بعد بروند
باسلام
به صورت معمولی راهی رو نمیتونم پیشنهاد کنم ولی از ابزارهای یکسری از افزونه ها (مثل asap) میتوانید استفاده کنید.
برای نمونه اول اعداد یک تا بیست را پشت سرهم بگذارید و از آن ابزارها استفاده کنید تا دو سطر ببین تمام دادههای یک تا بیست اضافه کند و در نهایت از یکی دیگر از ابزارها استفاده کنید تا تعداد رو در جای خالی تکرار کند. به این ترتیب شما یه سری از اعداد خواهید داشت.
پیروز باشید
باسلام من یک الگوی عددی رو میخوام توی اکسل بنویسم که تنها برخی از اعداد اون تغییر کنه و برخی دیگه ثابت بمونه به عنوان مثال می خوام از عدد ۱۱۰۰۰۵ تا ۱۱۹۹۹۵ رو بنویسم و تنها اعداد ۰۰۰ الی ۹۹۹ اون متغیر باشه و اعداد ۱۱ و ۵ در ابتدا و انتهای لیست ثابت بمونه آیا راهی برای انجام این کار وجود داره یا خیر تشکر
با سلام و احترام
باید از ترکیب فرمول RandBetween و Value استفاده کنید. به عنوان نمونه می توانید فرمول را بدین صورت بنویسید:
=VALUE(11 & RANDBETWEEN(0;9)&RANDBETWEEN(0;9)&RANDBETWEEN(0;9)&5)
عین همین عبارت بالا رو در یکی از سلول های اکسل کپی کنید.
پیروز باشید
سلام
خسته نباشد .من یک سوال داشتم . من یک جدول درست کردم برای گیم سنترم که با وارد کردنساعت ورود و بعد ساعت خروخ رو که بزنی مبلغ رو به من میده ولی نمی تونم از تابع روند درست استفاده کنم .یعنی اگه مبلغ بدست امده شد ۳۶۵۰تومان رند بشه به ۳۵۰۰
باسلام
بسته به اینکه تا چند رقم بخواهید رند کنه این روش متفاوته ولی برای مثال بالا این فرمول رو وارد کنید:
=ROUND(A1/500;0)*500
توجه کنید فرض من بر این است که عدد در سلول A1 قرار دارد و می خواهید اعداد ضریبی از ۵۰۰ باشد.
پیروز باشید
سلام .
خسته نباشید .میخواستم بدونم در تابع های اکسل فرمولی داریم که روزانه در یک برنامه ای که نوشتم یک موجودی هم به من بده که من بدونم چقدر فروش داشتم . ممنون با یک مثال
باسلام دوست عزیز
حقیقتش سوالتون رو خیلی متوجه نشدم ولی تا اونجایی که متوجه شدم سعی می کنم پاسخ دهم.
توی اکسل تابعی است به نام Today که تاریخ روز جاری رو بر می گردونه.
شما می تونید لیست فروشتان را به صورت لیست به همراه تاریخ (البته تاریخ میلادی) در اکسل وارد کرده و برای هر فروش تاریخ را وارد نمایید.
در جایی دیگر از ترکیب تابع SumIF و Today استفاده کنید تا میزان فروش همان روز رو مشخص کنه. برای روزهای دیگر هم می توانید تاریخ روز های دیگر در تابع SumIF وارد نمایید.
البته می تونید تاریخ ها رو به صورت شمسی هم وارد کنید و در فرمول SumIF نیز تاریخ شمسی را وارد نمایید.
امیدوارم واضح بیان کرده باشم.
پیروز باشید.
با عرض سلام و خسته نباشید خدمت شما
چطوری میتونم در یک سلول عددی مثل ۱۱۹۸۰۰۹۴۵۵۰۰۰۰۰۱ را بنویسم چون هرکاری میکنم اون یک آخر را صفر میکنه؟
باسلام
تعداد ارقامی که برای اعداد می توان در اکسل وارد کرد حداکثر ۱۵ رقم است.
برای اطلاعات بیشتر می توانید به این مقاله مراجعه کنید.
اگر می خواهید برای محاسبه از این اعداد استفاده کنید، باید دقت را پایین تر آورده و از فرمت اعداد نمایی استفاده کنید.
اگر هم فقط برای نمایش است و محاسبه خاصی ندارید بهتر است آن به به صورت متن در آورده (با قرار دادن علامت ‘ قبل از عدد) و آنگاه مشکل شما حل خواهد شد (ولی محاسبه ای نمی توان با آن انجام داد).
پیروز باشید.
علامت ‘ در اکسل کجاست
سلام
من برای گیم سنترم یک برنامه درست کردم و این بار از تابع ()now استفاده کردم ولی به مشگل خوردم .
الان از تابع ()now که استفاده میکنم زمان شروع و زمان خروج رو میده به من و من دقیقه رو در یک سل دیگه لازم دارم برای محاسبه مبلغ
ممنون میشم راهنمایم کنید با یک مثال از فرمولش
با سلام و احترام
سوال شما من رو به یاد ۱۵ سال پیش انداخت زمانی که یکی از دوستام می خواست Time Sheet کارکنان هیوندای در فاز ۴ و ۵ عسلویه رو محاسبه کرده و بر اساس کارکرد اونها حقوقشون رو محاسبه کنه. به هر حال این سوال شما برام جالب بود.
توابع Day , Hour , Minute , Second می تونه به شما در این زمینه کمک کنه.
اگر مطمئن هستید که زمان کار گیمرها بیش از یک روز نیست می توانید Day رو حذف کنید.
اگر هم دقت شما در حد دقیقه است می توانید از Second هم صرف نظر کنید ولی با روشی که در ادامه توضیح می دهم هر دوی آنها نیز می توانید به محاسبه نهایی اضافه کنید.
کار بسیار ساده است کافی است اختلاف زمان خروج و زمان ورود را در تابع Hour قرار دهید تا مدت ساعت محاسبه شود و همین اختلاف دو زمان خروج و ورود را در تابع Minute قرار دهید تا میزان دقیقه آن نیز مشخص گردد. آن گاه این دو نتیجه به دست آمده را در هزینه هر ساعت و در هزینه دقیقه ضرب کنید و آن گاه با هم جمع کنید تا مبلغ کل به دست آید.
طبیعی است اگر گیمرها بابت یک ساعت ۶۰۰۰ تومان پرداخت می کنند بابت یک دقیقه ۱۰۰ تومان پرداخت کنند.
اگر با روش فوق مدت زمان ساعت ۲ ساعت و میزان دقیقه ۳۰ دقیقه محاسبه شد، مبلغ نهایی ۱۵۰۰۰ تومان خواهد شد.
با همین روش می توانید مدت روز و حتی ثانیه اختلاف دو زمان شروع و پایان را حساب کنید و قیمت نهایی را به دست آورد.
پیروز باشید.
سلام .
مهندس من گیم سنترم ساعتی ۴۰۰۰هزار است .و من متوجه نشدم مهندس اگه میشه فرمولش رو برام بنویسید که در سیل a1
ساعت ورود با تابع ()now ثبت میشه و ساعت خروج هم همینطور در سیل a2
من میخوام فرمول رو در سیل a3 بنویسم که دقیقه برام بدست بیاد و در سیل a4 مبلغ رو محاسبه کنه .
ممنون میشم سید جان و خیلی ممنون از این همه راهنمای ها شما دوست گرامی
سلام دوست عزیز
ببنید تابع now همانطوری که گفتید زمان حال حاضر رو ثبت می کنه، فقط مشکل اینه که هر وقت زمان خروج رو خواستید محاسبه کنید، زمان ورود هم تغییر می کند. چون هر دو از تابع now استفاده کرده اید. پس یادتان باشد که با این فرمول نمی توانید زمان ورود و همینطور زمان خروج رو ثبت کنید؛ مگر اینکه جای دیگر فقط مقدار زمان نشان داده شده توسط now رو (هم در زمان ورود و هم در زمان خروج) کپی کنید و از آنها برای محاستان استفاده کنید. (برای به دست آورد زمان جاری – هم ورود و هم خروج – می توانید از ترکیب کلید های
Ctrl + Alt + ;
هم استفاده کنید)پس فرض من بر این است که در سلول A1 زمان ورود رو با روش درست ثبت کرده اید و مقدار آن فرضا
۸:۰۷ AM
است.مقدار سلول A2 هم که زمان خروج است (با روش درست ثبت کرده اید) و مقدار آن
۵:۳۸ PM
است.در سلول A3 این فرمول رو بنویس:
=HOUR(A2-A1)
در سلول A4 این فرمول رو بنویس:
=MINUTE(A2-A1)
در سلول B1 مقدار هزینه هر ساعت رو بنویس: ۴۰۰۰ تومان
در سلول B2 مقدار هزینه هر دقیقه رو بنویس:
=B1/60
این فرمول ساده است و مقدار هزینه هر دقیقه را محاسبه می کنددر سلول B3 این فرمول رو بنویس:
=A3*B1
این سلول کل هزینه ساعت های حضور رو بر اساس واحد هر ساعت در مقدار آن که قبلا محاسبه کردی به دست می آورد.در سلول B4 هم این فرمول رو بنویس:
=A4*B2
این هم مثل قبل است با این تفاوت که کل هزینه های هر دقیقه رو محاسبه می کنهدر سلول B5 هم این فرمول رو بنویس:
=SUM(B3:B4)
این هم مجموع هزینه های کل ساعت ها و دقیقه هایی که محاسبه کردی رو به دست می آورد.سعی کردم ساده توضیح دهم که کار پیچیده نشه وگرنه همه این فرمول ها رو می تونی خیلی خلاصه تر هم بنویسی.
پیروز باشید
ممنون مهندس از راهنمای شما خیلی هم علی بود
خواهش می کنم
سلام.
مهندس من از تابع ()now برای ساعت ورود و خروج استفاده کردم.
ومن پاسخ شما رو مطالعه کردم و انجام دادم ولی جواب نگرفتم میشه
فرمول صحیحش رو برام بنویسید .
ممنون میشم
در توضیح قبلی توضیح دادم
امیدوارم مفید باشه
پیروز باشید
با عرض سلام. فرمولی هست که بشود دو عدد مثل ۱۲۳۴۵۶۷ و ۲۲۳۴۵۶۷ را داد و یک میلیون عدد بین این دو عدد را بصورت خودکار و پشت سرهم تایپ بشه. بغیر از روشی که عدد رو در یک سلول مینویسیم و روی نشانگیر کلیک میکنیم و تا اون جایی که مد نظرمان هست درگ میکنیم. همچین کاری برای یک میلیون عدد خیلی زمان بر است. فرمولی میخواستم که اینکار را خودش بصورت خودکار انجام دهد. با تشکر
باسلام و احترام
به فرمول خاصی نیاز نیست و روشهای متنوع زیادی وجود دارد ولی چون گفتید فرمول با این روش امتحان کنید:
در سلول A1 عدد اول رو بنویسید یعنی همون ۱۲۳۴۵۶۷
در سلول A2 این فرمول رو وارد کنید:
=A1+1
در قسمت آدرس سلولها برید و آدرس A1000000 رو بزنید تا مستقیم به این آدرس بروید.
در آدرس A1000000 عدد آخر رو بنویسید یعنی ۲۲۳۴۵۶۷
با ترکیب کلیدهای
Ctrl + کلید جهت دار بالا
به آدرس سلول اول یعنی A1 برگردید.سلول A2 رو انتخاب کنید و این سلول را برای کپی کردن انتخاب کنید.
با ترکیب کلیدهای
Shift + Ctrl + کلید دار پایین
محدوده A2 تا A1000000 را انتخاب کنید.با راست کلیک و انتخاب Paste Special گزینه Formula را انتخاب کنید تا فرمول A1+1 تا انتها کپی شود.
بعد از اینکه فرمول را کپی کردید برای اینکه سرعت صفحه شما پایین نیاید بهتر است فرمول ها رو از بین ببرید.
پیروز باشید
با سلام و احترام فراوان و تشکر از راهنمای یتان. استاد عزیز همه موارد عالی بود فقط ترکیب Alt + Ctrl + کلید دار پایین باعث میشود صفحه مانیتور مطالبش برعکس بشه و محدوده A2 تا A1000000 انتخاب نمیشود. راهی هست که بشود محدوده A2 تا مثلا A100000 را انتخاب کرد برای کپی کردن. با تشکر
با عرض پوزش اشتباهی تایپ کرده بودم (در متن قبلی هم اصلاح شد).
Shift + Ctrl + کلید جهت دار پایین
همانطور که گفتید در بعضی از کارتهای گرافیک، این کلید میانبر، برای تعویض جهت صفحه نمایش به کار می رود.
هرچند خیلی راحت می توانید این کلیدهای میانبر رو در تنظیمات کارت گرافیکتان هم عوض کنید.
پیروز باشید.
با سلام بنده یه ستون دارم که بعنوان کد دورهه میشناسم پنج رقم اول اون عدد ثابت است تو سوال دوستان فرموده بودید از یه
=استفاده کنیم لطف مکنید واضح تر توضیح بفرمائید تو تعریف تابع این فرمول رو وارد کنم ؟
VALUE(11 & RANDBETWEEN(0;9)&RANDBETWEEN(0;9)&RANDBETWEEN(0;9)&5)
باسلام و احترام
دوست عزیز دقت بفرمایید ایشان می خواست یک رشته از اعداد تولید کنند که دو رقم سمت چپ آن ثابت بود و سه رقم بعدی به صورت تصادفی انتخاب شود و رقم پنجم هم ثابت بود.
برای این هدف ابتدا باید رشته ای از اعداد تولید کرد که به صورت متن است (عدد نیست). و آن گاه آنها را تبدیل به عدد کرد (با استفاده از تابع Value).
پس هدف این است که پنج رقم (از ۰ تا ۹) در کنار هم قرار گیرد و تبدیل به یک عدد پنج رقمی شود.
ابتدا قسمت اول که ۱۱ بود (و تغییر نمی کند) را به صورت ثابت قرار داده ایم. در مرحله بعد یک کارکتر درست کردیم که بین ۰ تا ۹ بود و آن را به ۱۱ چسباندیم. در دو مرحله بعد نیز این فرمول تکرار شده است و اعداد تصادفی ۰ تا ۹ را تولید می کند و به هم می چسبد. در نهایت عدد ۵ که ثابت بود به کارکترهای قبلی می چسبد.
تابع Value کارکترهایی که در مرحله قبل تولید شده است را تبدیل به یک عدد می کند. اگر این کار را نکنید، اکسل تشخیص نمی دهد که خروجی یک متن است یا یک عدد. به همین خاطر از Value استفاده می کنیم که متن تولید شده تبدیل به عدد شود.
اگر توضیحات کافی بود لطفا به این سوال پاسخ دهید چرا به جای سه تابع RANDBETWEEN نمی توان از یک تابع RANDBETWEEN بدین صورت استفاده کرد:
RANDBETWEEN(0;999)
اگر دوستان پاسخ این سوال را بدهند، مشخص است که مفهوم ساختار بالا رو کاملا متوجه شده اند.
سلام به همه اساتید
اگر بخوام که یک عدد بیست رقمی(مثلا شماره شبا) در اکسل وارد کنم طوری که اعداد آخر صفر نشه و چهار رقم هم جدا کنه باید چکاری انجام بدم؟
با سلام و احترام
شبیه پرسش شما رو یکی از دوستان پرسیده بودند که در این آدرس می توانید به آن مراجعه کنید:
ولی در کل باید گفت امکان وارد کردن بیش از ۱۵ رقم در اکسل به صورت عدد وجود ندارد.
یک راه حل شما استفاده از فرمت متن است که وقتی آن را وارد نمایید در سلول دیگه و بر اساس تابع MID ارقام را چهارتا چهارتا شناسایی کرده و با یک الگو در کنار هم قرار دهد.
راه حل دیگه نوشتن کد در VBA است که بر میگردد به دانش شما در زمینه برنامه نویسی.
پیروز باشید.
متوجه منظورتون در استفاده از تابع MID برای الگو نویسی نشدم میشه بیشتر توضیح بدید ممنونم
کارکرد تابع Mid بدین صورت است که رشته، شماره کاراکتر و طول رشته را گرفته و مقدار را بر اساس این پارامترها بر می گرداند. کارکرد این تابع زیاد پیچیده نیست و می توانید به منابع اکسل رجوع کنید.
پیرو باشید.
سلام،من یک جدول از یکسری نمایندگی دارم.میخوام چنتا کار انجام بشه:
۱-اکسل بمن شماره هایی که عدد ۰ پیش شماره رو ندارند رو نشون بده.
۲-نمایش شماره های ثابتی که پیش شماره ندارند یا اعداد و و حروف اضافی در شماره وجود داره.
برای مثال ۰۵۱-۳۲۴۰۴۰۹۷ که اشتباه هست.اون خط بین پیش شماره و شماره تلفن،برای من اضافی هست.باید امثال این موارد رو بهم نشون بده
۳-چجوری میتونم برای تعداد زیادی شماره انتخابی بصورت اتومات ۰ پیش شماره بزارم؟
(جدول بالای ۲۰هزار ردیف داره و وقت هم بسیار کمه)