Мэдээлэл боловсруулах, тайлагнах ажилд оролцдог хүмүүст зориулсан Excel-ийн амьдрал хакерууд

2024 Зохиолч: Malcolm Clapton | [email protected]. Хамгийн сүүлд өөрчлөгдсөн: 2024-01-13 02:26

Энэ нийтлэлд Манн, Иванов, Фербер хэвлэлийн газрын ерөнхий захирлын туслах Ренат Шагабутдинов Excel-ийн зарим гайхалтай хакеруудыг хуваалцаж байна. Эдгээр зөвлөмжүүд нь янз бүрийн тайлан гаргах, өгөгдөл боловсруулах, танилцуулга хийх ажилд оролцож буй хэн бүхэнд хэрэгтэй болно.

Энэ нийтлэлд Excel программ дээрх ажлыг хялбарчлах энгийн аргуудыг багтаасан болно. Эдгээр нь менежментийн тайлан гаргах, 1С болон бусад тайлангаас татан авалт дээр үндэслэн төрөл бүрийн аналитик тайлан бэлтгэх, тэдгээрийн танилцуулга, диаграммыг удирдахад зориулагдсан хүмүүст онцгой ач холбогдолтой юм. Би өөрийгөө үнэмлэхүй шинэлэг зүйл гэж дүр эсгэдэггүй - нэг хэлбэрээр эсвэл өөр хэлбэрээр эдгээр техникийг форум дээр хэлэлцсэн эсвэл нийтлэлд дурдсан байдаг.

Хүссэн утгууд нь хүснэгтийн эхний баганад байхгүй бол VLOOKUP болон HLOOKUP-ийн энгийн хувилбарууд: LOOKUP, INDEX + SEARCH

VLOOKUP болон HLOOKUP функцууд нь хүссэн утгууд нь таны өгөгдөл авахаар төлөвлөж буй хүснэгтийн эхний багана эсвэл мөрөнд байгаа тохиолдолд л ажиллана.

Үгүй бол хоёр сонголт байна:

1. LOOKUP функцийг ашиглана уу.

   Энэ нь дараах синтакстай: LOOKUP (хайлтын_утга; хайлтын_вектор; үр дүнгийн_вектор). Гэхдээ зөв ажиллахын тулд view_vector мужын утгуудыг өсөх дарааллаар эрэмбэлэх шаардлагатай.

   

2. MATCH болон INDEX функцуудын хослолыг ашиглана уу.

   MATCH функц нь массив дахь элементийн дарааллын дугаарыг буцаана (түүний тусламжтайгаар хайсан элемент хүснэгтийн аль мөрөнд байгааг олох боломжтой), INDEX функц нь өгөгдсөн тоо бүхий массив элементийг буцаана (бид үүнийг олж мэдэх болно. MATCH функцийг ашиглан).

   

   Функцийн синтакс:

   • SEARCH (хайлтын_утга; хайлтын_массив; тохирох_төрөл) - манай тохиолдолд тохирох төрөл "яг тохирох" хэрэгтэй бөгөөд энэ нь 0 тоотой тохирч байна.

   • INDEX (массив; мөрийн_тоо; [баганын_тоо]). Энэ тохиолдолд массив нь нэг мөрөөс бүрдэх тул баганын дугаарыг зааж өгөх шаардлагагүй.

Жагсаалтын хоосон нүднүүдийг хэрхэн хурдан бөглөх вэ

Даалгавар бол баганын нүднүүдийг дээд талд байгаа утгуудаар бөглөх явдал юм (ингэснээр тухайн сэдэв нь зөвхөн тухайн сэдвийн номын блокийн эхний мөрөнд биш, хүснэгтийн мөр бүрт байх болно):

"Сэдв" баганыг сонгоод "Нүүр хуудас" бүлгийн туузан дээр дарж "Олгох ба сонгох" товч → "Бүлэг нүдийг сонгох" → "Хоосон нүд" дээр товшоод томьёог оруулж эхэлнэ үү (өөрөөр хэлбэл тэнцүү тоог оруулна уу. гарын үсэг зурж) гар дээрх дээш сумыг дарахад л дээд талд байгаа нүдийг харна уу. Үүний дараа Ctrl + Enter дарна уу. Үүний дараа томьёо шаардлагагүй болсон тул хүлээн авсан өгөгдлийг утга болгон хадгалах боломжтой.

