Сообщество - MS, Libreoffice & Google docs
MS, Libreoffice & Google docs
119 постов 7 117 подписчиков
1678

Суммирование в Excel через одну, две, три ячейки

Всем известен способ подсчёта данных в Excel путём элементарного сложения ячеек =C2+C4+C6, либо с помощью функции =СУММ(C2;C4;C6), но если таблица большая, то такой способ выведет из себя даже самого терпеливого. Поэтому предлагаю рассмотреть способ суммирования по условию: =СУММЕСЛИ(B2:B15;"Доход";C2:C15)

=СРЗНАЧЕСЛИ(B2:B15;"План";C2:C15)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

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

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

B2:B15))

=МИН(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=0;

B2:B15))

=МАКС(ЕСЛИ(ОСТАТ(СТРОКА(B2:B15);2)=1;

B2:B15))

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Если же нужно суммировать каждую третью, четвертую и т.д. ячейки, то изменяется только делитель номера строки:

=СУММ(ЕСЛИ(ОСТАТ(СТРОКА(C2:C17);4)=1;

C2:C17))


В качестве второго варианта можно использовать функцию СУММПРОИЗВ, которая вводится как обычная формула (без Ctrl+Shift+Enter), но работает также:

=СУММПРОИЗВ(--(ОСТАТ(СТРОКА(C2:C17);4)=1);C2:C17)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Для подсчёта суммы, находящейся в столбцах, вместо СТРОКА используем СТОЛБЕЦ, затем протягиваем формулу вниз:

=СУММПРОИЗВ(--(ОСТАТ(СТОЛБЕЦ(B3:F3);2)=0);B3:F3)

Суммирование в Excel через одну, две, три ячейки Excel, Таблица, Сумма, Лайфхак, Полезное, Длиннопост

Примечание: для красивого отображения цифр устанавливаем формат ячеек «Числовой» – Число десятичных знаков «0» – галочка на «Разделитель групп разрядов». Для процентов «Процентный» – Число десятичных знаков «0».

Показать полностью 2
1440

Получение списка файлов в папке

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

1. *.* – все файлы;

2. *.xl* – все файлы Excel (xls, xlsx, xlsm, xlsb и т.д.);

3. *.exe – все исполняемые файлы (программы);

4. *.docx (doc, docm) – все документы Word. Добавив ключевое слово Рок*.docx в список войдут все названия файлов, начинающиеся со слова «Рок».

Получение списка файлов в папке Excel, Таблица, Полезное, Лайфхак, На заметку, Длиннопост

Дальше нажатием Ctrl+F3 или на вкладке «Формулы» - «Диспетчер имён», в открывшемся окне создадим новое имя Список_файлов и введём в строку Диапазон следующую формулу =ФАЙЛЫ(Лист1!$A$1) где «Лист1» – это название листа, а «А1» – ячейка с адресом

Получение списка файлов в папке Excel, Таблица, Полезное, Лайфхак, На заметку, Длиннопост

Теперь в созданном нами названии «Список_файлов» уже содержатся нужные имена файлов, осталось их оттуда извлечь. Для этого используем в ячейке А3 следующую формулу и протянем её вниз: =ЕСЛИОШИБКА(ИНДЕКС(Список_файлов;СТРОКА()-2);””)

Получение списка файлов в папке Excel, Таблица, Полезное, Лайфхак, На заметку, Длиннопост

Функция ЕСЛИОШИБКА нужна, чтобы спрятать ошибку #ССЫЛКА, которая возникает, когда мы протягиваем формулу "с запасом", т.е. на больший по размеру диапазон, чем количество имеющихся у нас файлов.


Функция ИНДЕКС извлекает из массива элемент по номеру, например, формула =ИНДЕКС(Список_файлов; 5) выдаст имя пятого по счету файла в папке.


Функция СТРОКА, выдает порядковый номер текущей строки, из которого мы вычитаем 2, т.к. первая ячейка, куда выводится имя, в нашем случае А3.

Показать полностью 1
5139

Минидиаграммы в ячейках

Очередной способ визуализации данных – это использование гистограмм в условном форматировании.

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

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

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

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

2. Также можно воспользоваться текстовой функцией ПОВТОР, которая выводит в ячейку любой заданный символ нужное количество раз. Для этого вводим в соседнюю от цифр ячейку формулу =ПОВТОР("|";B2) и нажимаем «Enter», при шрифте Arial Cyr получится так:

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

3. Для вывода нестандартных символов (заранее зная код) можно использовать функцию СИМВОЛ. Например, символ с кодом 103 - черный прямоугольник шрифта Webdings, вводим в соседнюю ячейку формулу =ПОВТОР(СИМВОЛ(103);C2:C8) и нажимаем «Enter». Поэтому предварительно установите этот шрифт для ячеек C2:C8.

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

