صفحات گسترده قرار است در وقت خود صرفه جویی کنند ، اما گاهی اوقات فقط اوضاع را مسخره تر می کنند. من از حفر منوهای بی پایان متنفرم تا اینکه تعداد انگشت شماری از فرمول ها را پیدا کردم که وزنه برداری سنگین را برای من انجام می دهد.
صفحات گسترده قرار است در وقت خود صرفه جویی کنند ، اما گاهی اوقات فقط اوضاع را مسخره تر می کنند. من از حفر منوهای بی پایان متنفرم تا اینکه تعداد انگشت شماری از فرمول ها را پیدا کردم که وزنه برداری سنگین را برای من انجام می دهد.
8
Vlookup
Vlookup در ستون اول یک محدوده یک مقدار خاص را جستجو می کند و یک مقدار مربوطه را از ستون دیگر در همان ردیف برمی گرداند. می توانید آن را یک ابزار جستجو بنامید که شما را از پیمایش دستی از طریق مجموعه داده های وسیع نجات می دهد. نحو Vlookup:
Vlookup (search_key ، دامنه ، فهرست ، [is_sorted])
در اینجا ، Search_Key مقداری است که می خواهید پیدا کنید ، و محدوده جدول جایی است که در آن جستجو می کنید. پارامتر شاخص به Vlookup می گوید کدام ستون برای بازگشت داده ها از آن ، در حالی که IS_SORTED نشان می دهد که آیا داده های شما مرتب شده است یا خیر.
اگر IS_SORTED را به False تنظیم کرده اید ، مسابقات دقیق را به شما نشان می دهد ، این معمولاً همان چیزی است که شما برای بیشتر کارهای جستجو می خواهید.
در اینجا نحوه عملکرد Vlookup در عمل آمده است:
= Vlookup (“Saniya” ، B2: J3000 ، 9 ، false)
این فرمول “Saniya” را در ستون B جستجو می کند و مقدار مربوطه را از ستون نهم برمی گرداند. اگر می خواهید جزئیات کارکنان ، اطلاعات محصول یا هر داده ای را که در جداول سازماندهی شده است ، پیدا کنید.
با این حال ، محدودیتی از Vlookup وجود دارد که فقط در سمت راست جستجو می کند. نمی تواند به سمت چپ ستون جستجوی شما به نظر برسد. اما برای بیشتر کارهای روزمره ، جستجوی داده ها را کاملاً انجام می دهد. برای مثال های بیشتر ، می توانید راهنمای ما در مورد استفاده از عملکرد VLOOKUP در برگه های Google را بررسی کنید.
7
جمع
Sumif مقادیر را در یک محدوده بر اساس معیارهای خاص که تعیین کرده اید اضافه می کند. این فرمول به جای محاسبه دستی کل برای دسته های مختلف ، بالابر سنگین را انجام می دهد و دقیق است.
در حالی که عملکرد اصلی اگر بر اساس شرایط درست یا نادرست بازگردد ، Sumif با انجام محاسبات در مورد داده هایی که معیارهای شما را برآورده می کند ، آن را بیشتر می کند. این نحو زیر را دارد:
sumif (دامنه ، معیارها ، [sum_range])
در اینجا ، دامنه حاوی سلولهایی است که شما در برابر معیارهای خود بررسی می کنید ، معیارها وضعیت شما است و SUM_RANGE مقادیر واقعی برای افزودن است. اگر sum_range را حذف کنید ، Sumif مقادیر موجود در محدوده خود را اضافه می کند. این فقط با متن ، اعداد و حتی کارتهای وحشی مانند Arterisks (*) برای مسابقات جزئی کار می کند.
=SUMIF(V2:V3000, "<1000", Z2:Z3000)
در مثال بالا ، فرمول ستون V را برای مقادیر کمتر از 1000 بررسی می کند و سپس مبالغ مربوطه را در ستون Z خلاصه می کند. این فرمول برای محاسبه کل فروش ، دسته هزینه ها یا هر جمع بندی مشروط مفید است.
شما حتی می توانید معیارهای مختلفی را با هم ترکیب کنید ، اگرچه برای آن سطح پیچیدگی به SUMIF نیاز دارید. برای چنین نمونه های پیچیده و ترکیب معیارهای پیشرفته ، می توانید راهنمای مفصل ما را در مورد استفاده از عملکرد SUMIF در برگه های Google بررسی کنید.
6
هماهنگ کردن
این عملکرد متن را از چندین سلول به یک سلول واحد ترکیب می کند. هنگامی که شما نیاز به ادغام نام های اول و خانوادگی دارید ، آدرس های ایمیل ایجاد کنید یا برچسب های سفارشی بسازید ، می توانید از این عملکرد در فرمول خود برای رسیدگی به دستکاری متن استفاده کنید.
همچنین می توانید از نماد Ampersand (&) به عنوان میانبر برای جمع بندی اساسی استفاده کنید ، اگرچه استفاده از عملکرد Concatenate در Google Sheets انعطاف پذیری بیشتری را برای ترکیب های پیچیده ارائه می دهد. نحو Concatenate:
concatenate (string1 ، [string2 ، …])
در اینجا ، String1 اولین مقدار متن شماست و می توانید رشته های اضافی نامحدود را اضافه کنید. هر پارامتر می تواند یک مرجع سلولی ، متن واقعی در نقل قول ها یا ترکیبی از هر دو باشد.
به یاد داشته باشید که به طور خودکار فضاها را اضافه نمی کند ، بنابراین باید همانطور که در مثال زیر نشان داده شده است ، آنها را به صورت دستی به عنوان پارامترهای جداگانه درج کنید.
= concatenate (b2 ، “” ، c2)
این فرمول مقادیر سلول های B2 و C2 را با یک فضا از هم جدا می کند. این برای ایجاد نام های کامل ، کدهای محصول ، آدرس های فرمت شده و موارد دیگر مناسب است.
اگر از پارامترهای زیادی استفاده کنید ، Concatenate می تواند ناخوشایند شود ، اما هنوز هم قابل اطمینان ترین روش برای ادغام داده های متن است. همچنین می توانید برای پیوستن به متن از چندین سلول از Concatenate در اکسل استفاده کنید.
5
قسم
Countif سلولهایی را که معیارهای خاص را در یک محدوده رعایت می کنند ، شمارش می کند. هنگامی که شما نیاز به ردیابی چند مورد در دسته های خاص دارید – مانند شمارش وظایف کامل یا پاسخ دادن به پاسخ های خاص – این فرمول خطاهای شمارش دستی را از بین می برد.
این کار برای تجزیه و تحلیل داده های نظرسنجی ، ردیابی موجودی و معیارهای عملکرد خوب کار می کند. نحو برای Countif:
Countif (دامنه ، معیارها)
در اینجا ، دامنه سلولهایی را که می خواهید بررسی کنید مشخص می کند و معیارها این وضعیت را تعریف می کنند که سلول ها باید برای شمارش برآورده شوند. این معیارها می تواند شامل متن ، شماره یا اپراتورهای منطقی باشد. موارد زیر به عنوان مثال است:
= Countif (K2: K3000 ، “فعال”)
این فرمول تعداد سلولهای موجود در محدوده K2 را شمارش می کند: K3000 که دقیقاً متن “فعال” است. شما می توانید از عملکرد Countif در Google Sheets برای ردیابی وضعیت پروژه ، شمارش انواع خاص محصول یا تجزیه و تحلیل داده های طبقه بندی شده استفاده کنید.
Countif همچنین از کارتهای وحشی مانند ستاره ها (*) برای مسابقات متن جزئی و علائم سؤال (؟) برای تعویض های تک شخصیت پشتیبانی می کند.
همچنین می توانید از اپراتورهای مقایسه ای مانند “> 50” یا “> = 100” برای شمارش دامنه های عددی استفاده کنید. این فرمول هنگام برخورد با مجموعه داده های بزرگ که در آن شمارش دستی به سادگی عملی نیست ، مفید است.
4
شکل
در مرحله بعدی در لیست ArrayFormula قرار دارد که یک فرمول واحد را به طور خودکار در طیف وسیعی از سلول ها اعمال می کند. به جای کپی کردن فرمول ها در صدها ردیف ، یک بار آن را می نویسید و اجازه می دهید برگه های Google بقیه را کنترل کنند.
من این کار را هنگام کار با مجموعه داده های بزرگ که به طور مداوم گسترش می یابند ، مفید پیدا کردم. ArrayFormula به جای به یاد آوردن فرمول ها به پایین کشیدن هر بار که داده های جدید ظاهر می شود ، به طور خودکار همه چیز را به روز می کند. نحو اساسی فرمول معمولی شما را می بندد:
= arrayformula (your_formula_here)
با این حال ، شما باید به جای سلولهای مجرد ، ستون ها یا دامنه ها را به کل ارجاع دهید. به عنوان مثال ، به جای A2 ، از A2 استفاده می کنید: A برای کل ستون از ردیف 2 به سمت پایین.
در اینجا یک مثال عملی است که نام های اول و نام خانوادگی را ترکیب می کند:
= ArrayFormula (B2: B & “” & C2: C)
این فرمول هر جفت مربوطه را در ستون های B و C به طور همزمان همزمان می کند. نتایج فوراً در تمام ردیف ها با داده ها ظاهر می شوند. علاوه بر این ، هنگام افزودن ردیف های جدید به طور خودکار تنظیم می شود.
برای مثال های بیشتر ، می توانید راهنمای ما را در مورد عملکرد ArrayFormula در صفحات Google بررسی کنید. قطعاً ارزش منحنی یادگیری را دارد ، به خصوص اگر به طور مرتب با داده ها کار کنید.
3
فیلتر کردن
بر اساس شرایطی که تعریف می کنید ، ردیف های خاص را از یک مجموعه داده استخراج می کند. هنگامی که شما نیاز به دیدن فقط برخی از کارمندان ، پروژه ها یا سوابق فروش بدون مخفی کردن ردیف ها دارید ، این فرمول فوراً یک زیر مجموعه پویا را ایجاد می کند.
آنچه من بیشتر از فیلتر قدردانی می کنم این است که نتایج به طور خودکار هنگام تغییر داده های منبع شما بر خلاف فیلتر دستی به روز می شود. این برای داشبورد و گزارش های زنده مفید است. نحو از این الگوی پیروی می کند:
فیلتر (دامنه ، شرط 1 ، [شرط 2] ، …)
دامنه شامل داده های شما است ، در حالی که Contion1 اولین معیار شما را مشخص می کند. علاوه بر این ، شما می توانید چندین شرایط را برای فیلتر دقیق تر ، همانطور که در مثال زیر نشان داده شده است ، جمع کنید.
= فیلتر (D2: D3000 ، R2: R3000 = “مهندسان”)
این فرمول فقط کارمندان بخش مهندسی را از پایگاه داده کارمندان نشان می دهد. در نتیجه ، شما دقیقاً زیر مجموعه مورد نیاز خود را بدون پیمایش از طریق سوابق بی ربط دریافت می کنید.
فیلتر برای ردیابی پروژه ، تجزیه و تحلیل هزینه و مدیریت مشتری فوق العاده خوب کار می کند ، زیرا متن و معیارهای عددی را به همان اندازه به خوبی انجام می دهد.
اگر می خواهید نمونه های دقیق تر و تکنیک های فیلتر را ببینید ، راهنمای ما در مورد عملکرد فیلتر را در برگه های Google بررسی کنید. صادقانه بگویم ، این یکی از آن فرمول هایی است که واقعاً ساعت کار دستی را صرفه جویی می کند وقتی دائماً زیر مجموعه های داده های مختلف را برای گزارش ها و جلسات می کشید.
2
پرسش
Query با استفاده از زبان SQL مانند ، جستجوی سبک پایگاه داده را به برگه های Google می رساند. در حالی که ممکن است در ابتدا ارعاب کننده به نظر برسد ، این عملکرد Google Sheets می تواند هر هفته ساعت شما را نجات دهد. این کار ساده است که اصول اولیه و قدرتمند را برای تجزیه و تحلیل داده های اداری درک کنید.
این ترکیب فیلتر ، مرتب سازی و گروه بندی در یک فرمول واحد است. به جای استفاده از چندین توابع ، می توانید دقیقاً آنچه را که لازم دارید با یک دستور واحد استخراج کنید. نحو اساسی به این شکل است:
پرس و جو (داده ، پرس و جو ، [هدرها])
پارامتر داده شامل دامنه شما است ، پرس و جو دستور SQL مانند شما را در نقل قول ها نگه می دارد ، و هدرها مشخص می کنند که چند ردیف هدر را شامل می شود. در مثال زیر ، پرس و جو داده های عملکرد کارمندان را تجزیه و تحلیل می کند:
= پرس و جو (A1: AB3000 ، “SELECT D ، Q ، AB ORDER BY AB DESC”)
این فرمول نام کارمندان (ستون D) ، بخش ها (ستون Q) و نمرات عملکرد (ستون AB) را بازیابی می کند ، سپس بر اساس نمره عملکرد به ترتیب نزولی مرتب می شود. در اصل ، شما در حال ایجاد گزارش های سفارشی هستید که در غیر این صورت شما را ملزم به استفاده از جداول محوری در صفحات اکسل یا گوگل می کند.
پرس و جو می تواند در تجزیه و تحلیل فروش ، ردیابی پروژه و گزارش بودجه مفید باشد زیرا در جستجوی متن ، محدوده تاریخ و عملیات ریاضی است. برای مثال های بیشتر ، می توانید راهنمای دقیق ما در مورد عملکرد پرس و جو در برگه های Google را بررسی کنید.
1
متحمل شدن
این عملکرد مستقیماً داده ها را از سایر پرونده های Google Sheets در صفحه گسترده فعلی شما وارد می کند. هنگامی که شما در حال مدیریت چندین پرونده پروژه ، ردیاب بودجه یا گزارش های دپارتمان هستید ، واردات می تواند روال چسباندن کپی را برای شما از بین ببرد.
این ارتباطات زنده بین پرونده ها را حفظ می کند. در نتیجه ، هنگامی که همکار شما صفحه گسترده فروش استاد را به روز می کند ، داشبورد شما به طور خودکار آن تغییرات را منعکس می کند و به روز رسانی دستی لازم نیست. نحو به دو بخش اصلی اطلاعات نیاز دارد.
Importrange (“spreadsheet_url” ، “دامنه”)
صفحه گسترده آدرس وب کامل پرونده منبع است ، در حالی که دامنه دقیقاً مشخص می کند که سلولها با استفاده از نماد استاندارد ، مانند “Sheet1! A1: C10” وارد می شوند. موارد زیر به عنوان مثال است:
= Importrange (“https://docs.google.com/spreadsheets/d/abc123” ، “B2: AD50”)
این فرمول داده های کارمندان را از پرونده بخش دیگری مستقیماً به گزارش تلفیقی شما وارد می کند. اما برای اولین بار باید مجوز اعطا کنید ، زیرا Google Sheets شما را وادار به اتصال پرونده ها می کند.
Importrange برای ایجاد داشبوردهای اجرایی ، تثبیت گزارش های تیم و حفظ نماهای متمرکز داده ها مفید است ، زیرا این ساده ترین راه برای وارد کردن داده ها از پرونده Google Sheets است. با این حال ، به یاد داشته باشید که بسیاری از فرمول های Importrange می توانند صفحه گسترده شما را کند کنند.
این هشت فرمول باعث می شود صفحه گسترده به طور قابل توجهی کار کند. یک منحنی یادگیری وجود دارد ، اما پس از استفاده منظم از آنها ، پس انداز زمان آشکار می شود. وظایف داده ای که قبلاً به طول می انجامیدند اکنون در عرض چند دقیقه اتفاق می افتد و این ارزش تلاش را دارد.