ترفند های excel

[Lean 56,968]

ترفند33

 

 

رگرسیون در اکسل

 

تابع ماتریسی تابعی است که بیش از یک سلول را برای ارائه نتایج در بر می گیرد ، تابع Linest یک تابع ماتریسی است که آرگومان های ان به شرح زیر هستند:

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

 

دو آرگومان اول همونطور که مشخصه مقادیر X,Y هستند آرگومان سوم یک مقدار منطقی هست که TRUE نشان دهنده مدلی با یک عرض از مبدا مشخص (پیش فرض) و FALSE نشان دهنده مدلی که عرض، از مبدا صفر داره هست

آرگومان چهارم هم یک مقدار منطقی هست که TRUE نشان دهنده ارائه خلاصه ای از اطلاعات اماری مورد نظرهست و FALSE (پیش فرض) نشان دهنده عدم ارائه خلاصه ای از اطلاعات اماری مورد نظره

نحوه استفاده از این فایل مطابق فرمول های آرایه ای هست ، به این صورت که ابتدا یک محدوده رو انتخاب و CTRL+SHIFT+ENTER رو باید بزنید تا محاسبات انجام بشه ، پیشنهاد میکنم حالت های مختلف رو چک کنید و نتایج رو ببینید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56,968]

ترفند34

 

یکی از ابزار های کاربردی اکسل که به خصوص در ساخت دیتا بیس های متوسط بسیار کاربرد دارد که Table نام دارد ، و از تب Insert قابل دسترس است ، در بخش اموزش بیشتر در این مورد بحث خواهد شد اما در این ترفند استفاده از شورت کی CTRL+T و نحوه Drag کردن یک فرمول در صفحه مد نظر هست در تصویر پیوست بیشتر متوجه ترفند می شوید.

.

[Lean 56,968]

ترفند 35

 

یکی از مواردی که مورد نیاز بسیاری از افراد است ردیف گذاری است یعنی دادن شماره ردیف برای رکورد های ثبت شده به نحوی که با هر بار فی.لتر کردن داده ها شماره ها از 1 آغاز شود

 

برای این کار از فرمول زیر استفاده می شود:

.

به قسمت هایلایت شده دقت کنید در این حالت سلول اول هم ردیف و هم ستون باید مطلق باشد.

 

 

.

 

فرمول فوق را در ستون F وارد کنید و تا اخرین داده فرمول را درگ نمایید.

[Lean 56,968]

ترفند36

 

فرض کنید نموداری مانند تصویر زیر ترسیم کرده اید که برخی اعدا صفر هستند ، اگر بخواهید دیتا لیبل بر رو نمودار داشته باشید اعداد صفر هم نمایش داده می شود.

 

.

 

حال اگر بخواهید دیتالیبلهای صفر مشاهده نشود ، ابتدا بر روی آنها کلیک کرده تا انتخاب شوند سپس CTRL+1 را بزنید تا پنجره format data labels باز شود از تب number همانند تصویر زیر از لیست categories فرمت custom را انتخاب و در بخش format code عبارت ;;;0 را وارد کرده و گزینه add را بزنید تا به بخش type اضافه شود خواهید دید که اعداد صفر حذف می شوند.

 

 

.

[Lean 56,968]

ترفند 37

 

در این ترفند نحوه معکوس کردن یک جدول یا ماتریس را آموزش می دهیم

 

1- ابتدا جدول را انتخاب و با CTRL+C کپی کنید

 

2-ناحیه مورد نظر رانتخاب و بازدن Alt+CTRL+V+L لینک جدول مبدا را پیست کنید

 

3- CTRL+H را بزنید و = را با =x جایگزین کنید

 

4-جدول دوم را انتخاب و با CTRL+C کپی کنید

 

5- ناحیه سوم را انتخاب و با CTRL+V+L+E به صورت ترانسپوز پیست کنید.

 

6- مجدد CTRL+H را زده و =x را به = تبدیل کنید

 

.

 

در فیلم زیر مراحل را ببینید

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56,968]

ترفند 38

در این ترفند از طریق VBA امکان ایجاد متن چشمک زن فراهم شده است، در کد مربوطه با تغییر خصوصیت font به interior color می توان سلول چشمک زن ایجاد نمود

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56,968]

ترفند 39

در فایل زیر ترفندی برای انتخاب نمودار از طریق یک کمبوباکس ارائه شده است با این روش میتوان برای تبدیل یک نمودار به نمودار دیگر مثلا ستونی به خطی استفاده کرد در واقع نمودار های اصلی در شیت دیگری طراحی شده و در شیت اصلی میتوان چارت مورد نظر را انتخاب نمود.

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[Lean 56,968]