Также можно использовать символы других шрифтов, например символ с кодом 110 из шрифта Wingdings.

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

4. Остался применить последний способ – это стрелки в разделе «наборы значков», здесь имеется большой выбор разных элементов, которые также отображаются в самой ячейке.

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

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

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

Имеющиеся символы в шрифтах Webdings и Wingdings можно посмотреть в разделе «Вставка» - «Символы»

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

И в конце предлагаю оценить какая минидиаграмма самая интересная

Минидиаграммы в ячейках Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

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

Показать полностью 6
2290

Мгновенное заполнение в Excel

Одной из самых гениальных и полезных, на мой взгляд, функцией Excel является «Мгновенное заполнение» (доступно с Microsoft Office 2013 года). Оно творит такие чудеса, что за пару движений пальцами вы сэкономите время и нервы от выполнения огромной рутинной работы. Вместе с тем функция проста как дважды два и подойдёт для пользователей любого уровня. Почувствуйте себя всемогущим, мгновенно решив сложные задачи, это как щелчок Таноса, только лучше)) Заинтриговал?)) Теперь поподробней:


1. У нас имеется таблица с фамилиями, именем и отчеством. Из неё нам нужно сделать «Фамилию и инициалы». Для этого мы в соседнюю строку вводим как это должно быть, затем при вводе на следующей строке у нас появляется предлагаемый вариант, если он нас устраивает, то нажимаем «Enter», если список не появился жмём Ctrl+ E и вуаля, у вас готов весь список по заданной форме.

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

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

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

Здесь есть одно правило, вводимый текст должен быть непосредственно рядом с исходным текстом, не важно слева или справа, но, чтобы рядом, иначе выйдет ошибка

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку
Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

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

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

Также он работает и в обратную сторону, больше не нужны формулы СЦЕПИТЬ, ЛЕВСИМВ, ПРАВСИМВ, ПСТР и т.д.

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

Можно задать регистр и сделать фамилии, написанные верхним регистром или полностью капсом. Можно исправлять слова с НеПрАвИлЬнЫм регистром. Если между словами нет пробела, это тоже не проблема, главное, чтобы Ф.И.О. начиналось с заглавной буквы, для определения начала слова

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

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

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

2. Вы восхищены его супер возможностями? Тогда возрадуйтесь, потому что это ещё не всё)) Есть возможность склейки текста. Итак, у нас имеются значения, разбитые по ячейкам, но нам нужно, чтобы они были в одной ячейке и ещё с дополненным текстом. Вводим в первую строку так, как нам нужно, повторяем то же действие на второй строке и на третьей нажимаем Ctrl+E и радуемся результатом

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

3. Вы уже кричите от того почему вы не знали об этом раньше? Тогда продолжаем удивляться «Мгновенным заполнением»))

У нас есть текст с цифрами в ячейках и нам нужно вывести в другой столбец только цифры, делать это вручную означает почувствовать себя Золушкой, перебирающей горох, но волшебным нажатием Ctrl+E, получаем нужный результат, причём Excel не важно, есть ли там лишние пробелы, знаки или прочее, он как настоящий мужик – берёт и делает.

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

То же самое он делает и с извлечением только текста

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

Если попадается разношёрстный текст, где первым стоит слово затем цифра, а в нижних строках имеется сначала цифра потом текст, лучше сначала в первых двух строках дописать цифры перед текстом, затем нажать Ctrl+E, тогда Excel правильно определит алгоритм и выведет текст без ошибок


4. Ты до сих пор сомневаешься в возможностях мгновенного заполнения? Говоришь он не справится с датами? Забери свои слова обратно, даты он изменяет на изи, причём определяет не только цифровые, но и текстовые, главное, чтобы они были в именительном падеже, либо на внятном сокращении месяца

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

5. Ты тайный поклонник нестандартных дат? Не волнуйся, мгновенное заполнение удовлетворит твои желания. Он может изменить обычную дату в Unix-формат ГГММДД. Для этого меняем формат соседнего столбца на текстовый, затем вводим форму значений в пару ячеек, чтобы правильно задать алгоритм и нажав Ctrl+E получаем результат

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

Вы ещё не устали удивляться великолепным «Мгновенным заполнением»? Тогда получайте очередную порцию волшебства))

6. У нас имеется столбец с телефонными номерами, но они выглядят как простые цифры, мы же придадим им красоты, добавив + и заключив код оператора в скобки. Преобразуем цифры и соседний столбец в текстовый формат, затем вводим нужный параметр в первые две ячейки соседнего столбца и нажав Ctrl+E, гордимся проделанной работой

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

7. Для тех, кто выгружает даты из различных ERP и CRM-систем, дата представляется 8-значным числом в формате ГГГГММДД. Исправлять в нормальный вид можно было функцией ДАТАЗНАЧ, либо вручную, но теперь ловите лайфхак от героя «Мгновенного заполнения», главное покажите ему правильный алгоритм, дальше он всё сам сделает за вас))

