Что можно сделать в экселе

Новые возможности Excel 2021 для Windows

Excel 2021 для Windows позволяет совместно работать с другими пользователями и с легкостью анализировать данные с помощью новых возможностей Excel, включая совместное редактирование, динамические массивы, функции XLOOKUP и LET.

Примечание: Некоторые перечисленные ниже функции не включены в Excel LTSC 2021 для коммерческих клиентов. Каждая из этих функций будет иметь отметку, если она не применима к Excel LTSC 2021 для коммерческих клиентов.

Совместное редактирование

Вы и ваши коллеги можете открывать и работать над одной и той же книгой Excel. Это называется совместное редактирование. После совместного редактирования вы можете быстро увидеть изменения друг друга — за считанные секунды.

Примечание. Совместное редактирование недоступно в Excel LTSC 2021.

Улучшение совместной работы с помощью современных комментариев

Управляйте временем отправки комментариев совместным редакторам и будьте продуктивны благодаря последовательной работе с комментариями в рабочих книгах и других приложениях Office.

Примечание. Современные комментарии недоступны в Excel LTSC 2021.

Узнайте, кто в вашей книге

Узнайте, кто еще работает вместе с вами и где они находятся в книге.

Примечание. Эта функция недоступна в Excel LTSC 2021.

Визуальное обновление

Воспользуйтесь современным интерфейсом меню «Пуск» и обновленными вкладками на ленте. С помощью монолинейного стиля изображений, нейтральных цветовых палитр и скругленных углов окна можно создавать четкий и чистый дизайн. Эти обновления сообщают о действиях и предоставляют функции с помощью простых визуальных средств.

Примечание. Визуальное обновление недоступно в Excel LTSC 2021.

Посмотрите налево, посмотрите направо… XLOOKUP уже здесь!

Найдите все, что вам нужно, по строкам в таблице или диапазоне с помощью XLOOKUP​.

Точные совпадения возвращаются по умолчанию — указывать их не нужно.

Функция ПУСТЬ

Функция LET присваивает имена результатам вычислений. Это позволяет хранить промежуточные вычисления, значения или определять имена в формуле. Эти имена применяются только в области функции LET.

Как и переменные в программировании, функция LET выполняется с помощью синтаксиса формул Excel.

Динамические массивы

Напишите одну формулу и возвращайте массив значений. Ускоряют вычисления и анализ с помощью шести
​ новых функций: FILTER, SORT, SORTBY, UNIQUE, SEQUENCEи RANDARRAY.

Функция ПОИСКПОЗX

Функция XMATCH выполняет поиск указанного элемента в массиве или диапазоне ячеек, а затем возвращает относительное положение элемента.

Функцию XMATCH также можно использовать для возврата значения в массиве.

Представления листа

Создавайте настраиваемые представления на листе Excel, не нарушая работу других.

Примечание. Представления листа недоступны в Excel LTSC 2021.

Новый способ работы со средствами специальных возможностей.

Все средства, необходимые для создания содержимого, поддерживающего специальные возможности, объединены в одном месте, на ленте «Специальные возможности».

Примечание. Лента «Специальные возможности» недоступна в Excel LTSC 2021.

Увеличьте охваты вашего контента

Проверка читаемости отслеживает содержание документов и сообщает в строке состояния, когда находит что-то, на что следует обратить внимание. Попробуйте, нажав Рецензирование > Проверка читаемости.

Повышение производительности

Улучшена производительность, стабильность и скорость работы Excel.

Ускоряйте вычисления с помощью основных функций Excel, таких как СУММЕСЛИ, СЧЁТЕСЛИ и СРЗНАЧЕСЛИ.

Одновременное отображение нескольких листов

Больше не нужно отображать листы по одному. Отображайте несколько скрытых листов одновременно.

Узнайте, что нового в стоковых мультимедиа

Мы постоянно добавляем разнообразное мультимедиа-содержимое в коллекцию Office Premium Creative Content, чтобы вы могли выразить себя, например библиотеки стоковых изображений, значков и многое другое.

Находите то, что вам нужно с помощью Поиска (Майкрософт)

В верхней части приложений Microsoft Office в Windows вы найдете новое поле Поиска (Майкрософт). Это мощное средство помогает быстро находить то, что вам нужно, например текст, команды, справку и т. д.

Сохранение изменений по мере внесения

Загрузите файлы в OneDrive, OneDrive для бизнеса или SharePoint Online, чтобы убедиться, что все обновления сохраняются автоматически.

Примечание. Автосохранение недоступно в Excel LTSC 2021.

Поддержка формата OpenDocument (ODF) 1.3

Теперь поддерживается формат OpenDocument (ODF) 1.3. Спецификация ODF 1.3 добавляет поддержку многих новых функций.

Обновленная вкладка «Рисование»

Вы можете быстро получать доступ ко всем инструментам для работы с красками и менять их цвет в одном месте.

Упростите работу с рукописным вводом с помощью новых дополнений вкладки «Рисование»: Точечный ластик, Линейка и Лассо.

Краткие сведения о том, что есть в книге

Хотите узнать, насколько большими становятся ваш лист или таблица? Excel предоставляет статистику по книге, которая поможет вам.

Выберите идеальный цвет

На основе ваших отзывов мы добавили в диалоговое окно Цвета новое поле ввода для шестнадцатеричных значений цветов. Вам не потребуется преобразовывать шестнадцатеричные значения цветов в значения RGB.

Для любого свойства, в котором можно определить цвет, теперь можно ввести в поле Hex шестнадцатеричное значение цвета, например #0F4C81 или 444.

Попробуйте структуру стиля «Набросок»

Вы можете придать фигурам в книге неформальный вид с помощью структуры стиля Набросок.

Параметры «Кривая», «Рисованная» и «Рисованная кривая» можно найти в меню Формат фигуры > Линия > Набросок.

Развивайте навыки работы с Office

Нужна дополнительная помощь?

Нужны дополнительные параметры?

Изучите преимущества подписки, просмотрите учебные курсы, узнайте, как защитить свое устройство и т. д.

В сообществах можно задавать вопросы и отвечать на них, отправлять отзывы и консультироваться с экспертами разных профилей.

Excel

Microsoft Excel — программа, позволяющая работать с электронными таблицами. Можно собирать, преобразовывать и анализировать данные, проводить визуализацию информации, автоматизировать вычисления и выполнять еще ряд полезных и необходимых в работе задач.

Освойте профессию «Аналитик данных»

Изучение возможностей Excel может быть полезно в рамках практически любой профессии и сферы деятельности, от работников продаж до бухгалтеров и экономистов.

Возможности Microsoft Excel

Работа с формулами и числовыми данными

Excel может выполнять практически всё: от простых операций вроде сложения, вычитания, умножения и деления до составления бюджетов крупных компаний.

Работа с текстом

Несмотря на то что некоторые возможности Word в Excel неприменимы, программа очень часто является базовой для составления отчетов.

Организация баз данных

Excel — табличный редактор, поэтому систематизация больших архивов не является для него проблемой. Кроме того, благодаря перекрестным ссылкам можно связать между собой различные листы и книги.

Находите закономерности и делайте выводы, которые помогут бизнесу

Построение графиков и диаграмм

Для создания отчетов очень часто требуется их визуальное представление. В современных версиях Excel можно создать диаграммы и графики любого типа, настроив их по своему усмотрению.

Создание рисунков

С помощью настройки графических объектов, встроенных в программу, можно создавать двухмерные и трехмерные рисунки.

Автоматизация стандартных задач

Excel обладает функцией записи макросов, которые облегчают работу с однотипными действиями. Под любой макрос можно создать отдельную кнопку на рабочей панели или установить сочетание горячих клавиш.

Импорт и экспорт данных

Для создания масштабных отчетов можно загружать данные различных типов со сторонних ресурсов.

