آموزش اکسل هفته ۲۳

آموزش اکسل هفته ۲۳ – ایران ترجمه – Irantarjomeh

مقالات ترجمه شده آماده گروه کامپیوتر

مقالات ترجمه شده آماده کل گروه های دانشگاهی

مقالات

چگونگی سفارش مقاله

الف – پرداخت وجه بحساب وب سایت ایران ترجمه(شماره حساب)ب- اطلاع جزئیات به ایمیل irantarjomeh@gmail.comشامل: مبلغ پرداختی – شماره فیش / ارجاع و تاریخ پرداخت – مقاله مورد نظر --مقالات آماده سفارش داده شده پس از تایید به ایمیل شما ارسال خواهند شد.

آموزش فراگیری اکسل از طریق آقای اکسل –

هفته ۲۳

این هفته : ۴ مطلب جالب – فراگیری چگونگی استفاده از مایکروسافت جستجو برای داشتن مجموعه یکتا از رکورد ها.

سپس استفاده از جستجو گر های وب جهت دریافت داده ها از وب.

استفاده از کوئری (پرس و جو) برای داشتن یک مجموعه یکتا از رکورد ها

مشکل : مشاهده نمودید که در اکسل راه های گوناگونی برای داشتن لیست منحصر به فردی از مشتریان از یک مجموعه داده وجود دارد. شما اکنون در این فکر هستید که آیا بیل جلن نمی توانست فقط یک راه دیگر برای داشتن لیست یکتایی از مشتریان ارائه کند؟

استراتژی : من باید آن را ایجاد کنم، چرا که هم سریع است و هم نسبتا جالب است. همچنین راهی است برای داشتن لیستی از مشتریان از یک فایل بسته. رمز کار استفاده از مایکروسافت کوئری است. در حال حاضر این محصول مایکروسافت به طور پیش فرض در نصب اکسل وجود ندارد. بنابراین، چنانچه به طور کامل آن را نصب نکرده اید، لازم است CD های نصب را برای افزودن آن به اکسل پیدا کنید.

اساسا، مایکروسافت کوئری راهی برای اجرای SQL در یک فایل خارجی است. من مطمئنم آنچه را مایکروسافت مجسم کرده یک جدول Access یا دستیابی و یا منبع داده ای از ODBC بوده است، اما بعدا شخصی متوجه شد که فایل خارجی می تواند کتاب کار دیگری از اکسل باشد. در این جا چگونگی راه اندازی آن ارائه می شود.

۱) فرض کنید کتاب کاری به نام SalesData.xls در اختیار دارید. این کتاب کار، کاربرگی به نام Data دارد. کاربرگ شامل دسته ای از رکورد ها در ستون A تا H می باشد، شکل ۱۰۰۳ را ملاحظه نمایید. داده های A:H را به حالت انتخاب درآورید. به این محدوده از جدول نام MyData را اختصاص دهید.

۲) SalesData را ذخیره کرده و آن را ببندید.

۳) در یک کاربرگ جدید مطابق شکل ۱۰۰۴، Data – Get External Data – New Database Query را انتخاب نمایید.

۴) در کادر محاوره ای Choose Data Sources، فایل های اکسل را ماند شکل ۱۰۰۵ انتخاب نموده وسپس OK را انتخاب نمایید.

۵) در کادر محاوره ای انتخاب کتاب کارSalesData.xls را مطابق شکل ۱۰۰۶ انتخاب نموده و OK را انتخاب نمایید.

۶) همانگونه که شکل ۱۰۰۷ نشان می دهد، فیلد مشتری واقع در لیت چپ را انتخاب نموده و کلید جهت سمت راست را فشار دهید تا مشتری را بر روی Query قرار دهد.

۷) دکمه Next را انتخاب کنید. مانند شکل ۱۰۰۸٫

۸) مطابق شکل ۱۰۰۹ جهت نادیده گرفتن بخش فیلتر دکمه Next را کلیک کنید.

۹) گزینه Sort Ascending by Customer را مطابق شکل ۱۰۱۰ انتخاب نمایید. بر روی Next کلیک کنید.

۱۰) از آنجاییکه تنها به رکورد های یکتا نیاز دارید، باید پرسش نامه را در مایکروسافت کوئری ویرایش نمایید. مطابق شکل ۱۰۱۱، این گزینه را انتخاب نموده و در خاتمه Finish را انتخاب نمایید.

