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

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

#Справка

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

Основной;-Основной;

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

#УР3 #Пользовательские_форматы

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

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

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

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

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

#УР1 #Печать_таблиц

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

- различные значения - все варианты значений, встречающиеся в диапазоне. В примере выше это Арбуз, Дыня, Яблоко, Банан
- уникальные значения - те значения, которые в списке встречаются только один раз. В примере выше это Яблоко и Банан.

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

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

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

Если закрались сомнения, что данные чисты, быстро проверить наличие чисел как текст в массиве чисел можно так:
1) Выделяем ячейки, в которых должны быть только числа
2) В строке состояния смотрим, сколько в выделенном диапазоне значений всего (Количество) и сколько из них чисел (Количество чисел).
Если подсчеты совпали, то всё хорошо. Все числа - это числа. В противном случае исправляем проблему любым удобным способом (например, копированием пустой ячейки и специальной вставкой со сложением).

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

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

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

#Справка

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

Представим, что нужно создать транспонированную таблицу, в каждой ячейке которой будет ссылка на соответствующую ячейку исходных данных. Специальная вставка при транспонировании не дает вставить связь. Функция ТРАНСП не создает отдельные ссылки, а возвращает сразу весь массив. Решить задачу поможет обычная функция замены текста.

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

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

1) Ширина полосы прокрутки максимально увеличена. В итоге ярлыки не помещаются на экране. Исправляем, потянув за 3 точки в левом нижнем углу

2) Для файла отключено отображение ярлычков листов в Параметрах. Идем в Файл-Параметры-Дополнительно и включаем обратно.

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

Мы уже рассказывали про генерацию случайных чисел в Excel с помощью СЛУЧМЕЖДУ (можете найти в поиске или навигаторе по каналу). Теперь пара приемов работы с СЛЧИС.

По умолчанию функция генерирует случайное число от 0 до 1. Если вам нужно сформировать случайное число от 0 до N, то достаточно просто умножить результат вычисления СЛЧИЛ на N.

А если нужно получить число от N до M, то делаем так:
=СЛЧИС()*(M-N)+N
И не забываем при необходимости округлять результаты до нужного количества разрядов.

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

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

Подготовили небольшую памятку в виде таблички. В ней видно, какой результат дает разное сочетание значений ИСТИНА и ЛОЖЬ и логических функция И, ИЛИ и ИСКЛИЛИ

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

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

Ну а если надо перевести десятичное число в другую систему, то имеется функция ОСНОВАНИЕ. Поддерживает основания от 2 до 35.

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

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

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

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

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

Кстати, в последних обновлениях Excel примечания стали называться заметками. А под старым именем "Примечания" теперь скрывается новый инструмент - такой же как в последнем Word. Поэтому пусть вас не смущает слово "заметки" в контекстном меню на видео.

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

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

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

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

В Excel символы * и ? используются как подстановочные знаки. Поэтому в формулах с ними надо быть аккуратными. Например, если оценки в таблице выставлены в виде звездочек, то функция СЧЕТЕСЛИ не сработает, если в условии указать одни только звездочки.

Если надо использовать символы * и ? именно как самостоятельные символы, то перед каждым отдельным знаком нужно указывать символ "~" (тильда). Или заменить формулу на аналогичную, которая не работает с символами подстановки.

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

Частая задача - подсчёт рабочих часов за какой-то период времени. Если знаем время начала и окончания рабочего дня и число дней - то всё просто. Умножаем дни на длительность рабочего дня.

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

В итоге имеем длинную формулу:
=(ЧИСТРАБДНИ(A2;B2)-1)*($K$1-$H$1)+ЕСЛИ(ЧИСТРАБДНИ(B2;B2);МЕДИАНА(ОСТАТ(B2;1);$H$1;$K$1);$K$1)-МЕДИАНА(ЧИСТРАБДНИ(A2;A2)*ОСТАТ(A2;1);$K$1;$H$1)

A2 - начало отсчёта
B2 - окончание отсчёта
$H$1 - начало рабочего дня
$K$1 - окончание рабочего дня

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

Из предыдущего урока вытекает логичный вопрос - как в этой формуле учесть время обеденного перерыва. Пожалуй, лучший вариант это сделать такой:
1) Считаем рабочие часы без учёта обедов по уже известной формуле
2) Считаем часы пришедшиеся на обеды за указанный период по этой же формуле
3) Вычитаем второе из первого

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

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

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

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

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

Если Вы часто используете защиту листа в своих файлах, то возможно сталкивались с такой задачей. Нужно быстро выяснить, в каких ячейках диапазона включена защита (стоит галочка "Защищаемая ячейка" на вкладке "Защита" в окне "Формат ячеек"), а в каких нет.

Ручная проверка каждой ячейки явно не выход. Но можно использовать условное форматирование и функцию ЯЧЕЙКА, которая умеет проверять, включена ли защита. Просто, быстро и наглядно.

#УР2 #Особенности_совместной_работы

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