Собственный язык программирования

Язык программирования Visual Basic позволяет сделать работу в программе максимально удобной. Большое количество встроенных функций помогают сделать таблицы интерактивными, что упрощает восприятие.

Интерфейс Excel

В настоящий момент самой современной, 16-й версией программы является Excel 2019. Обновления, появляющиеся с каждой новой версией, касаются прежде всего новых формул и функций. Начальный рабочий стол с версии 2007 года претерпел мало изменений.

Начальный рабочий стол Excel

По умолчанию в каждой книге присутствует один лист (в ранних версиях — три листа). Количество листов, которые можно создавать в одной книге, ограничено только возможностями оперативной памяти компьютера. Поле Excel представляет собой таблицу из ячеек. Каждая ячейка имеет свой уникальный адрес, образованный пересечением строк и столбцов. Всего в Excel 1 048 576 строк и 16 384 столбца, что дает 2 147 483 648 ячеек. Над полем с ячейками находится строка функций, в которой отображаются данные, внесенные в ячейки или формулы. Также в программе есть несколько вкладок, которые мы разберем подробнее.

Вкладки в Excel

«Файл». С помощью этой вкладки можно отправить документы на печать, установить параметры работы в программе и сделать другие базовые настройки.

«Главная». Здесь располагается основной набор функций: возможность смены параметров шрифта, сортировка данных, простейшие формулы и правила условного форматирования.

«Вставка». Вкладка предназначена для добавления графических элементов на лист. Пользователь может как добавить обычные рисунки и фотографии, так и создать 2D- и 3D-объекты через конструктор. Кроме того, один из самых важных разделов программы — графики и диаграммы — также находится здесь.

«Разметка страницы». Здесь пользователь может менять формат итогового файла, работать с темой и подложкой.

«Формулы». Все формулы и функции, с которыми пользователь может работать в программе, собраны в этой вкладке и рассортированы по соответствующим разделам.

«Данные». Вкладка помогает с фильтрацией текстовых и числовых значений в таблицах, позволяет импортировать данные из других источников.

«Рецензирование». Здесь можно оставлять примечания к ячейкам, а также устанавливать защиту листа и всей книги.

«Вид». На этой вкладке можно добавлять или убирать сетку таблицы, масштабировать значения, закреплять группы данных в таблицах.

«Разработчик». Дополнительная вкладка, которая не включена по умолчанию. Позволяет работать с Visual Basic, создавать новые макросы и запускать уже имеющиеся.

Форматы файлов

Для Excel существует несколько форматов готовых файлов, которые определяют возможности книги:

• .xlsx — стандартная рабочая книга. Пришла на смену бинарному формату .xls с версии 2007 года;
• .xlsm — рабочая книга, позволяющая работать с макросами;
• .xlsb — рабочая книга, использующая бинарный формат, но одновременно и поддерживающая макросы. Обычно используется при работе с большими таблицами;
• .xltm — шаблон-основа для рабочих книг. Имеется поддержка макросов;
• .xlam — надстройка, созданная для добавления дополнительного функционала.

Нужно учитывать, что некоторые формулы и функции из новых версий могут не работать в старых.

Станьте аналитиком данных и получите востребованную специальность

Стандартные операции с ячейками

Активной считается ячейка, которая имеет черную рамку выделения с квадратом в правом нижнем углу. Чтобы вносить данные в ячейку, не обязательно делать по ней дополнительные клики мышью. Вносить данные можно как непосредственно в ячейку, так и через строку формул. При перемещении курсора с помощью клавиш-стрелок активной становится соседняя ячейка, текст в предыдущей сохраняется. Чтобы изменить уже имеющийся текст, необходимо дважды кликнуть по ячейке или внести правки через строку формул.

Активная ячейка A1

При двойном клике по черному квадрату в правом нижнем углу текстовые данные протянутся до нижнего края созданной таблицы. Если выделить диапазон с последовательными значениями (числа, даты, дни недели), они автоматически пересчитаются в сторону увеличения, заполнив столбец до нижнего края таблицы. Те же операции можно проделать и вручную, протянув за квадрат в нижнем правом углу. В этом случае пользователь самостоятельно определяет границы заполнения.

Чтобы выделить несколько ячеек одновременно, нужно зажать клавишу Ctrl и выбрать нужные ячейки или диапазоны ячеек. Ту же самую операцию можно проделать со строками и столбцами, выделяя необходимое их количество.

Excel автоматически суммирует значения в ячейках, показывая результат в правом нижнем углу листа. Также пользователю предлагаются средние значения данных и общее количество заполненных ячеек.

Ширину столбцов и высоту строк можно менять как вручную, передвигая край столбца/строки, так и нажав правую клавишу мыши и выбрав меню «Ширина столбца (высота строки)». По умолчанию значение ширины указывает на количество символов текущего размера шрифта, которые могут разместиться в ячейке, а значение высоты — на максимально возможный размер шрифта для этой строки.

Ячейки можно объединять с помощью соответствующего меню на вкладке «Главная». Всего существует три варианта объединения:

• объединить и поместить в центре;
• объединить по строкам;
• объединить ячейки.

Следует помнить, что при объединении может нарушаться сортировка данных в таблицах, а также могут некорректно считаться формулы.

Работа с формулами Excel

Главная функция программы — возможность подсчета значений с помощью встроенных формул и функций. Среди них есть простые, позволяющие подсчитать сумму в диапазоне, среднее значение в диапазоне, количество заполненных ячеек и т.д. Есть и более сложные, способные вычислять количество дней между разными датами (в т.ч. рабочих), максимальные и минимальные значения в диапазоне. Работа с функциями и формулами также напрямую зависит от формата ячеек. По умолчанию для всех ячеек установлен общий формат, позволяющий работать как с числовыми, так и с текстовыми данными. Но для более сложной работы он может быть заменен на числовой, процентный, дату, денежный, финансовый, дробный и т.д.

Формула вводится либо через строку функций над таблицей, либо в самой ячейке при двойном клике. В начале функции обязательно должен стоять знак «=». После этого при вводе названия функции появляется помощник, который предлагает выбрать функции из списка. Разберем это на примере формулы средней сложности «СЧЕТЕСЛИ».

Пример использования функции СЧЁТЕСЛИ

В скобках после функции будут прописаны условия, по которым формула будет проводить вычислениях. Подсказка указывает на то, что потребуется задать диапазон вычислений и критерий, по которому они будут производиться.

Например, нам необходимо подсчитать количество букв «А» в указанном диапазоне:

Диапазон в рамках функции СЧЁТЕСЛИ

Выделяем диапазон, отмечаем первое условие с помощью знака «;»

Далее выделяем критерий. Его можно прописать вручную, заключив в кавычки; можно выделить ячейку, в которой этот критерий прописан.

Выбор критерия

Закрываем скобку, потому что все условия указаны, и нажимаем Enter.

В ячейке C1 получаем ответ: в шести ячейках нашей таблицы написана буква «А».

Вывод результата в ячейку

В случае ошибки в формулах в ячейке будет прописано «#ДЕЛ/0!». В этом случае необходимо перепроверить формулу на предмет неточностей и на корректность.

Excel может проводить вычисления не только на активном листе. Для этого при вводе формулы через строку функций нужно перейти на нужный лист и указать ячейку, с которой нужно провести расчет. При протягивании формул автоматически подтянутся и адреса всех ячеек. Чтобы этого не произошло и формулы были подсчитаны корректно, можно закрепить нужную ячейку, выделив ее адрес и нажав клавишу F4. Формат адреса будет выглядеть так: $A$1.

Быстрые клавиши в Microsoft Excel

Мы постарались, чтобы данная статья осталась у вас в закладках. Поэтому держите небольшую таблицу на каждый день с некоторыми полезными быстрыми клавишами в Microsoft Excel:

