راهنما:کار با اکسل

در زیر چند ترفند برای کار کردن با اکسل را بیان می‌کنم

ترجمه رده‌های مشابه

قرار دادن پیوندها در براکت

ممکن است در رایانه شما در سمت راست باشد

برای ترجمه رده‌های مشابه می‌توان آنها را در یک فایل اکسل قرار داد و مراحل زیر را انجام داد.

1. از ستون اطلاعات رده‌ها یک کپی بگیرید. (مثلا در ستون B قرار دهید)
2. به کمک دستور جستجو و جایگزین‌کننده (Ctrl+H) عبارت‌های تکراری در ستون دوم را حذف کنید. (با خالی جایگزین کنید)
3. در ستون C در سلول ۱ عبارت [[ را درج کنید.
4. در ستون D در سلول ۱ عبارت ]] را درج کنید.
5. در ستون E در سلول ۱ در بخش فرمول این دستور را بنویسید =C1&B1&D1 که نتیجه‌اش می‌شود : [[محتویات سلول B]]

شروع به ترجمه

1. حال سلول‌های C1 , D1 , E1 را انتخاب کنید بر روی علامت مثلث یا بعلاوه سیاه رنگی که در پائین سلول E1 وجود دارد (همان‌طور که در شکل مقابل می‌بینید) دوبار کلیک (دبل کلیک) کنید. اکسل محتویات سلول‌های مابقی را تا آخرین ردیف همانند ردیف ۱ ترکیب می‌کند.
2. ستون E را با کلیک کردن روی عبارت E انتخاب کنید و در یک صفحه زیر کاربری خود در ویکی‌انگلیسی کپی و ذخیره کنید. سپس صفحه مذکور را در حالت ویرایش قرار داده و به کمک ابزار کمک مترجم محتویاتش را ترجمه کنید.
3. نتیجه ترجمه شده را یا CTR+A انتخاب و در ستون F پیست کنید. (الان شما در ستون F تعدادی متن ترجمه شده دارید و تعدادی هم چون در ویکی‌فا مقاله ندارند ترجمه نشده‌اند.
4. با دستور جستجو]] و [[ را از محتویات ستون F حذف کنید.
5. در ستون G نام تکرار شونده در رده‌ها را به فارسی بنویسد و مانند مرحله ۶ در همه ردیف‌ها کپی کنید.
6. در ستون H یک فضای خالی (تک اسپیس) بسازید و مانند مرحله ۶ در همه ردیف‌ها کپی کنید.
7. در ستون I این دستور را بنویسید. =G1&H1&F1 که نتیجه می‌شود نام رده‌های جدید که ترکیبی از حروف تکرار شونده+فاصله+ترجمه
8. حال می‌توانید محتویات ستون I را انتخاب کنید و کپی کنید و به صورت paste special پیست کنید تا فرمولش از بین برود. (می‌بایست در پنجره دستور گزینه Value را انتخاب کنید)
9. ستون‌های دیگر را به جز ستون 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 کنید.

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)