Томъёоны алдааг хэрхэн олох вэ

Томъёоны салангид хэсгийг тооцоолох

Нарийн төвөгтэй томьёог ойлгохын тулд (бусад функцийг функцийн аргумент болгон ашигладаг, өөрөөр хэлбэл зарим функцийг бусад хэсэгт байрлуулсан байдаг) эсвэл алдааны эх үүсвэрийг олохын тулд та түүний зарим хэсгийг тооцоолох хэрэгтэй болдог. Хоёр хялбар арга бий:

1. Томъёоны нэг хэсгийг зөв томьёоны мөрөнд тооцоолохын тулд тухайн хэсгийг сонгоод F9 товчийг дарна уу:

   

   Энэ жишээнд SEARCH функцэд асуудал гарсан - аргументуудыг сольсон. Хэрэв та функцийн хэсгийн тооцооллыг цуцлахгүй бөгөөд Enter товч дарвал тооцоолсон хэсэг нь тоо хэвээр үлдэнэ гэдгийг санах нь чухал юм.

2. Тууз дээрх "Формула" бүлгийн "Формула тооцоолох" товчийг дарна уу:

   

   Гарч ирэх цонхонд та томъёог алхам алхмаар тооцоолж, ямар үе шатанд, аль функцэд алдаа гарч байгааг тодорхойлох боломжтой (хэрэв байгаа бол):

   

Томъёо нь юунаас хамаардаг, юунд хамаарахыг хэрхэн тодорхойлох вэ

Томъёо нь аль нүднээс хамааралтай болохыг тодорхойлохын тулд туузан дээрх Томъёо бүлэгт "Нөлөөлөх эсүүд" товчийг дарна уу.

Тооцооллын үр дүн юунаас шалтгаалж байгааг харуулах сумнууд гарч ирнэ.

Хэрэв зурган дээр улаанаар тодруулсан тэмдэг гарч ирвэл томъёо нь бусад хуудас эсвэл бусад номын нүднүүдээс хамаарна.

Үүн дээр дарснаар бид нөлөөлж буй эсүүд эсвэл мужууд яг хаана байрлаж байгааг харж болно.

"Нөлөөлөх эсүүд" товчлуурын хажууд "Хамаарах эсүүд" товчлуур байдаг бөгөөд энэ нь ижил аргаар ажилладаг: энэ нь идэвхтэй нүднээс хамааралтай нүднүүдэд томьёо бүхий сумыг харуулдаг.

Нэг блокт байрлах "Сумыг арилгах" товчлуур нь танд нөлөөлж буй нүднүүдийн сум, хамааралтай нүднүүдийн сум эсвэл хоёр төрлийн сумыг нэг дор арилгах боломжийг олгоно.

Олон хуудаснаас нүдний утгын нийлбэрийг (тоо, дундаж) хэрхэн олох вэ

Танд нэмэх, тоолох эсвэл өөр аргаар боловсруулахыг хүссэн өгөгдөл бүхий ижил төрлийн хэд хэдэн хуудас байна гэж бодъё:

Үүнийг хийхийн тулд үр дүнг харахыг хүссэн нүдэнд стандарт томьёо, жишээлбэл SUM (SUM) оруулаад боловсруулах шаардлагатай хуудасны жагсаалтаас эхний болон сүүлчийн хуудасны нэрийг зааж өгнө үү. аргумент, хоёр цэгээр тусгаарлагдсан:

Та "Data1", "Data2", "Data3" хуудаснаас B3 хаягтай нүднүүдийн нийлбэрийг хүлээн авах болно.

Энэ хаяглалт нь байрлах хуудасны хувьд ажилладаг тууштай … Синтакс нь дараах байдалтай байна: = FUNCTION (эхний_жагсаалт: сүүлчийн_жагсаалт! Хүрээний лавлагаа).