Быстрая клавиша	Описание
Ctrl + N	Создать новую книгу
Ctrl + O	Открыть существующую книгу
Ctrl + S	Сохранить книгу
Ctrl + P	Печать
Ctrl + Z	Отменить последнее действие
Ctrl + Y	Повторить действие
Ctrl + C	Копировать выделенные ячейки
Ctrl + X	Вырезать выделенные ячейки
Ctrl + V	Вставить скопированные/вырезанные ячейки
Ctrl + B	Полужирный текст
Ctrl + I	Курсивный текст
Ctrl + U	Подчеркнутый текст
Ctrl + F	Найти текст в книге
Ctrl + H	Заменить текст в книге
Ctrl + A	Выделить все ячейки в таблице
Ctrl + Shift + «+»	Вставить новую строку сверху текущей
Ctrl + «-«	Вставить новую строку снизу текущей
Ctrl + Space	Выделить всю строку
Shift + Space	Выделить всю колонку
Ctrl + Shift + «!»	Применить форматирование «Число»
Ctrl + Shift + «%»	Применить форматирование «Процент»
Ctrl + Shift + «#»	Применить форматирование «Дата»
Ctrl + Shift + «@»	Применить форматирование «Время»
Ctrl + Shift + «&»	Применить форматирование «Обрамление»
Ctrl + Shift + «_»	Удалить рамку
Alt + Enter	Начать новую строку в ячейке
F2	Режим редактирования ячейки
F4	Повторить последнее действие
F11	Создать новый лист
Ctrl + Tab	Переключение между вкладками листов
Ctrl + PgUp	Перейти на предыдущий лист
Ctrl + PgDn	Перейти на следующий лист
Ctrl + Arrow keys	Перемещение к ячейкам с данными
Ctrl + Shift + Arrow keys	Выделение диапазона ячеек
Ctrl + Home	Перейти к ячейке A1
Ctrl + End	Перейти к последней заполненной ячейке
Ctrl + Shift + Home	Выделить с начала таблицы до активной ячейки
Ctrl + Shift + End	Выделить от активной ячейки до последней заполненной ячейки

Пожалуйста, имейте в виду, что эти быстрые клавиши могут немного различаться в зависимости от версии Excel или операционной системы.

Аналитики влияют на рост бизнеса. Они выясняют, какой товар и в какое время больше покупают. Считают юнит-экономику. Оценивают окупаемость рекламной кампании. Поэтому компании ищут и переманивают таких специалистов.

Топ 20 самых важных формул MS Excel

Microsoft Excel — один из самых популярных инструментов анализа данных в мире. Все больше фирмы используют это программное обеспечение и много людей ежедневно пользуются Excel. Однако, по моему опыту, только немногие из них в полной мере используют возможности, которые может предложить эта программа. Я представляю Вам мой список из топ 20 самых полезных формул Excel, которые каждый должен использовать для повышения эффективности. Функции Excel также очень рекомендуются для вычислений, так как они могут значительно уменьшить количество ошибок. И последнее, но не менее важное: изучение этих функций — прекрасная возможность улучшить Ваши знания Excel и стать уверенным пользователем MS Excel.

Table of Contents

1. Sum

«Sum» — это, наверное, самая простая, но и самая важная функция Exceл. Вы можете использовать эта формула для вычисления суммы диапазона ячеек.

В этом примере первая формула суммирует ячейки от A1 до A10. Вторая суммирует только ячейки A1 и A10. Вы можете попробовать с ним и попытаться использовать разные диапазоны, чтобы привыкнуть к этой функций. Обратите внимание, что вы также можете использовать числа внутри этой функции вместо ссылок.

2. Average

Еще одна очень важная функция. Как следует из названия, эта формула оценивает среднее число из диапазона ячеек. Предположим, что мы имеем диапазон чисел в столбце A.

Это вернет среднее значение ячеек от A1 до A10.

Другая очень необходимая формула. Она относительно простая в использовании, но и очень мощная. Вы можете использовать эту функцию, чтобы проверить, является ли данный оператор истинным или ложным, и чтобы вернуть указанное число.

Здесь формула проверит если продажи в ячейке «A2» более 100 €. Если это заявление верно, формула возвращает «да». Если продажи ниже 100 евро, формула возвращает «нет».

4. Sumif

Sumif — еще одна очень важная формула Excel. Формула будет суммировать числа, если выполняется определенное условие.

Давайте рассмотрим приведенный выше пример. У нас есть торговые агенты, которые регистрируют определенное количество продаж. Мы хотим узнать общее количество продаж для каждого агента. Функция = SUMIF (A1: A20, A2, B1: B20) суммирует значения ячеек B1: B20, которые соответствуют тексту в ячейке A2 (в случае «Ивайло»). Парень зарегистрировал общий объем продаж 325 €. Мы можем использовать формулу для расчета оборота других агентов.

5. Countif

Countif — очень полезная функция, которая работает как sumif. Он суммируется при определенном условии. Разница между sum и count состоит в том, что функция count суммирует количество ячеек, где определенное условие истинно. Напротив, суммирующая функция суммирует значения внутри ячеек.

Формула в приведенном выше примере проверяет, сколько продаж были выше чем 100 евро и возвращает количество продаж. Вы также можете включить более одного критерия, используя countif для этой цели.

PS: Если вы не уверены в синтаксисе функции, вы всегда можете использовать функцию «insert function» в Excel (просто нажмите знак функции в левой части функциональной панели). В следующем примере мы продемонстрируем, как это работает.

6. Counta

Эта формула подсчитывает количество ячеек определенного диапазона, которые не являются пустыми. Это очень полезно, когда у вас есть тысячи строк, и вы хотите знать, где все это кончается.

Это даст вам количество строк, которые содержат значение, текст или любой другой символ.

7. Vlookup

Вероятно, самая мощная функция Excel. Очень полезно, когда вам приходится иметь дело с более чем одной таблицей, разными листами или даже другого файла. Vlookup позволяет вам извлекать данные из одной таблицы в другую.

Продолжим этот пример, добавив еще один столбец «А» с номерами клиентов.

Обратите внимание, что есть информация о продажах и номерах клиентов, но имена клиентов отсутствуют. Однако у нас есть еще один лист Excel (Sheet2) с именами клиентов и номерами клиентов.

Вместо того, чтобы тратить свое время, чтобы посмотреть с Sheet1 на Sheet2 и искать совпадение вручную для каждой отдельной строки, мы можем использовать следующую формулу:

Эта формула ищет значение в ячейке A2 (112 в нашем случае) в листе2, столбцы A & B (знаки «$» означают, что мы всегда хотим иметь постоянный диапазон поиска). Затем функция возвращает значение из второго столбца. Последняя часть формулы, мы пишем 0. Таким образом, мы делаем Excel не искать совпадение в порядке чисел.

Теперь попробуйте создать собственную формулу vlookup, используя опцию insert function.

В нашем случае это выглядит так:

8. Left, Right, Mid

Left, right и mid функции — очень важные функции, которые позволяют извлекать определенное количество символов из строки

В нашем примере с формулой left мы берем первый символ с левой стороны. С формулой right мы можем сделать то же самое, но с символами справа налево. Имейте в виду, что пустое пространство также является символом.

9. Trim

Еще одна исключительная функция MS Excel. Функция trim удаляет пустые пространства после слов, когда их больше одного. Это может случиться довольно часто в Excel. Возможно, что информация извлекается из разных баз данных и заполняется ненужными пустым пространством между словами. Это может быть огромной проблемой, потому что ваши формули Excel могут не работать. Чтобы избавиться от раздражающих пустых пространств, мы используем формулу trim. = TRIM(A1)

В этом примере формула удалит лишние пробелы, если она найдет несколько.

10. Concatenate

