راهنما:کار با اکسل
 | این صفحه یک راهنمای چگونه انجام دادن است. که یک کار یا فرایند را در ویکی‌پدیای فارسی شرح می‌دهد. این صفحه یکی از سیاست‌ها یا رهنمودهای ویکی‌پدیا نیست، زیرا هنوز توسط اجتماع مورد بررسی و تأیید قرار نگرفته است. |
در زیر چند ترفند برای کار کردن با اکسل را بیان می‌کنم
ترجمه رده‌های مشابه ویرایش
قرار دادن پیوندها در براکت ویرایش
برای ترجمه رده‌های مشابه می‌توان آنها را در یک فایل اکسل قرار داد و مراحل زیر را انجام داد.
- از ستون اطلاعات رده‌ها یک کپی بگیرید. (مثلا در ستون B قرار دهید)
- به کمک دستور جستجو و جایگزین‌کننده (Ctrl+H) عبارت‌های تکراری در ستون دوم را حذف کنید. (با خالی جایگزین کنید)
- در ستون C در سلول ۱ عبارت [[ را درج کنید.
- در ستون D در سلول ۱ عبارت ]] را درج کنید.
- در ستون E در سلول ۱ در بخش فرمول این دستور را بنویسید =C1&B1&D1 که نتیجه‌اش می‌شود : [[محتویات سلول B]]
شروع به ترجمه ویرایش
- حال سلول‌های C1 , D1 , E1 را انتخاب کنید بر روی علامت مثلث یا بعلاوه سیاه رنگی که در پائین سلول E1 وجود دارد (همان‌طور که در شکل مقابل می‌بینید) دوبار کلیک (دبل کلیک) کنید. اکسل محتویات سلول‌های مابقی را تا آخرین ردیف همانند ردیف ۱ ترکیب می‌کند.
- ستون E را با کلیک کردن روی عبارت E انتخاب کنید و در یک صفحه زیر کاربری خود در ویکی‌انگلیسی کپی و ذخیره کنید. سپس صفحه مذکور را در حالت ویرایش قرار داده و به کمک ابزار کمک مترجم محتویاتش را ترجمه کنید.
- نتیجه ترجمه شده را یا CTR+A انتخاب و در ستون F پیست کنید. (الان شما در ستون F تعدادی متن ترجمه شده دارید و تعدادی هم چون در ویکی‌فا مقاله ندارند ترجمه نشده‌اند.
- با دستور جستجو]] و [[ را از محتویات ستون F حذف کنید.
- در ستون G نام تکرار شونده در رده‌ها را به فارسی بنویسد و مانند مرحله ۶ در همه ردیف‌ها کپی کنید.
- در ستون H یک فضای خالی (تک اسپیس) بسازید و مانند مرحله ۶ در همه ردیف‌ها کپی کنید.
- در ستون I این دستور را بنویسید. =G1&H1&F1 که نتیجه می‌شود نام رده‌های جدید که ترکیبی از حروف تکرار شونده+فاصله+ترجمه
- حال می‌توانید محتویات ستون I را انتخاب کنید و کپی کنید و به صورت paste special پیست کنید تا فرمولش از بین برود. (می‌بایست در پنجره دستور گزینه Value را انتخاب کنید)
- ستون‌های دیگر را به جز ستون I و A را انتخاب کنید و حذف نمائید.
نتیجه کار دو ستون دارید که در ستون A نام انگلیسی رده و در ستون B نام فارسی رده قرار دارند که البته تعدادی از موارد موجود در ستون B درست ترجمه نشده‌اند که می‌توان دستی ترجمه کرد یا به کمک دستور sort آنها را مرتب کرد تا مواردی که فارسی نیستند در انتها قرار گیرند.
مثال ویرایش
نمایش مراحل ۱-۱۳ در جدول زیر که مشابه فایل اکسل هست.
| A | B | C | D | E | F | G | H | I |
| 1930s_establishments_in_Australia | Australia | [[|]] | [[Australia]] | [[استرالیا]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در استرالیا | ||
| 1930s_establishments_in_China | China | [[|]] | [[China]] | [[جمهوری خلق چین]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در چین | ||
| 1930s_establishments_in_Egypt | Egypt | [[|]] | [[Egypt]] | [[مصر]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در مصر | ||
| 1930s_establishments_in_England | England | [[|]] | [[England]] | [[انگلستان]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در انگلستان | ||
| 1930s_establishments_in_Estonia | Estonia | [[|]] | [[Estonia]] | [[استونی]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در استونی | ||
| 1930s_establishments_in_France | France | [[|]] | [[France]] | [[فرانسه]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در فرانسه | ||
| 1930s_establishments_in_Germany | Germany | [[|]] | [[Germany]] | [[آلمان]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در آلمان | ||
| 1930s_establishments_in_Greece | Greece | [[|]] | [[Greece]] | [[یونان]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در یونان | ||
| 1930s_establishments_in_Israel | Israel | [[|]] | [[Israel]] | [[اسرائیل]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در اسرائیل | ||
| 1930s_establishments_in_Japan | Japan | [[|]] | [[Japan]] | [[ژاپن]] | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در ژاپن |
- نتیجهٔ نهایی
| A | B |
| 1930s_establishments_in_Australia | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در استرالیا |
| 1930s_establishments_in_China | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در چین |
| 1930s_establishments_in_Egypt | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در مصر |
| 1930s_establishments_in_England | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در انگلستان |
| 1930s_establishments_in_Estonia | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در استونی |
| 1930s_establishments_in_France | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در فرانسه |
| 1930s_establishments_in_Germany | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در آلمان |
| 1930s_establishments_in_Greece | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در یونان |
| 1930s_establishments_in_Israel | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در اسرائیل |
| 1930s_establishments_in_Japan | بنیان‌گذاری‌ها در دهه ۱۹۳۰ (میلادی) در ژاپن |
چند ترفند ویرایش
- مسالهٔ ۱
به عنوان مثال قصد داریم در یک ستون اکسل هر سلولی که دارای پرانتز هست محتویات بعد از پرانتز حذف شوند و فقط محتوای قبل از پرانتز باقی بمانند. به عنوان مثال سلولی که عبارت نیویورک (شهر) دارد به صورت نیویورک بماند.
- پاسخ:برای حل این مشکل ستون مورد نظر را انتخاب می‌کنیم و به کمک دستور Data/Text to columns و گزینه Delimited را انتخاب کنید و بعد از زدن دکمه next در مقابل Other: علامت (را قرا دهید و دکمه next را بزنید با این کار هر سلولی که (داشته باشد محتویات بعد از پرانتز به ستون بعد انتقال می‌یابد. حال می‌توانید دیگر ستون‌ها را انتخاب کنید و دکمه Delete را بزنید.
- مسالهٔ ۲
قصد داریم سلول‌هایی که مثلاً عبارت Iran را دارند متن بعد از عبارت ایران در یک ستون دیگر قرار گیرد و آنهایی که ندارند کاری به آنها نداریم.
- پاسخ: این ترفند مانند مسألهٔ ۱ هست با این تفاوت که باید در ابتدا در ستون یک عبارت ایران را با مثلاً @ یا هر عبارتی که می‌دانیم در متن نیست جایگزین کنیم و سپس ستون را به کمک ترفند ۱ (در بالا) به کمک @ به دو بخش تقسیم کنیم.
- مسالهٔ ۳
در تعدادی از سلول‌ها عبارت _in_ در میان متن وجود دارد می‌خواهیم فقط آن سلول‌هایی را داشته باشیم که این عبارت در آنها وجود دارند.
- پاسخ: برای حل این مشکل باید در ستون دوم در سلول B1 این دستور را بنویسم.
=IF(ISNUMBER(SEARCH("_in_",A1)),"OK", "Not OK")
یا در صورتی‌که دستور بالا کار نکرد، در سلول B1 این دستور را می‌نویسیم:
=IF(ISNUMBER(SEARCH("_in_";A1));"A"; "B")
=(LEN(B1)-LEN(SUBSTITUTE(B1؛"|"؛"")))/LEN("|")
و با دابل کلیک بر روی مثلث پائین سلول محتویات این سلول تا پائین کپی می‌شود.
حال باید ستون ۱و۲ را با هم انتخاب کنید و دستور sort را برای ستون ۲ اجرا کنید. با این روش همه سلول‌هایی که عبارت OK دارند در بالا قرار می‌گیرند و سلول‌هایی که NOT OK هستند در پائین و به راحتی می‌توانید آنهایی را که در بالا هستند را کپی کنید و در انتها محتویات ستون ۲ را حذف نمائید.
- مسالهٔ ۴
در یک ستون اطلاعات سلول‌هایی هستند که متنشان مشابه هست و ما فقط می‌خواهیم از هر متن یک بار در ستون تکرار شود.
- راه حل ۱: در اکسل در بخش data دکمه‌ای هست به نام remove duplicates که ردیف‌های تکراری را حذف می‌کند.
- راه حل ۲:
- پاسخ:برای حل این مسئله باید در ابتدا ستون اول را sort کنید سپس به بخش Developer/Visual basci برویم در پنجره جدید بر روی sheet1 کلیک چپ insert/class module را بزنیم. حال یک پنجره سفید باز می‌شود؛ که در آن این کد را می‌نویسیم.
- سوال: اکسل من Developer ندارد، چه کنم؟ :
- پاسخ:روی آیکون آفیس در گوشه بالا سمت چپ کلیک کنید و Excel Option را انتخاب کنید. در صفحه باز شده در قسمت popular گزینه Show Developer tab in ribbon را تیک بزنید و Ok کنید.
- سوال: اکسل من Developer ندارد، چه کنم؟ :
Sub ourfunction()
For a = 1 To 100
If Cells(a, 1).Value = Cells(a + 1, 1).Value Then
Cells(a, 1).Value = ""
End If
Next
End Sub
توضیح: به جای عدد ۱۰۰ در کد بالا، تعداد ردیف‌های فایل اکسل تان را وارد کنید
حال آن را اجرا (Run) کنید مواردی که تکراری هستند را حذف می‌کند سپس از نو ستون مورد نظر را sort کنید حال فقط مواردی که در این ستون هستند مطمئناً تکراری نیستند.
- مسالهٔ ۵
-
- سوال: شمارش تعداد تکرار یک عنوان در یک ستون:
- پاسخ:
- سوال: شمارش تعداد تکرار یک عنوان در یک ستون:
کد روبرو به شما میزان تکرار عنوان نوشته شده در سلول B1 را در ستون A نمایش می‌دهد.
=COUNTIF($A:$A,B1)
برای زمانی که یک سلول در یک ستون موجود باشد
=IF(ISERROR(MATCH(F4,L:L, 0)), 'not match', 'match')
- مسالهٔ ۶
-
- سوال: چند خط متغیر داریم و قصد درون‌ریزی آنها در الگو به صورت تراگنجایش داریم برای آنکه متوجه شویم آیا در این خطوط مورد هست که تعداد متغیر از تعداد مورد نیاز ما بیشتر است یا آیا متغییری جابجا شده‌است چه باید کرد؟
- پاسخ:
- سوال: چند خط متغیر داریم و قصد درون‌ریزی آنها در الگو به صورت تراگنجایش داریم برای آنکه متوجه شویم آیا در این خطوط مورد هست که تعداد متغیر از تعداد مورد نیاز ما بیشتر است یا آیا متغییری جابجا شده‌است چه باید کرد؟
برای اینکه متوجه شوید یکی از ردیف‌ها متغییرش به ستون اضافه نرفته کافی‌است مثل مثال زیر عمل کنید.
مثلا شما ۶ ستون متغیر دارید در اکسل ستون ۷ام که قاعدتا نباید متغیر داشته باشد را انتخاب کنید و بر روی دکمهٔ text to column کلیک کنید اگر خطا داد یعنی متنی در آن ستون نیست اگر خطا نداد و مشغول انتقال متن به ستون شد یعنی در آن ستون متن وجود دارد و قاعدتا متغییرها جابجا شده‌اند.
یا می‌توانید از sort استفاده کنید مثلا همهٔ ستون‌هایی که متغیر دارند + یک ستون بیشتر (مثلا از ستون ۱ تا ستون ۷) را انتخاب کنید و بر روی دکمهٔ sort بزنید و بر اساس ستون ۷ همه را sort کنید اگر متنی به بالای صفحه آمد آن ردیف متغیر اضافی دارد.
فهرست کردن lookup ویرایش
Public Function FindSeries(TRange As Range, MatchWith As String)
For Each cell In TRange
If cell.Value = MatchWith Then
x = x & cell.Offset(0, 1).Value & ", "
End If
Next cell
FindSeries = Left(x, (Len(x) - 2))
End Function
دستور
=findseries(A1:A7,A1)