Fig. 1011

۱۱) پنجره مایکروسافت کوئری باز می شود. از منوی این پنجره View – Query Properties را مطابق شکل ۱۰۱۲ انتخاب نمایید.

۱۲) در شکل ۱۰۱۳ Unique Values Only را انتخاب و OK را کلیک نمایید.

۱۳) در شکل ۱۰۱۴ File – Return Data to Microsoft Excel را انتخاب نمایید.

۱۴) محل قرارگیری داده های وارد شده را مانند شکل ۱۰۱۵ انتخاب نموده و OK را انتخاب نمایید.

آموزش اکسل هفته ۲۳

نتیجه : از فایل بسته اکسل لیست یکتایی از مشتریان را در اختیار دارید. شکل ۱۰۱۶ را ملاحظه نمایید.

جزئیات بیشتر : از طریق Data – Import External Data – Data Range Properties می توانید این جستجو را به گونه ای تنظیم نمایید که هر روز همزمان با باز شدن صفحه کاری به روز شود. شکل ۱۰۱۷ را ملاحظه نمایید.

خلاصه : ابزار Data Query راهی برای وارد نمودن داده ها از Access، Excel یا منابع داده های ODBC فراهم می کند.

دستورات بحث شده : Data – Import External Data – New Database Query

 وارد نمودن یک جدول از یک صفحه وب به اکسل

مشکل : هر روز با باز کرد یک مرور گر قیمت اوراق بهادار سهام را بررسی می کنید و داده های مربوط به آن را به صورت دستی در اکسل کپی می کنید. یا احتمالا، صفحه وب سایت گزینه ای برای دانلود به یک صفحه گسترده را ماند شکل ۱۰۱۸ در اختیار شما قرار می دهد. راه ساده تری برای داشتن این داده ها در اکسل وجود دارد.

استراتژی : جستجوگرهای وب وارد کردن جدول ها از صفحات وب را ساده می کند. مراحل زیر را پیکگیری کنید:

۱) ابتدا مرور گری را باز نموده و سپس صفحه وبی  که می خواهید وارد نمایید را باز نمایید. در مرورگر بالایی، قبلا ۴ نماد سهام را برای وارد نمودن درج کرده بودم. نوار آدرس مرور گر شامل URL مربوطه برای این شکل داده می باشد.

۲) صفحه کار جدیدی باز کنید. از منو Data _ Import External Data – New Web Query را مطابق شکل ۱۰۱۹ انتخاب نمایید.

کادر محاوره ای New Web Query با صفحه اصلی مرور گر انتخابی شما ظاهر می شود. شکل ۱۰۲۰ را ملاحظه نمایید.

۳) با استفاده از کلید های Alt+Tab  به مرورگر همیشگی خود بروید. مانند شکل ۱۰۲۱، آدرس صحیح موجود در نوار آدرس را به حالت انتخاب در آورده وجهت کپی آن به کلیپ برد (بخشی از حافظه کامپیوتر) کلید های Ctrl+C را به کار ببرید.

۴) مجددا با استفاده از Alt+Tab به اکسل باز گردید. برای انتقال آدرس به نوارفرمول Ctrl+V را مطابق شکل ۱۰۲۲ به کار بگیرید. دکمه Go را برای جستجو کلیک کنید.

صفحه وب مورد نظر در کادر محاوره ای به نمایش در می آید. یک کادر زرد با یک فلش سیاه در کنار هر جدول بر روی صفحه وب وجود خواهد داشت. حتی در صورتیکه صفحات وب را طراحی کرده باشید، مشاهده تعداد جدول هایی که طراح برای ایجاد صفحه به کاربرده است جالب توجه است.

۵) از نوار مرور گر برای یافتن جدول مورد نظر کمک بگیرید. علامت پیکان زرد در کنار آن جدول را انتخاب نمایید. به هنگامی که علامت پیکان را انتخاب می کنید، جدول به صورت موقتی طراحی می شود. علامت پیکان زرد به علامت P سبز رنگ تغییر می یابد، شکل ۱۰۲۳ را ملاحظه نمایید.

۶) دکمه Import را انتخاب نمایید. اکسل مطابق شکل ۱۰۲۴، محل وارد نمودن داده ها را از شما سؤال می کند.