Это удобная функция Excel, которая помогает, когда мы хотим объединить ячейки. Здесь мы хотим объединить ячейки A1, A2 и A3. Это можно сделать с помощью следующей функции:

Таким образом, у нас не будет пробелов между словами. Чтобы добавить пробелы между «I» и «love», нам нужно добавить пробел. Это можно решить, добавив кавычки внутри функции:

PS: Вы также можете объединить ячейки, просто добавив:

11. Len

Еще одна прекрасная формула, которая может применяться во многих ситуациях. Она широко используется для построения более сложных формул.

Эта функция будет подсчитывать количество символов в ячейке.

12. Max

Функция max возвращает наибольшее значение из диапазона.

Эта формула будет выглядеть в ячейках A2: A8, и тогда она извлечет самое высокое значение из этого диапазона.

13. Min

Функция min функционирует аналогично функции max, однако она дает противоположный результат. Он извлекает самое низкое значение из диапазона данных.

Формула из приведенного выше примера вернет наименьшее число из выбранных ячеек в столбце «A».

14. Days

Это функция, когда вам нужно вычислить количество дней между двумя днями в электронной таблице.

Формула вернет количество прошедших дней.

15. Networkdays

Иногда вы хотите работать только с рабочими днями. Эта функция работает очень похоже на приведенный выше пример. Разница в том, что он возвращает рабочие дни, прошедшие, за исключением выходных.

16. SQRT

Это довольно просто и довольно полезно одновременно. Как следует из названия, формула дает квадратный корень из числа

Это вернет квадратный корень из 1444 (38). Вы также можете использовать ссылку на ячейку здесь, как и во многих других формулах Excel.

17. Now

Иногда вам нужно знать текущую дату, когда вы открываете электронную таблицу Excel.

Это вернет текущую дату. Не забудьте установить формат на сегодняшний день. Вы можете попробовать с ним столько, сколько хотите. Вы можете, например, добавлять или вычитать дни. = Now () -14 вернет дату до двух недель.

18. Round

Функция Round очень полезна, когда вам приходится работать с округленными номерами. Excel может отображать числа для определенного символа, но сохраняет исходный номер и использует его для вычислений. Однако в некоторых случаях это может быть проблемой, и нам нужно работать только с округленными номерами. В таких случаях функция ROUND обращается к нашей помощи. = ROUND(B1,2)

В этом примере число округляется до второго десятичного знака.

19.Roundup

Это вариация формулы round, которая может очень помочь в некоторых случаях. Онa округляет значение до ближайшего целого числа. Это очень полезно в сочетании с другими формулами Excel. Давайте посмотрим на этот пример:

Здесь мы хотим найти в каком квартале текущего года находится текущую дату. Секрет этой формулы — простая математика. Функция принимает номер месяца, например, январь равен 1, февраль равен 2 и т. Д., А затем делит его на 3, округляя до ближайшего большего числа.

20. Rounddown

Rounddown работает аналогично Roundup, однако она округляет число до ближайшего целого числа

Excel имеет много полезных функций. В этой статье я попытался представить наиболее важные формулы в MS Excel наилучшим образом. Я считаю, что эти функции являются очень хорошей базой для тех из вас, кто хочет развивать свои умения Excel. Посредством освоения этих формул вы можете легко справляться с повседневными ситуациями, которые могут возникать при работе с Excel, и это действительно хорошая возможность для дальнейшего развития ваших навыков.

Полезные трюки при работе в Excel ⁠ ⁠

Всем привет. Это моя первая статья на Пикабу, поэтому позвольте сначала представиться. Я являюсь преподавателем Microsoft Excel. Теперь, когда с формальностями покончено, можно перейти к основному.

Сомнения перед написанием

Я довольно часто читаю разный тематический материал на Пикабу, и меня восхищают большинство авторов и статей. Статьи восхищают, в первую очередь, своей интересностью (есть такое слово вообще?) и полезностью. Именно поэтому у меня были большие сомнения, а стоит ли вообще лезть со своими очередными «простыми, но полезными штуками при работе в Excel». Да и кому вообще ты со своим Excel нужен?! Тем более, что беглый поиск по сайту не выдал ни одной подобной статьи. И та часть меня, которая отвечает за неуверенность, сразу подметила, что раз нет, значит, оно никому не нужно. А может, просто плохо искал. И да, я отдаю себе отчёт в том, что подобного материала довольно много на просторах интернета. И всё-таки, принцип «лучше сделать и жалеть, чем не сделать вовсе» возобладал.

Почему я посчитал, что это будет полезно

Занимаясь преподаванием этой замечательной программы (а я и правда считаю её чудесной и, можно сказать, влюблён в неё), я довольно часто подмечал, что именно мелочи оказывают самое большое впечатление на слушателей. Рассказываешь про сочетание функций ИНДЕКС(ПОИСКПОЗ), какое оно крутое, позволяет двумерный поиск по таблице осуществлять и много чего ещё делать, все сидят, понимающе кивают. Потом в процессе показываешь какую-нибудь мелочь, вроде той, что листы можно копировать, зажав Ctrl и мышкой перетащив лист чуть правее/левее, аудитория сразу оживает: «Ну всё, не зря время потратили». Именно про такие вот простые приёмы я и хотел бы вам рассказать (про первый так уже рассказал).

Небольшое пояснение

Путь до той или иной команды обычно описывается следующим образом: название вкладки — потом группа команд — сама команда:

Если у вас ноутбук, то функциональные клавиши могут работать только при одновременном нажатии на кнопку Fn+F1-12 (есть такие ноутбуки, в которых и этот способ не работает, тут надо уже по модели ноута смотреть).

Вообще, почти каждая функциональная клавиша отвечает за какое-то действие. Но я остановлюсь на одной, а именно — F4. И нет, речь пойдёт не про то, что этой кнопкой в Excel мы можем менять тип ссылки для ячейки.

F4 — повтор последнего выполненного пользователем действия (если нажимать её не тогда, когда курсор находится в строке формул)

Например, вам нужно для нескольких несмежных столбцов установить определённую ширину. Вместо того, чтобы каждый раз выбирать столбец, потом переходить на вкладку Главная — Ячейки — Формат — Ширина столбца. Можно один раз проделать эту операцию, потом просто выделить следующий столбец и нажать F4. И такой фокус можно проделывать со многими операциями, будь то закраска ячеек, строк, столбцов, части графика на диаграмме или банальная вставка столбцов (да, столбец можно вставлять сочетанием Ctrl + «+», но ведь это две кнопки, а F4 — одна).

Представления

Представления, с моей точки зрения, являются одним из самых недооценённых инструментов в Excel. Предположим, у вас есть таблица, в которой вы часто фильтруете несколько столбцов по разным критериям: отдел, пол и город.

И вот вы каждый раз раскрываете фильтр, устанавливаете нужные критерии, просматриваете данные, потом раскрываете фильтр, следующий критерий, потом фильтр. Думаю, суть вы уловили. «Но всё меняется, когда приходят они — представления!» © Установив нужные критерии, переходим на вкладку Вид — Режимы просмотра книги — нажимаем Представления:

Далее всё интуитивно (куда же без интуиции в этой прекрасной программе) понятно. Жмёшь «Добавить», обзываешь представление так, как тебе угодно — Ок. Здесь же, в окне добавления представления, мы можем узнать, а что, собственно, Excel сохраняет. А сохраняет он параметры печати, результаты фильтрации, скрытые строки и столбцы. Создав под каждый набор фильтров, строк и столбцов представление, потом лёгким и непринуждённым нажатием на эту команду ты будешь менять свою таблицу в мгновение ока. Это не совсем удобно? Что же, согласен. Давайте сделаем ещё удобнее и добавим представления на панель быстрого доступа. Для этого раскроем настройку панели быстрого доступа — Другие команды:

В открывшемся окне в поле «Выбрать команды из:» выбираем «Все команды». Потом находим «Представления» — Добавить:

