Excel Everyday

Чтобы быстро округлять значения времени в Excel, удобно использовать тот факт, что время - дробная часть единицы (1 мин = 1/1440, 1 час = 1/24) и функции ОКРУГЛения
#УР1 #Простые_Вычисления

Поиск и замена умеет менять не только текст, но и форматирование. Например, можно перекрасить сразу все ячейки из одного цвета в другой (но лучше - использовать стили ячеек)
#УР1 #Оформление_таблиц

Когда Вы заполняете столбец таблицы и приходится вводить то, что уже было в столбце, нажмите Alt+вниз. Откроется выпадающий список уже введенных значений
#УР2 #Работа_с_большими_табличными_массивами

За оформление ячеек по умолчанию в книге Excel отвечает стиль "Обычный". Если вы хотите, например, изменить размер шрифта для всех ячеек в файле (в том числе и для тех листов, которые будут созданы в дальнейшем), то просто изменить настройки формата для этого стиля.

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

#УР1 #Оформление_таблиц

Если планируете передать файл кому-то, кто, возможно, использует более старый Excel , чем Ваш - выполните проверку совместимости. Она покажет все конфликты версий.
#УР2 #Особенности_совместной_работы

Функции СУММЕСЛИ и СУММЕСЛИМН одни из самых популярных в Excel. Но они не работают с закрытыми файлами. Обойти можно, заменив СУММЕСЛИ на СУММПРОИЗВ. Она справится.
#УР2 #Применение_встроенных_функций

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

В новых Excel это решается очень просто функцией ОБЪЕДИНИТЬ. А вот в старых все не так однозначно, потому что пустые ячейки в разных строках могут менять позицию. Выручит комбинированная формула с использованием трех функций. Пишем один раз и просто копируем вниз.

#УР2 #Примеры_формул

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

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

#УР2 #Проверка_данных

Порой возникает задача получить из какого-то столбца значения каждой второй, каждой третьей, каждой пятой строки и т.д. Делается это очень просто. Понадобится использовать функцию ИНДЕКС и функцию СТРОКА. К последней надо будет добавить коэффициент, который и будет обозначать шаг по строкам.

#УР2 #Примеры_формул

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

Можно использовать их для преобразования числа в двоичное, восьмеричное и шестнадцатеричное.

#УР2 #Применение_встроенных_функций

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

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

Итак, в некоторых случаях может помочь инструмент "Текст по столбцам". Выделяем "кривые" даты, доходим до третьего шага инструмента и выбираем в списке нужный формат "слипшейся" даты. Быстро и эффективно.

#УР3 #Обработка_внешних_Баз_Данных

В позапрошлом уроке мы показывали прием, в основе которого была функция ЧИСТРАБДНИ.МЕЖД. Она появилась в Excel 2010. Если вдруг у вас более старая версия, а описанную задачу надо решить - можно использовать формулу массива.

Она довольно длинная, но с задачей справляется легко и работает во всех версиях Excel. Ну а если Вы любите сложные и красивые формулы, то она вам точно понравится.
#УР2 #Примеры_формул

Любую команду на ленте можно выполнить горячими клавишами (даже если для нее нет спец.сочетания). Нажмите Alt и возле каждой команды появится буква, которую надо нажать для ее запуска
#Горячие_клавиши

Разберем небольшую, но интересную задачу. Нужно подсчитать, сколько Понедельников пришлось на определенный временной период, заданный первой и последней датой. Как часто бывает - решений несколько. Одно из них - использование функции ЧИСТРАБДНИ.МЕЖД.

Находим сколько всего дней в периоде. А затем вычитаем из этого числа кол-во дней без учета нужного (Понедельника). На выходе получаем количество Понедельников в указанном периоде.
#УР2 #Примеры_формул

Иногда нужно отсортировать столбец с числами "по алфавиту". Числа, начинающиеся с единицы - в начале, затем - те, что с двойки и т.д. Для этого придется числа превратить в текст
#УР1 #Обработка_таблиц

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

В таком случае пригодится расширенный фильтр (про него мы уже рассказывали https://t.me/excel_everyday/532 ). Ну а если он слишком сложен для простой задачи, то можно добавить "на лету" дополнительный столбец с формулой, которая проверит нужные условия. Тоже весьма удобно.

#УР1 #Фильтрация_и_сортировка

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

Это позволит неопытным пользователям работать с файлом, не отвлекаясь на лишние ячейки.
#УР1 #Работа_с_листами_книги

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

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

#УР1 #Оформление_таблиц

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

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

Делается это очень простой формулой с использованием функции ОСТАТ.

#УР2 #Проверка_данных