Мгновенное заполнение в Excel Excel, Таблица, Полезное, Длиннопост, На заметку

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

- первое правило напомню, ячейки должны находиться рядом с определяемым столбцом;

- при выявлении шаблона учитываются все значения в строке - слева и справа от вводимого столбца, поэтому лишние колонки, способные запутать алгоритм или внести помехи, лучше заранее отделить от рабочих данных пустыми столбцами или удалить;

- мгновенное заполнение также работает и в умных таблицах;

- ошибка или опечатка при наборе ячеек-образцов может привести к тому, что мгновенное заполнение не сможет найти нужное значение и не сработает;

- бывает, что шаблон определяется некорректно, поэтому всегда проверяйте полученные результаты (хотя бы выборочно).


Как вам «Мгновенное заполнение», поделитесь впечатлениями в комментариях))

Показать полностью 13
720

Подсветка и сравнение двух и более списков

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

Допустим, что у нас имеется два списка с повторяющимися словами:

Самый быстрый и лёгкий способ найти отличия в двух таблицах – это применить условное форматирование. Итак, выделяем оба диапазона удерживая клавишу «Ctrl» и на вкладке Главная – Условное форматирование – Правила выделения ячеек – Повторяющиеся значения выбираем опцию "Уникальные", в результате Excel подсветит все ячейки, где нет повторов. Выбрав вариант "Повторяющиеся", будут выделены совпадения:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Таким способом можно применить оба правила одновременно.

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

Чтобы получить отличия отдельным списком я пошагово покажу процесс создания такого списка. Для этого вводим в соседней ячейке D2 формулу =ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;C2)=0;СТРОКА(C2)) которая будет проверять количество вхождений с помощью функции СЧЁТЕСЛИ и если оно равно 0, то выводить номер строки для текущего элемента функцией СТРОКА.

Для того, чтобы номер ячейки стал абсолютным, т.е. со знаком $, нужно в строке формулы навести курсор на номер и нажать F4.

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Дальше в ячейке F2 используем формулу СТРОКА(F1)

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Затем в ячейку G2 вводим формулу =НАИМЕНЬШИЙ($D$2:$D$10;F2) которая выведет последовательно номера строк от меньшего к большему:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

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

=ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ($D$2:$D$10;F2)-1)

которая показывает значение из массива-столбца по порядковому номеру:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Теперь, чтобы избавиться от вспомогательного столбца, вместо диапазона D2:D10 вставим в нашу формулу логическую проверку количества вхождений с помощью функций ЕСЛИ и СЧЁТЕСЛИ, которую мы применили в самом начале:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Вводим формулу =ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F2)-1) Чтобы формула массива заработала нажимаем сочетания клавиш Ctrl+Shift+Enter и протягиваем формулу вниз. После этого столбец D можно удалить.


Добавим красоты спрятав ошибку #ЧИСЛО!, возникающие в избыточных ячейках. Добавляем к формуле функцию =ЕСЛИОШИБКА, получается:


=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$10;НАИМЕНЬШИЙ(ЕСЛИ(СЧЁТЕСЛИ($A$2:$A$9;$C$2:$C$10)=0;СТРОКА($C$2:$C$10));F10)-1);)


Убираем нули в Файл – Параметры – Дополнительно – Показывать нули… и получаем результат

Заменив цифру 0 на 1, мы получим общие значения в списках

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Для поиска совпадений в трёх и более списках проделаем следующее.

Сначала озаглавим наши списки, чтобы использовать их в формулах. Для этого выделим оба диапазона вместе с названиями, удерживая клавишу «Ctrl» и на вкладке Формулы – Создать из выделенного в открывшемся окне включим галочку «в строке выше» и жмём ОК:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Excel даст нашим спискам имена, взяв их из первых строк выделенных диапазонов, т.е. Metal1 и Metal2. Проверить именованные диапазоны можно на вкладке Формулы - Диспетчер имён:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

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

Нужная нам формула для поиска и вывода общих элементов в этих двух списках будет выглядеть следующим образом:

=ИНДЕКС(metal1;ПОИСКПОЗ(1;СЧЁТЕСЛИ(metal2;metal1)*НЕ(СЧЁТЕСЛИ($H$1:H1;metal1));0))

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

Плюсом является то, что при увеличении количества списков достаточно будет добавить ещё один именованный диапазон (Metal3) и множитель в нашу формулу-массив проверки совпадений с помощью ещё одной функции СЧЁТЕСЛИ:

Подсветка и сравнение двух и более списков Excel, Таблица, Сравнение, Список, Полезное, Лайфхак, На заметку, Длиннопост

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

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

Показать полностью 9
1407

Очистка текста в Excel

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