Кстати, так можно добавить на панель быстрого абсолютно любую команду.

Теперь у нас появился выпадающий список со всеми нашими сохранёнными представлениями. Через это же окно можно и новые представления создавать. Просто пишешь в нём название, нажимаешь Enter — готово.

ПРЕДУПРЕЖДЕНИЕ!

Представления не работают в книгах, в которых есть «умные» таблицы (таблицы, которые мы создаём через вкладку Главная — Стили — Форматировать как таблицу).

После создания представления не нужно перемещать столбцы/менять их местами, иначе представление прекратит работать.

Два окна одной книги.

Прежде, чем кидать в меня различные предметы с криками «мало того, что про какой-то Excel пишет, так сейчас ещё будет рассказывать, как в двух окнах работать, смерд?!» позвольте пояснить. Речь пойдёт о том, как работать в двух окнах с ОДНОЙ книгой. Давайте смоделируем ситуацию. Есть у тебя два монитора (если ещё нет, обязательно заводи второй, пускай небольшой, но чтобы был), один файл Excel с несколькими листами внутри. Тебе нужно из одной таблицы перенести данные в другую (сравнить их, связать формулами и так далее). Что ты делаешь? Правильно, бесконечно долго и уныло переключаешься между листами. Второй монитор тем временем грустно за этим наблюдает. Но можно сделать этот процесс более удобным и быстрым. Прошу любить и жаловать, вкладка Вид — Окно — Новое окно:

Нажав на эту команду, мы получим ту же самую книгу, но в другом рабочем окне. Название файла будет немного изменено на «Мой файл:1» и «Мой файл:2». А дальше уже дело за тобой. Располагай окна так, как тебе удобно (на одном мониторе, на разных), копируй данные, создавай связи, формулы — в общем, работай. Но делать ты это уже будешь быстрее и удобнее. Все изменения, которые мы вносим в любое из клонированных окон, появляются сразу во всех связанных окнах. Главное, не забыть нажать «Сохранить» хоть в каком-нибудь окне.

Специальная вставка (пропускать пустые ячейки)

Вообще, про специальную вставку в Excel можно написать отдельную статью, наверное. Инструмент во многих случаях просто незаменимый. Но в рамках данной статьи я расскажу только про одну возможность. Представим, что есть две таблицы:

Нужно перенести данные из крайнего правого столбца второй таблицы (столбец Р) в крайний столбец первой таблицы (столбец F) таким образом, чтобы существующие номера остались. Обычным копированием-вставкой сделать это не получится, так как в столбце Р есть пустые ячейки, которые заменят собой существующие номера в столбце F. И тут на сцену выходит специальная вставка. Выделяем диапазон из столбца Р, копируем. Далее выбираем ячейку, начиная с которой нужно вставить данные (в нашем случае это F2), и либо щёлкаем правую кнопку мыши — в контекстном меню ищем «Специальная вставка», либо нажимаем сочетание клавиш Ctrl+Alt+V. Попадаем в такое окно:

Ставим галочку рядом с «пропускать пустые ячейки» — Ок. Профит!

Хочу отметить, что большинство приёмов, которые я здесь описал, не начнут прям с ходу экономить вам часы рабочего времени. Но если постепенно приучить себя их использовать, вспоминать о них, то скорость работы будет неуклонно возрастать. На этом, пожалуй, всё. Спасибо всем, кто уделил своё внимание и драгоценное время чтению поста. Надеюсь, что кому-то это было полезно. Вообще, если хотя бы одному человеку данный материал поможет в работе, я уже буду считать это успехом.

P.S. Если статья покажется интересной и полезной, то на примете есть ещё несколько приёмов, про которые могу рассказать.

Друзья, создал на Ютубе свой канал. Пока только видео с первой статьёй. В ближайшие дни опубликую вторую часть. Полезные трюки и приёмы при работе в Microsoft Excel — YouTube

Самое полезное в экселе это надстройки и макросы. Если хорошо поискать в сети, то можно найти очень много полезного и бесплатно. Чем более точно озвучить свою хотелку, тем лучше. Очень много людей ищут, и немногие помогают с написанием макросов и формул. Но первоочередное, что нужно всем — надстройка plex. Её функции очень удобны, и главное просты и понятны. К примеру — вставить текст в начале строки во всех ячейках, или в конце. Удалить все непечатные символы и переносы строки. Открыть все ссылки одной кнопкой. Показать скрытые листы у книг и прочее. Я работаю контент менеджером, и именно эта надстройка упростила мне работу, и серьёзно ускорила работу с таблицами.

Про представления не знал, спасибо!

7 интересных приёмов при работе со сводной таблицей⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир 🙂

1 — Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.

Поля, которые находятся в области значений, нельзя фильтровать привычным для нас образом. Там просто нет фильтров, и установить их обычным способом нельзя, команда не активна:

Но если нельзя, но очень хочется, то можно.

Решение: нужно поставить курсор в соседнюю ячейку справа от заголовка поля и установить фильтр. Всё, готово. Теперь можно фильтровать значения, как в обычной таблице.

2 — Изменение порядка элементов в списке.

Вообще, есть несколько способов менять порядок элементов в сводной. Я хочу рассказать вам про наиболее удобный. Предположим, мы хотим, чтобы список менеджеров начинался с Чайниковой:

Решение: выбираем ячейку с Баранкиным и вероломно начинаем печатать фамилию Чайниковой. Целиком печатать не нужно, программа сама предложит готовый вариант. Нажимаем Enter. Вуаля! Теперь Чайникова на вершине нашего списка:

Ещё один способ: это обычное копирование и вставка. Выбираем ячейку с Чайниковой, копируем её. Далее выбираем ячейку с Баранкиным (место, куда хотим переместить наше значение), и вставляем.

3 — Использование собственных списков для сортировки.

Прошлый способ хорош, если нужно сделать это однократно. Но если у нас уже есть какой-то устоявшийся порядок, он вот совсем не по алфавиту, а бездушный Excel всегда всё сортирует именно по алфавиту?

Решение: многие знают, что если мы напишем в ячейке «январь» («февраль», «март» и т.д.), потом протащим ячейку за правый нижний угол вниз, то всемогущий Excel автоматически заполнит следующие ячейки месяцами по порядку. Но не только лишь все знают, что такую штуку можно сделать и по своему списку.
Шаг 1. Создаём свой список (с блэкджеком и порядком). Где-нибудь на листе Excel создаём список в том порядке, в котором нам нужно. Далее заходим в Файл — Параметры — Дополнительно — Изменить списки:

В поле «Импорт списка из ячеек:» указываем диапазон, в котором находится наш список, далее нажимаем Импорт. Всё, подготовительная работа завершена:

Шаг 2. Для сортировки данных используем наш список. Теперь можно применить сортировку от А до Я в столбце с менеджерами. И вот ведь неожиданность, сортировка будет не по алфавиту, а согласно нашему списку:

К сожалению, данный приём имеет ограничения в части обновления списка. Если добавится новый сотрудник, то при обновлении сводной сотрудник будет в самом конце списка независимо от того, какая у него там первая буква фамилии. Дальше либо добавлять его в список, либо вручную перетаскивать в нужное место.
Если нужно будет вернуть стандартную православную сортировку по алфавиту для данного списка, необходимо раскрыть значок фильтра, выбрать Дополнительные параметры сортировки — по возрастанию — Дополнительно — снять галочку с «Автоматическая сортировка. » — в поле ниже выбрать «Без вычислений»:

Кстати, созданные нами списки будут работать так же, как встроенные. То есть если вписать в ячейку Баранкина, протащить вниз, в следующих ячейках будут фамилии по списку.

4 — Отображение пустых строк.