ترفند 40

 

در نرم افزار Word برای ایجاد فاصله مجازی برای نوشتن مثلا کلمه «برنامه‎ریزی» از کلید Crrl+- استفاده می‎کنیم ولی می‎دونید که این کلید در اکسل و پاور پوینت کار نمی‎کنه. برای ایجاد این فاصله در این دو نرم‎افزار از کلیدهای Ctrl+Shift+3 استفاده کنید. این کلید رو در Word هم می‎تونید به کار ببرید

 

با تشکر از خانم امیرسیافی

[Lean 56,968]

ترفند 41

 

ایجاد لیست دلخواه در اکسل

دقت کرده اید زمانی که در یک سلول می نویسید sun و آن را درگ می کنید کل ایام هفته به ترتیب قرار می گیرند.مانند تصویر زیر:

 

 

اما چرا وقتی می نویسیم “شنبه” و درگ می کنیم این اتفاق نمی افتد؟

برای اینکه بتوانیم لیست دلخواه خود را وارد اکسل کنیم.باید ایتدا لیست مورد نظر خودمان را در یک ردیف بنویسیم.مثلا من در اینجا ماه های شمسی را می نویسم.مانند تصویر زیر(شما می توانید ایام هفته یا اعضا یا سهامداران شرکت خود را بنویسید)

 

سپس به سیر زیر رفته:

File>Options>Advanced>Edit Custom Lists

همان طور که در تصویر زیر می بینید ابتدا باید لیست مورد نظر را وارد کنیم که ما در اینجا سلول های A1 تا A12 را انتخاب می کنیم.سپس بر روی Import کلیک می کنیم و در نهایت بر روی Ok کلیک می کنیم.

[h=4][/h]

[ehsan124 12]

با سلام. فيلم آموزشي مربوط به ترفند 18 در سايت مورد نظر يافت نميشه :5c6ipag2mnshmsf5ju3 لطفا راهنمايي كنيد ممنون

[Lean 56,968]

ترفند 42

 

فرض کنید فرمی دارید که باید برای تعداد زیادی از افراد چاپ کنید حالا باید چکار کرد مشخصات هر فرد را در فرم تک تک نوشت یا راه بهتری هم هست؟

 

در این ترفند به ارائه راهکاری در این زمینه می پردازیم

 

فرض کنید فرمی داریم که در آن میخواهیم مشخصات تعداد زیادی از افراد رو چاپ کنیم مثلا

 

نام...............

 

نام خانوادگی ...................

 

ابتدا نیاز دارید که این مشخصات در یک فایل اکسل ذخیره گردد ، سپس در یک فایل ورد این فرم را طراحی می کنید در فایل ورد از تب MAILINGS قسمت Start Mail Merge از منوی کشویی ابزار Step by Step Mail Merge Wizard رو انتخاب کنید پنجره ای در سمت چپ با نام Mail Merge باز می شود سپس از پایین پنجره بخش Step1of6روی Next: Starting document کلیک کنید سپس روی Next:select recipients مجدد کلیک کنید در مرحله سه در بالای پنجره زیر Use an existing list روی Browseکلیک کنید سپس پنجره ای باز می شود به مسیری که فایل اکسل را ذخیره کرده اید رفته و انتخاب و open را بزنید پنجره ای به نام Select Table باز می شود که حاوی اسامی شیت های اکسل است ، شیت حاوی اطلاعات را انتخاب و OK را بزنید پنجره ای به نام Mail Merge Recipients باز می شود آنرا OK کنید حال در مرحله step 3 of 6 هستید روی next:write your letter کلیک کنید در مرحله بعد بالای پنجره روی More items کلیک کنید پنجره ای به نام insert merge fields باز می شود که حاوی تیتر اطلاعات شماست حال آنرا بسته در فرم طراحی شده هر قسمتی که باید اطلاعات وارد شود انتخاب کرده سپس پنجره مذکور را مجدد باز کنید و تیتر مورد نظر را انتخاب و insert بزنید و مجدد بسته و برای هر قسمت از فرم که باید پر شود این عمل را تکرار کنید حال به مرحله 5 بروید و سپس 6 بروید در زیر قسمت merge روی print کلیک کنید اگر روی پنجره باز شده OK کنید فرم مذکور به تعدا افرادی که مدنظر دارید پرینت می شود.

[Lean 56,968]

