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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

#УР2 #Сводные_таблицы

Для перехода по листу пользуйтесь полем слева от строки формул. Поможет избежать долгой прокрутки в попытке попасть в ячейку AIF47 и легко перенесет в нужный именованный диапазон
#УР1 #Работа_с_листами_книги

Лишние пробелы и непечатаемые символы могут сильно осложнить жизнь. Из-за них могут некорректно работать формулы, сортировки, сводные таблицы и т.д. Для исправления некорректного текста используются функции СЖПРОБЕЛЫ (удаляет лишние пробелы) и ПЕЧСИМВ (удаляет непечатаемые знаки).

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

#УР2 #Условное_форматирование

Одна из интересных проблем, которые иногда встречаются в Excel - псевдопустые ячейки. Это такие ячейки, в которых находится пустая текстовая строка. Функция ДЛСТР покажет нам, что в такой ячейке нет ни одного символа, но функция ЕПУСТО скажет, что ячейка не пустая. А комбинация клавиш Ctrl+стрелка будет перемещать нас вдоль таких ячеек так, словно они заполнены. Иногда это бывает неудобно.

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

#УР1 #Обработка_таблиц

Проверка данных может контролировать ввод дат в ячейку. Например, можно разрешить вводить только даты со вчерашней по завтрашнюю (то есть СЕГОДНЯ +/- один день).

Такой вариант реализовать очень просто. Минимальной датой указываем СЕГОДНЯ()-1, а максимальной - СЕГОДНЯ()+1. Только помните, что функция СЕГОДНЯ() возвращает текущую системную дату на вашем компьютере.

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

Если хотите реализовать в ячейке выпадающий список с датами со вчерашней по завтрашнюю, то это можно сделать введя в какой-то диапазон ячеек три простые формулы:
=СЕГОДНЯ()-1
=СЕГОДНЯ()
=СЕГОДНЯ()+1
Они будут всегда возвращать нужные даты, а в самой проверке данных нужно выбрать пункт "Список" и указать диапазон ячеек с формулами. Но снова напоминаем: СЕГОДНЯ() возвращает текущую дату вашей операционной системы. Если она неправильная, то и формула будет работать неверно.

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

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

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

#УР1 #Работа_с_листами_книги

В Excel есть множество способов настроить внешний вид графика или диаграммы. Один из них - замена стандартных маркеров на какое-то изображение (например, на логотип или значок).

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

#УР3 #Диаграммы