При разбивке какого-то показателя на несколько групп/категорий в сводных таблицах не отображаются данные, которых нет в источнике. В целом, это логично. Но согласитесь, что если мы, к примеру, хотим проанализировать показатель по сотрудникам по месяцам, то вот такая картина будет только нас запутывать:

Месяцы идут по порядку, да, но у кого-то пропущен июль, у кого-то февраль и октябрь.

Решение — щёлкаем правой кнопкой мыши по любой ячейке того поля, в котором хотим отображать ВСЕ элементы (в том числе и пустые) — далее Параметры поля. — вкладка Разметка и печать — ставим галочку Отображать пустые элементы.

У этого приёма есть один побочный эффект — если речь идёт про работу с датами, то отображаются даты до самого раннего числа и после него (видно на гифке). Избавиться от этого довольно просто: нужно убрать их через фильтр, сняв галочки.

5 — Топ-N сотрудников/месяцев/товаров и так далее.

Вообще, отображение топ-3/5/10 сотрудников или любого другого — это не привилегия фильтра в сводной таблице. Работая с фильтрами в обычных таблицах, у нас есть точно такая же возможность. Но про эту возможность, исходя из моего хоть и небогатого, но всё же опыта, мало кто знает. То ли нафиг никому не нужно, то ли просто пользователи не знают. Как по мне, вещь полезная. Особенно с учётом того, что в сводной таблице фильтр будет обновляться вместе с внесением изменений в источник (и обновления самой сводной, конечно же), чего в обычных таблицах, увы, нет.

Решение — всё просто. Раскрываем фильтр по тому полю сводной, по которому хотим выделить топ-N чего-нибудь — далее Фильтр по значению — Первые 10 — указываем нужные нам настройки.

Правда, без побочных эффектов тут, к сожалению, тоже не обошлось. Если у какого-то сотрудника не будет хватать данных для того, чтобы отобразить топ-3, то по этому сотруднику (или по тому, по чему вы ищете топ-N) будут вообще все данные. Поведение более чем странное, но как есть 🙁

6 — Повтор подписей и промежуточные итоги для отдельных полей, а не для всей таблицы.

Когда у нас с вами в сводной таблице в области строк больше двух полей, то сразу возникает вопрос про промежуточные итоги. И если идти стандартным путём, то есть через вкладку Конструктор — группа Макет — Промежуточные итоги, то там мы можем играться с промежуточными итогами для всей таблицы, а не для конкретного поля. Порой, это неудобно. Как в примере ниже:

Я бы хотел оставить промежуточный итог по сотруднику, а вот по месяцу убрать.
И сразу второй момент. Когда меняем макет сводной на табличный или форму структуры, то возникает вопрос с повторением подписей элементов, чтобы не было пустых ячеек. Если перейти на вкладку Конструктор — группа Макет — раскрыть Макет отчёта и выбрать Повторять все подписи элементов, то это сработает для всей таблицы. А я, например, хочу только по месяцам подписи.

Решение — великая и могучая правая кнопка мыши. Для того, чтобы добавить/убрать промежуточные итоги у определённого поля сводной таблицы, щёлкаем правой кнопкой мыши по любой ячейке этого поля и в контекстном меню находим Промежуточный итог.
Что касается подписей элементов, то щёлкаем правой кнопкой мыши по тому полю, где хотим/не хотим повторять подписи — далее Параметры поля. — на вкладке Разметка и печать находим галочку Повторять все подписи элементов.

7 — Объединение ячеек и выравнивание подписи по центру.

А если вам вообще не нужны все эти повторения, вы хотите объединённые ячейки с надписью по центру без всех этих плюсов/минусов, то и тут сводная сдюжит.

Решение — для объединения ячеек и выравнивания подписи по центру щёлкаем по любой ячейке сводной таблицы правой кнопкой мыши (данная настройка будет работать для всех полей сводной, для какого-то конкретного поля настроить возможности нет) — в контекстном меню находим Параметры сводной таблицы — вкладка Макет и формат — находим галочку Объединить и выровнять по центру ячейки с подписями.
А чтобы убрать плюсы/минусы, нужно выбрать любую ячейку сводной — далее вкладка Анализ сводной таблицы (в предыдущих версиях просто Анализ) — группа Показать — иконка Кнопки.

Заключение. На этом, пожалуй, всё. Вообще, всяких разных интересных штук при работе со сводной таблицей огромное множество. Про все и не напишешь. Делитесь в комментариях своими интересными приёмами. Думаю, многим будет интересно и полезно узнать что-то новое (мне так точно). Спасибо всем огромное, кто потратил своё драгоценное время и внимание на прочтение данного поста. Надеюсь, что-то из описанного было для вас полезным и вы сможете использовать это при работе со сводными таблицами.

Преобразование чисел, сохранённых как текст, в нормальный числовой формат в Excel⁠ ⁠

Друзья, всем привет. В комментариях, да и на занятиях, довольно часто поднимается тема преобразования чисел, сохранённых как текст, в рабочий числовой формат, поэтому решил про это написать. Как обычно, здесь не будет никаких откровений и чего-то нового. Так или иначе, всё то, о чём напишу, уже давно есть в интернете. Просто решил собрать всё в одном месте. Может, кому-то будет полезно (я на это очень надеюсь).

Небольшое вступление. Чего рассматривать не будем, и почему такое вообще происходит.

Сразу оговорюсь, что не буду здесь рассматривать случаи, когда в ячейках кромешный ад и вакханалия вроде «10 руб», «33 попугая» и так далее. Хотя один способ может и с такими ячейками помочь. Будем разбирать православные ячейки, в которых только число, но, по тем или иным причинам, оно сохранено как текст.

Причины могут быть разными, но чаще всего такая ситуация случается, когда мы импортируем данные в Excel из какой-нибудь другой корпоративной программы или копируем из интернета/другого источника. Выгрузил данные в Excel, пытаешься что-то просуммировать, а на выходе получаешь шиш с маслом:

Excel любезно даёт понять, что число сохранено как текст, добавляя в такие ячейки зелёный треугольник в левый верхний угол (не касается дат, сохранённых как текст, такие ячейки никак не выделяются). Интересный момент: если суммировать ячейки вручную, то результат будет нормальным.

Вся загвоздка в том, что если пойти путём обычной смены формата на числовой (Главная — Число — в выпадающем списке выбрать Числовой), результата это не даст. Придётся взять бубен и немного потанцевать с ним.

И так, что же можно сделать.

Важно! Прежде чем пробовать какой-то из способов, убедитесь, что у вас в принципе правильные разделители указаны. Если система использует запятую как разделитель групп разрядов, а у вас точки везде, нужно просто через поиск и замену поменять точку на запятую (и наоборот, если в качестве разделителя система использует точку).

Способ 1. Использовать инструмент «Текст по столбцам» (text to columns) . Выделяем все «проклятые» ячейки, идём на вкладку Данные — Работа с данными — Текст по столбцам, и на самом первом шаге нажимаем Готово.

Способ 2. Замена разделителя на аналогичный. Честно скажу, когда рассказываю про этот способ, почти все говорят «Да какого. Да почему!? Да что с этой программой не так?!». Да чего уж, признаюсь, что без улыбки на лице про него сам не могу рассказывать. Если в ячейках дробные числа, то преобразовать можно с помощью банальной замены запятой на. Барабанная дробь. ЗАПЯТУЮ! Выделяем данные, запускаем поиск и замену (CTRL+ H), ищем запятую, меняем на запятую. Этот способ также подходит для дат, которые сохранены как текст, только вместо запятой заменять нужно разделитель, который указан в ячейках (точка, слэш, дефис).

Способ 3. С помощью специальной вставки. Довольно интересный способ, так как знакомит нас со специальной вставкой — очень полезным инструментом. Смысл в том, что для преобразования текстовых чисел в нормальные числа с ними нужно произвести какое-то математическое действие. Но нужна такая операция, которая не меняет самого числа, а это, например, умножение на единицу (есть более экзотические, прибавить или вычесть ноль, но их рассматривать не будем). Алгоритм следующий:

В произвольную ячейку пишем единицу (1)

Копируем эту ячейку (Ctrl + C)

Выделяем диапазон с «кривыми» числами

По любой выделенной ячейке щёлкаем правой кнопкой мыши (либо Ctrl + Alt + V)

В контекстном меню выбираем Специальная вставка (Paste special)

В следующем окне выбираем «Умножить». Если в таблице есть какое-то оформление, то ещё желательно выбрать «Значения», чтобы это самое оформление не слетело

Способ 4. Функция ЗНАЧЕН (VALUE). Если планируете делать некий шаблон, в который будете копировать текстовые числа, а на выходе получать нормальные, то можно воспользоваться функцией, которая как раз и занимается преобразованием. Если работаете с датами, то нужна функция ДАТАЗНАЧ (DATEVALUE).

Способ 5. Бинарное отрицание. Хотите быть не как все? Хотите, чтобы коллеги подходили к вам с вопросом «А что это такое тут у тебя формуле?». Тогда этот способ для вас! Бинарное отрицание, если рассматривать его в контексте нашего вопроса, умножает число на -1, а потом ещё раз на -1. То есть мы производим математическую операцию, которая не меняет самого числа. Нужно просто перед ссылкой на ячейку поставить два знака минус (-). Вполне можно использовать при создании шаблона вместо функции.

Способ 6. Excel спешит на помощь. Вообще, если нажать на смарт-тэг (знак «дорожные работы») с ошибкой, то программа сама предложит преобразовать текст в число:

То есть можно выделить диапазон с такими вот ячейками, потом нажать на смарт-тэг, выбрать «Преобразовать в число», и всё сработает как надо. Но должен предупредить, что с большим количеством ячеек способ может работать довольно долго. Особенно если в книге много всего другого (формулы, листы, связи и т.д.).

Немного другая история. Иногда, когда выгружаем данные откуда-то в Excel, в числовых ячейках попадается пробел, как разделитель групп разрядов. И в большинстве случаев все способы, про которые я писал выше, справляются с преобразованием. Но порой что-то идёт не так. И ничего не срабатывает. Отчаявшись, ты пытаешься заменить пробел на пусто, но Excel, подлец, пишет, что пробелов в ячейках не найдено. Но как, вот же он, бездушная ты слепая программа:

К своему стыду (ладно, вру, мне не стыдно), я не погружался прям глубоко в то, почему так происходит. Для себя я придумал версию, что пробелы бывают разные. Так вот, чтобы разобраться с таким недоразумением, нужно скопировать пробел из самой ячейки, выделить ячейки, которые хотим преобразовать, потом открыть окно поиска и замены, вставить скопированный пробел в поле «Найти», а заменить на пусто.

Всесильный Power Query.

Долго думал, стоит ли включать сюда этот способ, и всё-таки решил включить. Часто в комментариях пишут, что а вот это можно с помощью PQ сделать, а вот то вообще на раз-два. И ведь это правда. PQ — это безумно крутая штука. Почему боюсь про него писать? По одной простой причине: более менее интерфейс PQ устаканился, начиная с 2019 версии. Для 2010 и 2013 вообще надо отдельно скачивать и устанавливать (да, это просто и занимает всего пару минут, но всё же). В 2016 версии сразу из коробки идёт, но выглядит чуть иначе. И вот я сейчас покажу, как это делается, а кто-то потом напишет, что у него этого нет, а это по-другому выглядит, и вообще автор — кАзёл. И всё же 🙂 Собрал все случаи, про которые писал. Дальше:

Желательно преобразовать таблицу в «умную» (не сделаете сами, всё равно потом Excel это сделает за вас)

Далее вкладка Данные — Получить и преобразовать данные — Из таблицы/диапазона

Если настройки PQ не меняли, то автоматически будет применён шаг «Изменённый тип», который всё и сделает

В PQ на вкладке Главная нажимаем Закрыть и загрузить

Получаем на новом листе «умную» таблицу с правильными форматами.

На этом, пожалуй, простыню свою завершу. Возможно, я рассказал не про все способы борьбы с текстовыми числами/датами. В комментариях делитесь своими, с удовольствием про них почитаю. Если спросите, зачем так много способов, то ответ будет так себе. Для разнообразия, конечно 🙂 Когда какой использовать — дело вкуса, привычки и исходных данных. В большинстве случаев все способы взаимозаменяемы.

Как всегда, всем огромное спасибо, кто потратил своё драгоценное время и внимание на прочтение данного материала. Надеюсь, было полезно и что-то из статьи поможет вам в работе.

Excel вечен⁠ ⁠

Диаграмма «спагетти» в Excel⁠ ⁠

Ребятушки, подскажите, можно ли сделать в эксель диаграмму спагетти, типа такой, которая на рисунке?

Нужно отобразить передвижение из одного кабинета в другой

5 вещей, которые НЕ НУЖНО делать в Excel (и как делать нужно). Часть 1⁠ ⁠

Друзья, всем привет.

Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.

Важное примечание!

Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы «с нуля».

1 — Объединение ячеек.

Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) — правая кнопка мыши — Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание — по горизонтали — по центру выделения:

К остальным заголовкам можно уже применить «Формат по образцу» (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 — «Правильная» таблица

Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто — не делайте трёхмерные таблицы. Плоская таблица — вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 — «Правильные» даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить «29 марта 2023 г.». Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что «тупой иксель» не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

«-» — дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 — Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я — молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная — Стили — Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку «Таблица с заголовками» — Ок. Всего 4 (. ) нажатия на кнопку мыши и готово. В этих наших интернетах это называется «умная» таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. «Умная» таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 — Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но.

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим «1». Выделяем весь диапазон, где хотим что-то закрашивать. Главная — Стили — Условное форматирование — Правила выделения ячеек — Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке «Заливка» выбираем нужные нам цвета. А вот самое интересное происходит на вкладке «Число». Выбираем «(все форматы)», в поле «Тип» прописываем следующий формат «;;;» (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет «1», но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с «умной» таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Прошу помочь с Excel (функция впр)⁠ ⁠

Дано:
Таблица с данными, уникальный ключ данных по строкам — текст (артикул 3 буквы).
В этой же таблице есть числа, сохранённые как текст (Инн, рс и тп).

Надо функцией впр найти по артикулу нужную строку и вставить как текст данные из столбцов (Инн рс и тп).

Текстовые ячейки впр спокойно сцепляет (через функцию сцепить), а вот с числами как текст ни в какую работать не хочет.

Upd. Утро вечера мудренее.
Прописывая условия функции впр задавала размер таблицы меньше (столбцы), чем надо было.
Проблема решена, спасибо 🙌

Извлечение текста в Excel. Страшные, но иногда такие полезные текстовые функции⁠ ⁠

Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному — наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) — подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;» «;»»)) — подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;» «;»*»;E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

ПСТР (MID) — как много в этом слове. А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 — понятно, ячейка с текстом.

D2+1 — начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 — количество извлекаемых символов. Порядковый номер звёздочки — порядковый номер первого пробела — 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

Вот и решение задачи. Вообще, моё мнение, такие штуки в любом случае нужно сначала решать пошагово, а потом только впихивать всё в одну ячейку. Но тут уж каждый сам для себя решает.

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в «Должно быть»:

Здесь уже расписывать не буду, а то ещё одна «простыня» получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать — это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Мгновенное заполнение в Excel — магия в чистом виде⁠ ⁠

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться — https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента «Текст по столбцам». Правда, потом придётся от символа «№» ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter — PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора — Enter — Ctrl + E — наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.

Ух ты! И так будет работать всегда?! Строго говоря — нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая «красота». Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не «убегайте» далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.