ترفند 43

 

سولی توسط یکی از دوستان مطرح شده بود که اینجا در غالب یک ترفند ارائه میشه

 

سوال

میخواهیم اعداد روی محورای نمودارها همانی باشند که به عنوان ورودی نمودار باشند. به عبارت دیگه هیچ عددی بجز اعداد خود داده ها روی محورها نباشد.

 

جواب

برای انجام این کار با یک نمودار پراکندگی استاندارد شروع می کنیم و به صورت سیستمی تمام عناصر غیر ضروری را حذف میکنیم چیزی که باقی می ماند یک نمودار با محور است که مقادیر داده را نشان میدهد داده ها برای این نمودار در محدوده a2:b13 قرار دارند محورها در این نمودار در واقع سرهای پراکندگی اضافه شده به آن می باشند و به شکل محور تغییر فرمنت یافته اند داده هایی که محور عمودی را ایجام می کنند در محدوده a16:b27 قرار دارند و داده هایی که محور افقی را پدید می آورند در محدوده c16:d27 قرار دارند هر دو اینها خطوط xy هستند که با خط و مربعات علامت نمایش داده می شوند.

 

با تشکر از دوست عزیزم جناب آقای مسعودیان

 

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

برای مشاهده این محتوا لطفاً ثبت نام کنید یا وارد شوید.

• ورود
• یا
• ثبت نام

[mohs3n 13]

سلام،

چجوری میشه چندتا فایل مختلف اکسل رو به هم لینک کرد؟

منظورم اینه که مثلاً تو یه شرکت هر شخصی برای خودش یک تایم شیت تو اکسل پر میکنه و هر روز میزنه که چقدر روی یک پروژه ی بخصوص کار کرده... حالا اگر ما بخوایم بدونیم کلاً روی یک پروژه توسط افراد مختلف روی هم چند ساعت کار شده، چجوری باید این فایل های اکسل با هم لینک کنیم؟

ممنون

[Lean 56,968]

سلام،

چجوری میشه چندتا فایل مختلف اکسل رو به هم لینک کرد؟

منظورم اینه که مثلاً تو یه شرکت هر شخصی برای خودش یک تایم شیت تو اکسل پر میکنه و هر روز میزنه که چقدر روی یک پروژه ی بخصوص کار کرده... حالا اگر ما بخوایم بدونیم کلاً روی یک پروژه توسط افراد مختلف روی هم چند ساعت کار شده، چجوری باید این فایل های اکسل با هم لینک کنیم؟

ممنون

 

باید یک فایل اکسل در شبکه شیر کنید گرچه این روش روش مناسبی نیست اصولا اکسل برای کار شبکه ای طراحی نشده ولی امکان شیر کردن دارید ، راه دیگه هم اینه داده ها در یک دیتابیس اکسس وارد بشه و اکسس به اکسل لینک بشه که امن تر هست

[ali karimi rounaghi 31]

تكرار يك كاراكتر به تعداد مشخص در يك سلول:

Rept(text;number_times=

مثال:

(REPT("k";10=

حاصل تكرار 10 بار k مي باشد

[ali karimi rounaghi 31]

استخراج kامين عدد بزرگ در يك محدوده

فرمول: (LARGE(array, k

array محدوده انتخابي است و k شماره عدد بزرگي است كه ما مي خواهيم.

مثال: ستون D شامل عددهاي 100-200-300-400 مي باشد. دومين عدد بزرگ در ستون D را بدست آوريد:

LARGE(D:D, 2) =300

[ali karimi rounaghi 31]

استخراج kامين عدد كوچك در يك محدوده

فرمول: (Small(array, k

array محدوده انتخابي است و k شماره عدد بزرگي است كه ما مي خواهيم.

مثال: ستون D شامل عددهاي 100-200-300-400 مي باشد. دومين عدد كوچك در ستون D را بدست آوريد:

Small(D:D, 2) =200

[ali karimi rounaghi 31]

فرمول:([ TRUNC(number, [num_digits

number عدد اعشاري مورد نظر است

num digits تعداد اعشار است كه به صورت پيش فرض 0 در نظر گرفته شده است

مثال1: قسمت صحيح عدد 4.33 را بدست آوريد

( TRUNC(4.33 كه حاصل برابر 4 است

مثال1: حاصل عدد 4.33 را تا يك رقم اعشار بدست آوريد

( 1,TRUNC(4.33 كح حاصل برابر 4.3 مي باشد

[Lean 56,968]

ترفند44

 

[Lean 56,968]

ترفند45