Если вы когда-либо работали с таблицами или данными, то наверняка сталкивались с программой Microsoft Excel. Но знаете ли вы обо всех её возможностях?
Что такое Excel?
Excel – это программа для обработки данных, которая позволяет организовывать информацию в виде электронных таблиц (так называемых "рабочих листов"). С помощью этой программы можно создавать отчёты, анализировать данные, строить графики и диаграммы, выполнять расчёты и многое другое.
Для какой работы нужен Excel?
Использовать Excel можно практически во всех сферах деятельности. Вот несколько примеров:
- Финансы: вести учёт доходов и расходов, рассчитывать проценты по кредитам, прогнозировать доходы компании.
- Бизнес-аналитика: обрабатывать большие объёмы информации, выявлять закономерности и тренды.
- Образование: составлять расписание занятий, выставлять оценки студентам, подсчитывать средний балл успеваемости группы.
- Личное использование: планировать семейный бюджет, вести списки покупок, следить за своим весом и т.д.
Теперь перейдём к тому, ради чего мы здесь собрались – поговорим о специальных функциях и комбинациях клавиш, о которых знают далеко не все пользователи!
Специальные функции для повседневной работы.
Для обычных пользователей Microsoft Excel предлагает целый набор удобных специализированных функций, которые делают повседневную работу намного проще и комфортнее. Давайте рассмотрим самые полезные из них:
1. СУММЕСЛИ / СУММЕСЛИМН (SUMIF/SUMIFS)
Функция для суммирования данных по условию (или нескольким условиям). Например, подсчитать сумму продаж конкретного товара или сотрудника.
Формула: =СУММЕСЛИ(Диапазон_условия; Условие; Диапазон_суммы)
Пример: =СУММЕСЛИ(A1:A10; "яблоки"; B1:B10), здесь суммируются продажи яблок.
2. СЧЁТЕСЛИ / СЧЁТЕСЛИМН (COUNTIF/COUNTIFS)
Подсчет ячеек по одному или нескольким критериям. Полезно, если нужно посчитать количество товаров определенного типа или сотрудников определенной должности.
Формула: =СЧЁТЕСЛИ(Диапазон; Критерий)
Пример: =СЧЁТЕСЛИ(A1:A10; ">50"), считает количество ячеек, содержащих значения больше 50.
3. ЕСЛИ (IF)
Простейшая логика для проверки условий и возвращения разных значений в зависимости от условия.
Формула: =ЕСЛИ(Условие; Значение_если_Истина; Значение_если_Ложь)
Пример: =ЕСЛИ(A1 > 100; "Высокий уровень"; "Низкий уровень"), вернет сообщение в зависимости от значения в ячейке А1.
4. ВЫБОР (CHOOSE)
Выбирает одно из предложенных значений в зависимости от номера позиции.
Формула: =ВЫБОР(номер; значение1; значение2; ...)
Пример: =ВЫБОР(2; "Москва"; "Санкт-Петербург"; "Новосибирск"), вернет второе значение ("Санкт-Петербург").
5. ПРОПИСНАЯ (UPPER), СТРОЧНАЯ (LOWER), ПРОПНАЧ (PROPER)
Преобразование регистра текста. Помогает привести имена, названия компаний или города к единому формату.
Примеры:
=ПРОПИСНАЯ("привет")
=СТРОЧНАЯ("Привет")
=ПРОПНАЧ("ИВАНОВ ИВАН ИВАНОВИЧ")
Первая формула сделает весь текст заглавным, вторая – строчным, третья – каждое слово начнётся с большой буквы.
6. ПОИСКПОЗ (MATCH)
Определяет позицию элемента в списке. Часто используется совместно с функцией ИНДЕКС для удобной выборки данных.
Формула: =ПОИСКПОЗ(искомое_значение; диапазон; тип_соответствия)
Пример: =ПОИСКПОЗ("Иван"; A1:A10; 0), вернёт номер строки, где находится имя "Иван".
7. ИНДЕКС (INDEX)
Возвращает значение из указанного диапазона по индексу строк и столбцов.
Формула: =ИНДЕКС(массив; номер_строки; номер_столбца)
Пример: =ИНДЕКС(A1:C10; 3; 2), вернёт значение из третьей строки второго столбца диапазона.
8. СРЗНАЧЕСЛИ (AVERAGEIF)/СРЗНАЧЕСЛИМН (AVERAGEIFS)
Среднее арифметическое по указанному критерию (или нескольким критериям).
Формула: =СРЗНАЧЕСЛИ(Диапазон_условия; Условие; Диапазон_среднего)
Пример: =СРЗНАЧЕСЛИ(A1:A10; "<50"; B1:B10), средняя сумма заказов меньше 50 рублей.
9. СЖПРОБЕЛЫ (TRIM)
Удаляет лишние пробелы вокруг текста и внутри него, оставляя только одинарные пробелы между словами.
Формула: =СЖПРОБЕЛЫ(" Привет мир! "), результатом будет строка "Привет мир!".
10. ОКРУГЛ (ROUND)
Округление числа до нужного количества знаков после запятой.
Формула: =ОКРУГЛ(число; кол-во_цифр)
Пример: =ОКРУГЛ(3.14159; 2), округлит число Пи до двух знаков после запятой: 3.14.
Специальные функции для работяг:
Функции даты и времени
1. ТЕКСТДАТА ()
Преобразует дату, указанную текстом, в стандартное значение даты Excel.
Формула: =ТЕКСТДАТА(дата_нестандарт._значения)
Пример: =ТЕКСТДАТА("1 января 2023")
2. РАБДЕНЬ ()
Вычисляет рабочий день, учитывая праздничные дни и стандартные рабочие графики.
Формула: =РАБДЕНЬ(дата_начала; номер_рабочего_дня)
Пример: =РАБДЕНЬ("1 мая 2023"; 10)
3. ЧИСТРАБДНИ ()
Подсчитывает количество рабочих дней между двумя датами.
Формула: =ЧИСТРАБДНИ(дата1; дата2)
Пример: =ЧИСТРАБДНИ("1 июня 2023"; "30 июня 2023")
Логические функции
1. ЕСЛИОШИБКА ()
Обрабатывает ошибку и возвращает заданное значение вместо нее.
Формула: =ЕСЛИОШИБКА(формула; значение_при_ошибке)
Пример: =ЕСЛИОШИБКА(1/0;"Ошибка!")
2. ПЕРЕТРАСН ()
Меняет ориентацию диапазона данных с горизонтальной на вертикальную и наоборот.
Формула: =ПЕРЕТРАСН(диапазон_ячеек)
Пример: ={ПЕРЕТРАСН(A1:B1)}
Финансовые функции
1. ПС ()
Рассчитывает приведенную стоимость инвестиции исходя из процентной ставки, количества периодов и регулярных платежей.
Формула: =ПС(ставка; кпер; плт; бс; тип)
Пример: =ПС(10%/12; 5*12; -2000; 0; 0)
2. ЭФФЕКТ ()
Определяет эффективную годовую ставку процента на основе номинальной ставки и количества начислений.
Формула: =ЭФФЕКТ(номинальная_ставка; количество_начислений)
Пример: =ЭФФЕКТ(10%; 4)
Статистические функции
1. РАНГ.РАВН ()
Определяет ранг числа среди набора данных, сравнивая его с другими значениями.
Формула: =РАНГ.РАВН(число; ссылка; порядок)
Пример: =РАНГ.РАВН(A5; A1:A10)
Или, если нужен рейтинг в порядке возрастания: =РАНГ.РАВН(A5; A1:A10; 1)
2. СТАНДОТКЛОН.Г ()
Вычисляет стандартное отклонение выборочной совокупности.
Формула: =СТАНДОТКЛОН.Г(число1; [число2]; ...)
Пример: =СТАНДОТКЛОН.Г(A1:A10)
Инженерные функции
1. ДВОИЧН.В.ДЕСЯТ ()
Преобразует двоичное число в десятичное.
Формула: =ДВОИЧН.В.ДЕСЯТ(двоичное_число)
Пример: =ДВОИЧН.В.ДЕСЯТ("1010")
2. ШЕСТНАДЦ.В.ПЯТИР ()
Преобразует шестнадцатеричное число в пятеричную систему счисления.
Формула: =ШЕСТНАДЦ.В.ПЯТИР(шестнадцатеричное_число; [разрядность])
Пример: =HEX2OCT("F")
Математические функции
1. МОБРАЗНОСТЬ ()
Вычисляет определитель матрицы.
Формула: =МОБРАЗНОСТЬ(матрица)
Пример: МОБРАЗНОСТЬ({1;2|3;4})
2. РИМСКОЕ ()
Преобразует арабские цифры в римские.
Формула: =РИМСКОЕ(число; [вариант])
Пример: =РИМСКОЕ(2023), Результатом будет: MMXXIII.
Спецфункции Excel, о которых мало кто знает
Сортировка по цвету ячеек
Вы знали, что можно сортировать ячейки не только по алфавиту или числовым значениям, но и по цвету заливки? Это особенно полезно при работе с большими объёмами данных, где нужно быстро выделить определённые строки или столбцы. Чтобы воспользоваться этим функционалом, выберите нужный диапазон, затем зайдите в меню «Данные» → «Сортировка», там найдите опцию «Сортировать по формату».
Автозаполнение формул без перетаскивания мышью
Если вам надоело вручную протягивать формулы вниз таблицы, попробуйте нажать сочетание клавиш Ctrl + D после того как ввели формулу в первую ячейку. Программа автоматически заполнит выбранные ниже ячейки аналогичной формулой, изменяя ссылки соответствующим образом.
Быстрое создание сводных таблиц
Многие пользователи боятся сводных таблиц из-за кажущейся сложности их создания. На самом деле всё очень просто: выделите область данных, нажмите Alt + A + F + T (в английской раскладке) и следуйте инструкциям мастера. Всего пара кликов – и у вас готовая таблица со всеми нужными фильтрами и группировками.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Когда требуется быстро получить промежуточные итоги по группе строк или столбцов, воспользуйтесь функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Например, если у вас есть список товаров с ценами, а внизу необходимо вывести общую сумму каждой категории товара, введите =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B:B), где 9 означает функцию СУММ.
Горячие клавиши для быстрого перемещения между листами
При работе с несколькими рабочими листами удобно использовать сочетания клавиш для быстрой навигации: Ctrl + Page Up / Page Down позволит мгновенно перемещаться между соседними листами книги.
Скрытие нулевых значений
Иногда наличие большого количества пустых или нулевых ячеек мешает восприятию важных данных. В этом случае можно скрыть такие значения следующим способом: выберите нужные ячейки, кликните правой кнопкой мыши, откройте контекстное меню и выберите пункт «Формат ячеек». Перейдите на вкладку «Число», далее выберите категорию «Пользовательский формат» и введите в поле типа формата 0;-0;;@. Теперь нули исчезнут из вашего поля зрения.
Автофильтр по дате
Нужно выбрать даты, попадающие в определенный период времени? Просто используйте автофильтр. Выделите диапазон дат, щелкните кнопку фильтра («Фильтры»), выберите интересующий интервал времени ("Дата") и укажите начало и конец периода.
Вставка текущей даты одним нажатием кнопки
Не всегда удобно вводить дату вручную каждый раз, когда нужно зафиксировать текущее время. Используйте сочетание клавиш Ctrl + ; чтобы вставить текущую дату прямо сейчас.
Копирование только видимых ячеек
Часто бывает так, что часть данных скрыта от глаз пользователя благодаря использованию фильтров или условного форматирования. Однако, когда приходит необходимость скопировать только те данные, которые видны пользователю, достаточно выделить весь диапазон, затем нажать Shift+F10 (или правую кнопку мыши), и выбрать команду «Копировать только видимую часть».
Мы познакомились со специальными функциями Excel, которые упрощают решение многих задач. Главное — научиться правильно выбирать инструменты и применять их на практике. Надеюсь, эти советы помогут вам повысить эффективность вашей работы в Excel и сделать процесс более приятным и продуктивным! Делитесь своими любимыми трюками в комментариях, возможно, именно ваш опыт станет полезным для кого-то другого. Изучайте функции, экспериментируйте, совершенствуйте навыки, раскрывайте потенциал Excel и повышайте свою производительность. Успехов в работе с программой!