Загвар хэллэгийг хэрхэн автоматаар бүтээх вэ

Excel програмын тексттэй ажиллах үндсэн зарчмууд болон хэд хэдэн энгийн функцуудыг ашиглан тайланд зориулж загвар хэлцүүдийг бэлтгэж болно. Тексттэй ажиллах хэд хэдэн зарчим:

• Бид текстийг & тэмдгийг ашиглан холбоно (та үүнийг CONCATENATE функцээр сольж болно, гэхдээ энэ нь тийм ч утгагүй).
• Текстийг үргэлж хашилтанд бичдэг, тексттэй нүднүүдийн лавлагаа үргэлж байхгүй байна.
• "Хашилт" үйлчилгээний тэмдэгтийг авахын тулд CHAR функцийг аргумент 32 ашиглана уу.

Томъёо ашиглан загвар хэллэг үүсгэх жишээ:

Үр дүн:

Энэ тохиолдолд CHAR функцээс (хашилтыг харуулах) гадна борлуулалтын эерэг хандлага байгаа эсэхээс хамааран текстийг өөрчлөх боломжийг олгодог IF функц, мөн TEXT функцийг ашигладаг. ямар ч форматтай дугаар. Түүний синтаксийг доор тайлбарлав.

TEXT (утга; формат)

Форматыг нүднүүдийн цонхонд тусгай формат оруулж байгаатай адил хашилтанд зааж өгсөн болно.

Илүү төвөгтэй текстийг автоматжуулж болно. Миний практикт "ҮЗҮҮЛЭЛТ төлөвлөгөөтэй харьцуулахад ХХ хувиар буурсан / өссөн нь голчлон FACTOR1-ийн өсөлт / бууралтаас XX, FACTOR2-ын өсөлт / бууралтаас шалтгаалсан" хэлбэрээр удирдлагын тайлангийн урт, гэхдээ ердийн тайлбарыг автоматжуулсан байдаг. YY …" гэсэн өөрчлөлтийн жагсаалттай. Хэрэв та ийм сэтгэгдлүүдийг байнга бичдэг бөгөөд тэдгээрийг бичих үйл явцыг алгоритмжуулж чаддаг бол ядаж зарим ажлыг хэмнэх томьёо эсвэл макро үүсгэхийн тулд нэг удаа толгой эргүүлэх нь зүйтэй.

Холболтын дараа нүд бүрт өгөгдлийг хэрхэн хадгалах вэ

Нүднүүдийг нэгтгэх үед зөвхөн нэг утга хадгалагдана. Excel нь нүдийг нэгтгэхийг оролдохдоо үүнийг анхааруулж байна:

Үүний дагуу, хэрэв танд нүд бүрээс хамаарч томьёо байсан бол тэдгээрийг нэгтгэсний дараа ажиллахаа болино (жишээний 3-4 мөрөнд # N / A алдаа):

Нүднүүдийг нэгтгэж, тэдгээрийн доторх өгөгдлийг хадгалахын тулд (магадгүй танд энэ хийсвэр жишээн дээрх шиг томьёо байгаа байх; магадгүй та нүдийг нэгтгэхийг хүсч байгаа ч ирээдүйд бүх өгөгдлийг хадгалах эсвэл зориудаар нуух) хуудасны дурын нүдийг нэгтгэнэ үү., тэдгээрийг сонгоод Формат зураач командыг ашиглан форматыг нэгтгэх шаардлагатай нүднүүдэд шилжүүлээрэй.

Хэрхэн олон мэдээллийн эх сурвалжаас пивот үүсгэх вэ

Хэрэв та хэд хэдэн өгөгдлийн эх сурвалжаас нэг дор пивот үүсгэх шаардлагатай бол ийм сонголттой тууз эсвэл хурдан хандалтын самбарт "пивот хүснэгт ба диаграмын шидтэн"-ийг нэмэх шаардлагатай болно.

Та үүнийг дараах байдлаар хийж болно: "Файл" → "Сонголтууд" → "Түргэн хандалтын хэрэгслийн мөр" → "Бүх тушаалууд" → "Пивот хүснэгт ба диаграмын хөтөч" → "Нэмэх":

Үүний дараа туузан дээр харгалзах дүрс гарч ирэх бөгөөд ижил шидтэнг дууддаг дээр дарна уу.

Үүн дээр дарахад харилцах цонх гарч ирнэ:

Үүний дотор та "Хэд хэдэн нэгтгэх мужид" гэсэн зүйлийг сонгоод "Дараах" дээр дарна уу. Дараагийн алхамд та "Нэг хуудасны талбар үүсгэх" эсвэл "Хуудасны талбар үүсгэх"-ийг сонгож болно. Хэрэв та өгөгдлийн эх сурвалж бүрийн нэрийг бие даан гаргахыг хүсвэл хоёр дахь зүйлийг сонгоно уу:

Дараагийн цонхонд тэнхлэгийг бий болгох бүх мужийг нэмж, нэр өгнө үү.

Үүний дараа, сүүлчийн харилцах цонхонд пивот хүснэгтийн тайланг одоо байгаа эсвэл шинэ хуудсан дээр хаана байрлуулахыг зааж өгнө үү.

Пивот хүснэгтийн тайлан бэлэн боллоо. "Хуудас 1" шүүлтүүрээс шаардлагатай бол өгөгдлийн эх сурвалжаас зөвхөн нэгийг нь сонгож болно:

В текст дэх А текстийн тохиолдлын тоог хэрхэн тооцоолох вэ ("MTS SuperMTS тариф" - MTS товчлолын хоёр тохиолдол)

Энэ жишээнд А баганад хэд хэдэн текст мөр байгаа бөгөөд бидний даалгавар бол E1 нүдэнд байрлах хайлтын текст тус бүрийг хэдэн удаа агуулж байгааг олж мэдэх явдал юм.

Энэ асуудлыг шийдэхийн тулд та дараах функцүүдээс бүрдсэн цогц томъёог ашиглаж болно.

1. DLSTR (LEN) - текстийн уртыг тооцоолдог, цорын ганц аргумент нь текст юм. Жишээ нь: DLSTR ("машин") = 6.
2. SUBSTITUTE - текстийн мөр дэх тодорхой текстийг өөр текстээр солино. Синтакс: ОРЛУУЛАХ (текст; хуучин_текст; шинэ_текст). Жишээ нь: ОРЛУУЛАХ (“машин”; “авто”; “”) = “гар утас”.
3. UPPER - мөрөнд байгаа бүх тэмдэгтийг том үсгээр солино. Цорын ганц аргумент бол текст юм. Жишээ нь: UPPER (“машин”) = “CAR”. Том жижиг жижиг хайлт хийхэд бидэнд энэ функц хэрэгтэй. Эцсийн эцэст ДЭЭД ("машин") = ДЭЭД ("Машин")

Тодорхой текстийн мөрийн өөр текстийн илрэлийг олохын тулд та түүний эх хувилбар дахь бүх тохиолдлыг устгаж, үүссэн мөрийн уртыг эхтэй нь харьцуулах хэрэгтэй.

DLSTR (“Тариф MTS Super MTS”) - DLSTR (“Super Tarif”) = 6

Дараа нь энэ ялгааг бидний хайж байсан мөрний уртаар хуваа.

6 / DLSTR (“MTS”) = 2

"MTS" гэсэн мөрийг эхэнд нь яг хоёр удаа оруулсан байна.

Энэ алгоритмыг томъёоны хэлээр бичихэд л үлдлээ (бидний хайж буй тохиолдлуудыг "текст"-ээр, "хайж буй" гэж - бидний сонирхож буй тохиолдлын тоог тэмдэглэе):

= (DLSTR (текст) -LSTR (ОРЛУУЛАХ (ДЭЭД (текст); ДЭЭД (хайх), ""))) / DLSTR (хайлт)

Бидний жишээн дээр томъёо дараах байдалтай байна.

= (DLSTR (A2) -LSTR (ОРЛУУЛАХ (ДЭЭД (A2), ДЭЭД ($ E $ 1), ""))) / DLSTR ($ E $ 1)