کادر محاوره ای ناپدید می شود. برای مدت چند ثانیه، مقدار عجیبی در سلول A1 مشاهده می کنید که به دنبال ان عبارت ” Getting Data ” ظاهر می شود. در مدت ۱۰-۱ ثانیه ، صفحه گسترده به همراه نسخه فعلی  داده های صفحه وب مجددا شکل می گیرد. شکل ۱۰۲۵ را ملاحظه نمایید.

۷) برای refresh یا نوسازی داده ها در هر زمان، به این کاربرگ مراجعه نمایید و ماند شکل ۱۰۲۶ از Data – Refresh Data استفاده نمایید.

اطلاعات بیشتر : شما می توانید اعداد و فرمول های مجاور را به جستجو گر وب اضافه نمایید. به طور مثال، ممکن است ستونی از اعداد سهام خود را اضافه نموده و سپس مقداری را محاسبه نمایید. شکل ۱۰۲۷ را ملاحظه نمایید.

آموزش اکسل هفته ۲۳

خلاصه : جستجو گرهای وب راه ساده ای برای ورود دائمی داده ها از یک صفحه وب به اکسل ارائه می کنند.

دستورات بحث شده : Data – Import External Data – New Web Query; Data – Refresh Data

به روز رسانی اتوماتیک داده ها همزمان با باز نمودن کاربرگ

مشکل : هر روز صفحه گسترده ای با یک جستجو گر وب باز نموده و از Data – Refresh Data استفاده می نمایید. آیا راهی برای نوسازی خود کار جستجو گر وب وجود دارد؟

استراتژی :

۱) همانگونه که در بخش قبلی توضیح داده شد، یک جستجو گر وب ایجاد کنید. پس از فشردن دکمه Import و پیش از بستن کادر محاوره ای Import Data ، دکمه Properties در سمت چپ و پایین تر مطابق شکل ۱۰۲۸ را انتخاب نمایید. گزینه Update When the Spreadsheet Opens را انتخاب نمایید.

۲) کادر خصوصیات محدوده دادهای خارجی ظاهر می شود. در بخش Refresh Control گزینه Refresh Data on File Open را مانند شکل ۱۰۲۹ انتخاب نمایید.

اطلاعات بیشتر: چنانچه یک جستجو گر وب راه اندازی نموده و نیاز به دسترسی به کادر Properties یا خصوصیات برای تغییر تنظیمات بعد از تعیین جستجو گر دارید، مراحل زیر را دنبال نمایید.

۱) سلولی در صفحه وب باز گردانده شده انتخاب نمایید.

۲) همانگونه که در شکل ۱۰۳۰ مشاهده شده است، از منو، Data – Import External Data – Data Range Properties را انتخاب نمایید.

خلاصه : یک جستجو گر وب را می توان به گونه ای تنظیم کرد که همیشه با باز نمودن صفحه گسترده، به طور خودکار به روز رسانی شود. با این کار می توانید مدیریت داشبوردی با اطلاعات فعلی صفحات متنوع وب راه اندازی کنید.

دستورات بحث شده : Data – Import External Data – Data Range properties

به روز رسانی اتوماتیک داده ها هر دو دقیقه یکبار

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

آموزش اکسل هفته ۲۳

استراتژی :

۱) سلولی را در یک جدول جستجو وب انتخاب نمایید. از منو Data – Import External Data – Data Range Properties .

۲) از کادر محاوره ای External Range Properties در بخش control Section، گزینه Refresh Every  را انتخاب نمایید. دکمه چرخشی  شکل ۱۰۳۱ را به ۲ دقیقه افزایش دهید.

Gotcha : چنانچه در حالت Edit باشید عمل نوسازی انجام نمی شود. اگر شروع به وارد نمودن یک سلول به صفحه کاری نمایید، و تلفن زنگ بزند، و تصمیم به رفتن به جلسه قبل از کامل نمودن سلول نمایید، اکسل نمی تواند جستجو را به روز رسانی کند.

Gotcha : ممکن است بخواهید نمونه دوم اکسل را باز کنید و جستجوی AutoRefresh را بالای پنجره خودش بازیابی کنید. چنانچه سعی در کار با همان نمونه اکسل را دارید، توقف هایی که هر دو دقیقه برای نوسازی اکسل انجام می شود، دیوانه کننده خواهد بود.

