- 1. Сума альтернативних стовпців / рядків
- 2. Підрахунок кількості повторень в листі А елементів з листа Б
- 3. Швидкий підрахунок даних за допомогою СЧЁТЕСЛІ і СУММЕСЛИ
- 4. Пошук другого, третього ... n -ного входження елемента в списку EXCEL
- 5. Скорочуємо кількість вкладень функції ЯКЩО
Excel має різні функції, включаючи функції розрахунку арккосинуса заданого значення, множення двох матриць, оцінки внутрішньої норми прибутковості. Але більшість з нас (ну, і я в тому числі) використовує не більше 5-6 формул для роботи. І формула ЯКЩО знаходиться в цьому списку. Так що, не зашкодить дізнатися ще кілька цікавих речей, які можна зробити тільки з однією функцією ЯКЩО.
1. Сума альтернативних стовпців / рядків
Трапляється таке, коли нам необхідно підсумувати кожну другий рядок стовпця (не питайте мене навіщо, таке трапляється і не рідко). Звичайно, ми могли б підсумувати кожну клітинку окремо, але це не наш варіант. Замість цього ми влаштуємо собі каву-брейк, в той час як Excel зробить за нас всю брудну роботу. Для цього нам знадобиться альтернативний стовпець і функція СУММЕСЛИ, яка підсумовує осередку, задані певною умовою. У нашому випадку критерієм буде «якщо елемент знаходиться в непарних рядках».
Все що нам потрібно зробити, це додати додатковий стовпець і заповнити його одиницями і нулями (введіть 1 і 0 в два рядки, виділіть обидві комірки і перетягніть до кінця таблиці). Тепер ми можемо використовувати наш стовпець для визначення умови за допомогою функції = СУММЕСЛИ (діапазон умови; 1; діапазон підсумовування).
2. Підрахунок кількості повторень в листі А елементів з листа Б
Часто, коли ви працюєте з кількома листами, необхідно підрахувати кількість повторень будь-якого елементу на іншому аркуші. Це можна реалізувати за допомогою старої доброї СЧЕТЕСЛІ, яка вважає кількість осередків, що задовольняють заданій умові.
В наведеному вище прикладі я використовував функцію СЧЁТЕСЛІ, щоб порахувати, скільки клієнтів знаходитися в певному місті (де список з клієнтами знаходиться на Листі 2, список з містами на Листі 1). Формула виглядає наступним чином = СЧЁТЕСЛІ (діапазон умов; умова). Наприклад, формула СЧЁТЕСЛІ ($ E $ 6: $ E $ 16; "Волгоград") скаже мені, скільки клієнтів перебувати в Волгограді.
3. Швидкий підрахунок даних за допомогою СЧЁТЕСЛІ і СУММЕСЛИ
Тепер, коли ми з'ясували, як користуватися формулами СЧЁТЕСЛІ і СУММЕСЛИ, може використовувати їх разом.
Наприклад, ми можемо з'ясувати середній обсяг доходу від клієнта, що припадає на кожне місто шляхом ділення суми доходу міста на кількість клієнтів. Це швидкий зведений аналіз без використання інструменту побудови зведених таблиць.
4. Пошук другого, третього ... n -ного входження елемента в списку EXCEL
Часто трапляється так, що ми працюємо зі списком даних, що мають багато дублікатів, наприклад, в список телефонів клієнта з часом можуть додаватися нові дані. Отримати друге, третє ... десята входження може бути не простим. За допомогою комбінацій СЧЁТЕСЛІ і ВПР ми можемо знайти будь-яка поява в списку.
Для початку додамо додаткову колонку в таблицю з даними про клієнтів і пропишемо в осередку формулу, яка до імені клієнта додає порядковий номер входження імені в списку. Таким чином, при першій появі в списку, формула видасть імя1, при другому імя2 і так далі.
Далі, при пошуку інформації про Крістіни Агілери, ми будемо користуватися зміненими даними клієнтів. Наприклад, формула ВПР ( "Христина Агілера3", діапазон пошуку, 2, БРЕХНЯ) видасть нам третій номер телефону Крістіни Агілери. Зверніть увагу, що останній аргумент формули ВПР буде БРЕХНЯ, так як наш списку не відсортований за алфавітом і нам потрібно точний збіг значень пошуку.
5. Скорочуємо кількість вкладень функції ЯКЩО
У більш ранніх версіях Excel, допускалося всього 7 рівнів входження функції ЯКЩО. Починаючи з Excel 2007 це обмеження зняте. На щастя, більшість з нас ніколи не опускалося нижче 3-го або 4-го рівня. Але навіщо писати такі багаторівневі формули, якщо можна скористатися функцією ВИБІР, яка вибирає значення або дію зі списку значень за номером індексу. Синтаксис функції виглядає наступним чином ВИБІР (номер_індекса; значеніе_прі_індексе_равном_1; значеніе_прі_індексе_равном_2; значеніе_прі_індексе_равном_2 ...). Наприклад, наступна функція, ВИБІР (3; «Відмінно», «Добре», «Погано»), поверне «Погано», якщо її вбити в клітинку. Є одне обмеження, значення номера індексу повинно бути задано в числовому форматі, тому буде потрібно трохи творчості (як в наступному прикладі), коли значення індексу не можна задати явно:
Як я перевів значення букви в число в функції ВИБІР (), ну нехай це буде вашим домашнім завданням)