Извлечение только чисел из столбца

Если нам из «красивого» столбца, в котором есть значения вроде «123руб», «55 рублей» и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде «Имя Ф.» В первой ячейке вводим образец — Enter — Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец — Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке — Enter — Ctrl + E:

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь — Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале — (36) Андрей Митрохин — YouTube

Таблица подсчета розеток/выключателей/рамок. ⁠ ⁠

Когда я работал в магазине электротоваров регулярно приходилось считать ЭУИ и рамки к ним по зарисовкам заказчиков или их работников, тогда я это делал на бумаге и неплохо набил на этом руку. Но у некоторых продавцов консультантов это выходит не слишком быстро и качественно. Для автоматизации процесса я решил создать таблицу в google, а затем перенес ее в Excel (последний мне нравится больше). Таблицей я намерен поделиться ссылки будут ниже, а пока краткое описание:

Это страница «Сводка» первоначально ее надо заполнить под себя и сохранить как шаблон:
— Наименования всех типов ЭУИ какие у вас могут быть (если не достаточно того, что ввел я)
— Цвета механизмов (или например серия + цвет, как удобнее будет)
— Цвета рамок (аналогично механизмам)

Потом переходим на страницу «Ввод данных»
При добавлении новой строки указываете комнату
цвет механизма, цвет рамки и наполнение постов выбираете из выпадающего списка (подтянутся варианты со страницы «Сводка»), когда вы выбираете механизм для поста — ячейка окрашивается, считая количество постов в рамке

После заполнения страницы «Ввод данных», возвращаемся на «Сводка»
При выборе нужного цвета в крайней правой таблице («Текущий цвет») в списке ЭУИ и рамок останется только то количество, которое соответствует выбранным цветам.
Кроме того, общее количество механизмов и постов в рамках и количество установочных коробок

ЗЫ Отдельное спасибо @XaXa3Pa3a

Если б мишки были пчёлами… условия в Excel⁠ ⁠

Решил попробовать формат коротких постов по Excel

Дети, сегодня я расскажу вам о такой удобной функции в Excel как ЕСЛИ.

В общем виде выглядит так:

ЕСЛИ (условие; результат если условие верное; результат если условие не верное)

Я буду писать формулу, а на картинке будет результат этой формулы в 5 столбце

При множественных условиях иногда удобно применять функции И, и ИЛИ, которые позволяют соединять несколько условий

=ЕСЛИ(И(A2=»овощи»;D2>50);»дорогой овощ»;»либо не дорогой, либо не овощ»)

Не забываем, что можно комбинировать несколько ЕСЛИ Главное не запутаться в скобках и точках с запятыми.

=ЕСЛИ(A2=»фрукты»;ЕСЛИ(D2>100;»дорогой фрукт»;»не дорогой»);»не фрукт»)

Как видим результат получается довольно гибкий.
Также напомню, что есть операторы сравнения <,>,<=,>=,=,<>.

А на этом сегодня все, пойду дальше писать про макросы…

Ответ на пост «Помощь в формировании Таблицы на основе 2 таблиц»⁠ ⁠

Если по-простому, формулами, то так:

Таблицу 3 переделываем в другой вид:

Это будет состав детали, даты будем добавлять вправо.

(Таблица соответствия количества материала количеству детали)

Таблица 2 тогда будет выглядеть так:

(тупо суммируем кол-во материалов на дату)

1. Важно чтобы во всех 3-х таблицах даты были синхронизированы, чтобы можно было протягивать формулы не опасаясь что-то поломать.

2. Важно следить чтобы в составе (таблица 3) не было дубликатов пар — [деталь — материал] (желтые столбцы), иначе объем задваиваться будет.

Сумма итогов, чтобы её не переписывать каждый раз (в ячейке H2) — может выглядеть так:

Ответ на пост «Как сохранить список запрещенных в России в файл?»⁠ ⁠

Бро, на данном сайте есть таблица.
вот тебе лайфхак

@1g0rbm ты круто всё описал и, уверен, твой способ рабочий,
но зачем усложнять когда, можно не усложнять и пойти попить кофеек?)

1 минута.
и пример из множества страниц в ответ на некоторые комментарии
ссылка на пост

Ответ на пост «Excel, делим покупки на несколько человек»⁠ ⁠

И так господа, перед Вами методичка по распиливанию затрат после попоек.

Разработана она мною, но на копирайт не претендую.

Ситуация проста: Пятница, Вы собрались пошуметь. Заказаны «стартовые» места, собрана компания друзей, сняты ограничения с карточек, побрились, подмылись, вызвали такси и поехали!

Сразу рекомендация: договоритесь как будете делить счёт. Вполне возможно, что в вашей компании тот, кому завтра платить за ипотеку. И он вообще в принципе не собирается много тратить.

Помните, между друзьями не должно быть обид или споров по денежным вопросам. Если у вас в компании есть друг, не желающий сопровождать общий счёт, объясните ему, что он сам платит за себя и главное платит СРАЗУ! Принесли счёт в ресторане, он сам запоминает и считает сумму напитого и наеденного и СРАЗУ кидает на стол деньги, а ещё фиксит это, например в общем чате. Кстати, общий чат — это тема. История с карточками — это хорошо, но налом платить в некоторых местах безопасней. Кидайте сразу в общий чат, кто, сколько и главное, за что заплатил. Поверьте, с утра вам будет не до воспоминаний, да и не всё вспомнить. Не забывайте о конспирации! Нельзя писать «3000 Диме на приват». Продумайте заранее все фразы и договоритесь об обозначениях.

Вы проснулись в субботу, в обед с привкусом тухлых кошачьих носков во рту, тремя размытыми печатями, двумя клубными браслетами и одной татуировкой с рожей чихуахуа на правом бедре. На карте минус месячный доход. Но вы всё предусмотрели, поэтому гоу в чат и историю операций с карты в приложении банка.

Помните! Очень большая вероятность, что вы вчера платили за всех и вам все должны (что не факт). В этом случае не один из ваших друзей чисто морально не хочет к похмелью ещё добавлять чувство долга! Поэтому от того, как быстро вернёте деньги зависит только от вас. Теперь вы коллектор и начинаете обзвон друзей.

1. Кидаем в чат позывной. Кто, за что, вчера платил.

2. Открываем Excell и создаём следующую таблицу:

И так: Перед вами таблица со следующими параметрами:

Столбец «Событие» — кратко фиксим события оплаты;

Столбцы «Участники» — все, кто присутствовал на пьянке;

Столбец «Сумма» — Общая сумма к разделу;

Столбец «N» — кол-во человек участвующих в разделе;

Столбец «Проверка» — собственно сумма всех сумм в столбцах B-E = 0.

Если пьянка длилась несколько дней (например Нашествие), то слева ещё вставляется таблица «Дата».

Допустим начали сначала Вася, Петя, Игорь. Зашли в Кафе, пожрали, да выпили на 9500 рубликов. Платил Петя.

Тогда для Пети пишется формула:

Для всех, кто учувствовал пишется формула:

В результате получаем:

Все кто с минусом — те в долгах;

Кто с плюсом -тот кредитор.

Формулы с $ помогают просто копипастить ячейки, и заполняя только столбцы Сумма и N, долбить долг дальше по событиям:

Как мы видим, в пабе к нам пришёл ещё Ваня.

Дальше из караоке идём в клуб. Ваня цепляет тёлку, Вася уезжает домой.

Для этого вводим ещё один столбец а с Васей прощаемся. В клубе мы оставляем 15 к.

Ваня с «Ваней — 2» уезжают, а Петя и Игорь идут:

После стрипухи все возвращаются в кафе на завтрак, при этом по дороге подбирая уже сбежавшего под утро Ваню. В кафе решают платить по честному! Поэтому каждый платит за то, сто съел (Платит Ваня):