1. Когда в названиях городов присутствует лишняя буква «г.». Выделяем текст, нажимаем «Ctrl+H» и дедовским способом заменяем «г.» на пустую строку.

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

2. Дальше почистим текст от лишних пробелов, которые встречаются не только в начале слова, но и в середине, и в конце, оставив нужный пробел только между слов. Для этого в соседней строке вводим формулу =СЖПРОБЕЛЫ(A1:A3) и нажимаем «Enter», затем протягиваем край ячейки на всю длину текста

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

3. Теперь очистим текст от апострофов, которые встречаются при выгрузке из различных баз данных и 1С, а также переносов слов (Alt+Enter), которые некоторые пользователи так любят ставить. Минус апострофа в том, что его не видно в ячейке, но он мешает при сортировке и вручную его не убрать. Для этого в соседней строке вводим формулу =ПЕЧСИМВ(A1:A3) и нажимаем «Enter», затем протягиваем край ячейки на всю длину текста

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

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

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

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

Очистка текста в Excel Excel, Таблица, Полезное, Лайфхак, На заметку, Текст, Длиннопост

6. Остальные столбцы удаляем, а в освободившееся время, читаем пикабу, работаем))

Показать полностью 2
596

Суммирование чисел, написанных через дробь

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

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

Для начала выделите ячейки с цифрами и задайте им текстовый формат, чтобы числа не переводились в даты. Затем в первой ячейке, как указано на скриншоте, введите формулу =СУММПРОИЗВ(--ЛЕВСИМВ(B3:F3;ПОИСК("/";B3:F3)-1))

Суммирование чисел, написанных через дробь Excel, Таблица, Сложение, Лайфхак, Полезное, На заметку, Длиннопост

А во второй =СУММПРОИЗВ(--ПСТР(B3:F3;ПОИСК("/";B3:F3)+1;999)) затем, удобным для вас способом, протяните обе формулы на другие ячейки вниз.

Суммирование чисел, написанных через дробь Excel, Таблица, Сложение, Лайфхак, Полезное, На заметку, Длиннопост

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

249

Спарклайны

Продолжаю серию постов по Excel.

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

Для этого нужно выделить пустые ячейки, куда мы хотим поместить спарклайны, и на вкладке «Вставка» выбрать кнопки группы «Спарклайны»:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Применив кнопку «Гистограмма» мы получим мини-графики, которым в разделе «Конструктор» можно задать цвет минимальных и максимальных значений:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Аналогичным способом, применив кнопку «График», указываем диапазон данных, в результате мы получим кривую линию отображающую динамику цифровых значений:

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

Во вкладке «Конструктор» можно добавить «Маркеры», тогда каждое значение будет отмечено точкой. Здесь также можно подобрать цвет линий и точек.

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

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


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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост

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

Спарклайны Excel, Таблица, Лайфхак, Полезное, Длиннопост
Показать полностью 4
378

Подсветка дат за 30 и более дней

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

Здесь я покажу формулу для выделения дат за 30 и более дней.

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

1. Создаём таблицу в Excel.

2. Создаём текущую обновляемую дату. Наводим курсор на удобное для вас место и вводим формулу: =СЕГОДНЯ()

3. Чтобы подсветить даты за 3 месяца вперёд выделяем всю таблицу Excel (без шапки) и выбираем на вкладке ГлавнаяУсловное форматирование Создать правило. В открывшемся окне задаём последний тип правила: Использовать формулу для определения форматируемых ячеек и вводим в строку следующую формулу: =$H1<СЕГОДНЯ()+120

Не забываем в графе «Формат» выбрать цвет заливки.

4. Аналогичным способом, для подсветки дат за 2 месяца вперёд, в новое правило вводим: =$H1<СЕГОДНЯ()+90

5. Также подсвечиваем даты за 1 месяц вперёд: =$H1<СЕГОДНЯ()+60

6. Даты меньше текущей даты подсветим с помощью формулы: =$H1<$L$2

7. Для пустых ячеек вводим: =ЕПУСТО($H1).

В итоге у вас должно получиться так:

Подсветка дат за 30 и более дней Excel, Таблица, Контроль, Срок, Полезное, Лайфхак

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

178

Цветовые шкалы

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

Для этого выделяем таблицу с цифрами и применяем «Цветовые шкалы» в «Условном форматировании».

Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

Можно выбрать разные вариации по цвету и значениям

Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост
Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост
Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост

Здесь же можно настроить параметры и цвет выделения наибольших и наименьших цифр

Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост
Цветовые шкалы Excel, Таблица, Лайфхак, Полезное, На заметку, Длиннопост
Показать полностью 2
Мои подписки
Подписывайтесь на интересные вам теги, сообщества,
пользователей — и читайте персональное «Горячее».
Чтобы добавить подписку, нужно авторизоваться.
Отличная работа, все прочитано!