Microsoft Excel - мощный инструмент для работы с данными, который предоставляет широкий спектр функций и формул для упрощения анализа и обработки информации. В этой статье мы рассмотрим топ 50 полезных формул Excel, предоставляя подробный разбор каждой из них.
1-10: Основные формулы
1. SUM (СУММА) - Суммирует значения в диапазоне ячеек.
=SUM(A1:A10)
2. AVERAGE (СРЗНАЧ) - Рассчитывает среднее значение для диапазона ячеек.
=AVERAGE(B1:B5)
3. COUNT (КОЛИЧЕСТВО) - Подсчитывает количество ячеек с числовыми значениями в диапазоне.
=COUNT(C1:C8)
4. MIN (МИН) - Находит минимальное значение в диапазоне ячеек.
=MIN(D1:D12)
5. MAX (МАКС) - Находит максимальное значение в диапазоне ячеек.
=MAX(E1:E15)
6. IF (ЕСЛИ) - Выполняет условное действие, в зависимости от того, истинно условие или нет.
=IF(F2>10, "Больше 10", "Меньше или равно 10")
7. VLOOKUP (ВПР) - Ищет значение в первом столбце диапазона и возвращает соответствующее значение из другого столбца.
=VLOOKUP(G2, A1:B20, 2, FALSE)
8. HLOOKUP (ГПР) - Ищет значение в первой строке диапазона и возвращает соответствующее значение из другой строки.
=HLOOKUP(H2, A1:D5, 3, FALSE)
9. INDEX (ИНДЕКС) - Возвращает значение из диапазона ячеек по указанным координатам строки и столбца.
=INDEX(A1:C3, 2, 3)
10. MATCH (СОВПАДЕНИЕ) - Ищет значение в диапазоне и возвращает его позицию.
=MATCH(I2, D1:D10, 0)
11-20: Текстовые формулы
11. CONCATENATE (СЦЕПИТЬ) - Объединяет текст из нескольких ячеек.
=CONCATENATE(A1, " ", B1)
12. LEFT (ЛЕВ) - возвращает указанное количество символов из начала текста.
=LEFT(C2, 5)
13. RIGHT (ПРАВ) - Возвращает указанное количество символов с конца текста.
=RIGHT(D2, 3)
14. LEN (ДЛСТР) - Возвращает количество символов в ячейке.
=LEN(E2)
15. MID (СРЕД) - Возвращает определенное количество символов из текста, начиная с указанной позиции.
=MID(F2, 2, 4)
16. LOWER (ПРОПИСН) - Преобразует текст в нижний регистр.
=LOWER(G2)
17. UPPER (ЗАГЛАВН) - Преобразует текст в верхний регистр.
=UPPER(H2)
18. PROPER (ПРОПИСН1) - Преобразует текст так, чтобы первая буква каждого слова была заглавной.
=PROPER(I2)
19. TRIM (СЖПРОБЕЛЫ) - Удаляет все пробелы из текста, кроме одиночных пробелов между словами.
=TRIM(J2)
20. SUBSTITUTE (ЗАМЕНА) - Заменяет один текст на другой в ячейке.
=SUBSTITUTE(K2, "a", "o")
21-30: Формулы для работы с датами и временем
21. NOW (СЕЙЧАС) - Возвращает текущую дату и время.
=NOW()
22. TODAY (СЕГОДНЯ) - Возвращает текущую дату.
=TODAY()
23. DATE (ДАТА) - Создает дату на основе указанных года, месяца и дня.
=DATE(2024, 1, 19)
24. TIME (ВРЕМЯ) - Создает время на основе указанных часов, минут и секунд.
=TIME(12, 30, 0)
25. YEAR (ГОД) - Извлекает год из даты.
=YEAR(M2)
26. MONTH (МЕСЯЦ) - Извлекает месяц из даты.
=MONTH(N2)
27. DAY (ДЕНЬ) - Извлекает день из даты.
=DAY(O2)
28. HOUR (ЧАС) - Извлекает час из времени.
=HOUR(P2)
29. MINUTE (МИНУТА) - Извлекает минуты из времени.
=MINUTE(Q2)
30. SECOND (СЕКУНДА) - Извлекает секунды из времени.
=SECOND(R2)
31-40: Финансовые формулы
31. PV (ТП) - Определяет текущую стоимость инвестиции.
=PV(0.05, 10, 2000)
32. FV (ТВ) - Определяет будущую стоимость инвестиции.
=FV(0.05, 10, -2000)
33. RATE (Ставка) - Рассчитывает процентную ставку для инвестиции.
=RATE(10, -200, 1000)
34. NPER (Срок) - Рассчитывает количество периодов для инвестиции.
=NPER(0.05, -200, 1000)
35. PMT (ПЛТ) - Рассчитывает ежемесячный платеж по кредиту.
=PMT(0.05/12, 5*12, -20000)
36. IRR (ВНД) - Рассчитывает внутреннюю норму доходности.
=IRR({-5000, 1500, 2000, 2500, 3000})
37. NPV (ЧДД) - Рассчитывает чистую приведенную стоимость инвестиции.
=NPV(0.1, -1000, 300, 400, 600, 800)
38. CUMIPMT (НАКОППЛТ) - Рассчитывает накопленные проценты по кредиту до указанного периода.
=CUMIPMT(0.05, 3, 5*12, -20000, 1, 2)
39. CUMPRINC (НАКОППЛТОСН) - Рассчитывает накопленный основной долг по кредиту до указанного периода.
=CUMPRINC(0.05, 3, 5*12, -20000, 1, 2)
40. DDB (АМОРД) - Рассчитывает амортизацию с использованием метода уменьшающегося остатка.
=DDB(1000, 100, 5, 1)
41-50: Логические и информационные формулы
41. IFERROR (ЕСЛИОШИБКА) - Определяет, есть ли ошибка в формуле, и возвращает заданное значение, если ошибка обнаружена.
=IFERROR(1/0, "Ошибка деления на ноль")
42. IFNA (ЕСЛИНЕДОСТУПНО) - Определяет, является ли значение ошибкой #N/A, и возвращает заданное значение, если ошибка обнаружена.
=IFNA(VLOOKUP(A1, B1:C10, 2, FALSE), "Значение не найдено")
43. AND (И) - Возвращает TRUE, если все аргументы истинны, и FALSE в противном случае.
=AND(D2>10, E2<100)
44. OR (ИЛИ) - Возвращает TRUE, если хотя бы один из аргументов истинен, и FALSE в противном случае.
=OR(F2="Апрель", F2="Май", F2="Июнь")
45. NOT (НЕ) - Инвертирует значение логического выражения.
=NOT(G2>0)
46. IFERROR (ЕСЛИОШИБКА) - Определяет, есть ли ошибка в формуле, и возвращает заданное значение, если ошибка обнаружена.
=IFERROR(1/0, "Ошибка деления на ноль")
47. TEXT (ТЕКСТ) - Форматирует значение как текст с использованием заданного формата.
=TEXT(NOW(), "dd.mm.yyyy hh:mm:ss")
48. LEN (ДЛСТР) - Возвращает количество символов в ячейке.
=LEN(E2)
49. ISNUMBER (ЯВЛЯЕТСЯЧИСЛОМ) - Возвращает TRUE, если значение является числом, и FALSE в противном случае.
=ISNUMBER(H2)
50. IF (ЕСЛИ) - Выполняет условное действие, в зависимости от того, истинно условие или нет.
=IF(I2="Да", "Оплачено", "Не оплачено")
Эти формулы представляют лишь малую часть возможностей Microsoft Excel. Знание этих функций и формул позволит вам более эффективно обрабатывать данные и создавать сложные отчеты, что в свою очередь увеличит вашу производительность в работе с таблицами и документами.