خلاصه : جهت به روز رسانی دوره ای جستجو گر وب به طور اتوماتیک، از کادر خصوصیات استفاده نموده و دکمه AutoRefresh را انتخاب کنید.

دستورات بحث شده : Data – Get External Data – Data Range Properties

فضا های موجود در این صفحه وب از بین نمی روند

مشکل : همانگونه که شکل ۱۰۳۲ نشان می دهد، این داده ها از یک صفحه وب وارد نموده اید. پس از تلاش های بسیار جهت از بین بردن فضا های داخلی باز هم موفق به انجام این کار نشدید.

تلاش کردید با انتخاب ستون A و استفاده از Edit – Replace  هر رخداد فضای خالی را با  تهی (حذف فضا های خالی) جایگزین نمایید. فضاهای خالی باقی ماندند.

سعی کردید از تابع SUBSTITUTE مطابق شکل ۱۰۳۳ استفاده نمایید. باز هم فضاهای خالی از بین نرفتند.

چنانچه به سلول A1 بروید و AJ 335 986 را تایپ نمایید، فرمول ستون B عملی خواهد شد، شکل ۱۰۳۴ را مشاهده نمایید.

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

استراتژی : می توانید اقدام به تحقیقات نمایید. برای هر کاراکتر الفبا عددی در نظر گرفته شده است. حرف “A” بزرگ در واقع کد ۶۵ است. طبیعتا یک کاراکتر فضای خالی کد ۳۲ دارد.

جهت یافتن کد کاراکتر برای یک کاراکتر، می توانید از تابع CODE استفاده نمایید. برای جداسازی کاراکتر سوم در سلول A1 ، از =MID(A2,3,1) استفاده نمایید. برای یافتن کد کاراکتر با ان مقدار، از =CODE(MID(A2,3,1)) استفاده نمایید.

مطابق شکل ۱۰۳۵، این فرمول تأیید می کند که مقدار تایپ شده در سلول A1 شامل یک فضای خالی (با کد کاراکتر ۳۲) در مکان سوم است.

حال آن فرمول را به تمام سلول های دیگرستون A کپی نمایید. مطابق شکل ۱۰۳۶، تمام سلول های دیگر دارای کد کاراکتر۱۰۶ در مکان سوم هستند.

این نشان می دهد که چرا تلاش های شما برای حذف فضای خالی اثر نمی کند. سلول های A فضای خالی را شامل نمی شوند.

تحقیقات اندکی نشان می دهد که کاراکتر ۱۶۰ یک فضای شکستگی ناپذیر است. این فضا جایی است که نمی خواید مرور گر خط جدیدی بین آن واژه ها آغاز کند. کاراکتر Nbsp در صفحات وب بسیار رایج است.

بنابراین چگونه می توانید از کاراکتر ۱۶۰ در Edit – Replace استفاده نماید؟ در اینجا یک روش مطرح می شود:

آموزش اکسل هفته ۲۳

روش ۱:

۱) به یک سلول خالی در کاربرگ رفته و مانند شکل ۱۰۳۷ از فرمول =CHAR(160) استفاده کنید. چیزی در سلول نخواهید دید، اما نوار فرمول نشان می دهد که فرمولی در انجا پنهان است.

۲) این سلول را کپی کنید.

۳) محدوده داده های وب را به حالت انتخاب در آورید. از منو، Edit – Replace را مانند شکل ۱۰۳۸ انتخاب نمایید.

۴) در کادر Find What با استفاده از کلید های Ctrl+V فضای شکستگی ناپذیر را وارد کنید. مجددا، چیزی در اینجا نخواهید دید. کادر Replace with را خالی بگذارید.Replace All را انتخاب نمایید.

نتیجه : همانگونه که شکل ۱۰۳۹ نشان می دهد، فضاهای ناخواسته از بین می روند.

روش ۲:

راه حل دیگر استفاده از تابع SUBSTITUTE مطابق شکل ۱۰۴۰ می باشد.

 خلاصه : با وجود آنکه کاراکتر۱۶۰ علت اصلی این مشکل است، استفاده از تابع CODE به شما اجازه خواهد داد تا کد کاراکتر را برای هر کاراکتری که این چنین مشکل آفرین است پیدا نمایید.

توابع بحث شده : =CODE(); =CHAR(); =SUBSTITUTE()