Создание новой вкладки.

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

#параметры #созданиевкладки #созданиегруппы #настройкаленты

Подсчет количества слов.

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

=ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";""))+1

Обращаем внимание на то что, формула вернет 1, если ячейка пуста, поэтому добавим в формулу условие ЕСЛИ:

=ЕСЛИ(ДЛСТР(A1)=0;0;ДЛСТР(СЖПРОБЕЛЫ(A1))-ДЛСТР(ПОДСТАВИТЬ(СЖПРОБЕЛЫ(A1);" ";""))+1)

#количествослов #текстовыефункции #подсчетслов #ДЛСТР #ПОДСТАВИТЬ #СЖПРОБЕЛЫ #ЕСЛИ

Вычисление возраста человека. Сравнение дат. Функция РАЗНДАТ(DATEDIF).

Отвечаем на вопрос нашего подписчика. Один из самых простых способов вычислить возраст человека состоит в использовании функции РАЗНДАТ(). Эта функция делает любой вариант сравнения дат предельно простым.
Для вычисления возраста человека с использованием функции РАЗНДАТ() можно воспользоваться формулой, подобной этой:

=РАЗНДАТ(В2; СЕГОДНЯ(); “y”)

Функцию РАЗНДАТ() можно использовать для вычисления количества дней, месяцев и лет между двумя датами. Она имеет три обязательных аргумента: нач_дата, кон_дата и код. Значение аргумента код указывает на то, что именно нужно найти (разность дней, месяцев или лет).

#сравнениедат #РАЗНДАТ #DATEDIF #возрастчеловека

Количество рабочих дней. Функция ЧИСТРАБДНИ.МЕЖД().

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

При вводе функции ЧИСТРАБДНИ.МЕЖД(), как только вы дойдете до третьего аргумента, Excel выведет дополнительное меню для его определения. Просто выберите требуемый код.

В функции ЧИСТРАБДНИ.МЕЖД() есть и четвертый необязательный аргумент, определяющий перечень дней в году, которые следует дополнительно исключить из календаря рабочих дней, например государственные праздники. Этот перечень определяется вручную в виде диапазона ячеек, содержащих исключаемые даты (в произвольном порядке).

#рабочиедни

Вычисление количества лет и месяцев между двумя датами. Функция РАЗНДАТ (DATEDIF).

Отвечаем на вопрос нашего подписчика. Чтобы определить количество лет и месяцев между двумя датами, можно воспользоваться двумя функциями РАЗНДАТ(), тогда формула будет иметь следующий вид:

=РАЗНДАТ(A2;B2;"Y")&" лет, "&РАЗНДАТ(A2;B2;"YM")&" месяцев"

#количестволет #количествомесяцев #РАЗНДАТ #DATEDIF

Заблокированные и незаблокированные ячейки. Защита рабочего листа.

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

#заблокированныеячейки #ячейки #защита #защиталиста

Преобразование дат в стандартном формате в юлианский формат.

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

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

=ПРАВСИМВ(ГОД(A2);2)&A2-ДАТА(ГОД(A2);1;0)

#дата #юлианскийформат

Вычисление процента отклонения.

Отвечаем на вопрос нашего подписчика. Отклонение является показателем различия, существующего между двумя значениями. Чтобы лучше понять предыдущее определение, представим, что в один из дней вы реализовали 120 единиц продукции, а на следующий день — 150 единиц. Различие между объемами реализации в натуральных единицах определить совсем несложно: во второй день было реализовано на 30 единиц продукции больше. Из 150 единиц вычитаем 120 единиц, что и дает нам отклонение в +30 единиц.
Хорошо, тогда что же такое процентное отклонение? По сути, это та же разность между исходным значением (120) и новым значением (150), но выраженная в процентах. Вычисляется процентное отклонение как разность между новым и исходным значениями, деленная на исходное значение. В нашем примере (150 - 120)/120 = 25%. Процентное отклонение указывает на то, что в последний день вы реализовали на 25% больше изделий, чем в предыдущий.

#отклонение #процентотклонения #процентноеотклонение

Функция ОБЪЕДИНИТЬ (TEXTJOIN).

#текстовыефункции #ОБЪЕДИНИТЬ #TEXTJOIN

Функция ОБЪЕДИНИТЬ (TEXTJOIN).

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

ОБЪЕДИНИТЬ(разделитель; игнорировать_пустые; текст!.; [текст2]; . ..)

Первый аргумент определяет разделитель, который будет вставлен между содержимым объединяемых ячеек. Если в качестве его значения задать запятую, то при объединении текстовых значений обрабатываемых ячеек программа вставит между ними этот символ.
Второй аргумент указывает, как следует поступить с пустыми ячейками. Он может принимать только два значения — истина или ложь. В первом случае программа будет просто игнорировать пустые ячейки. Фактически этот аргумент определяет, как в результирующей строке будут расставлены разделители, определенные в первом аргументе. Если значение второго аргумента - ИСТИНА, то программа не будет вставлять дополнительные разделители (например, запятые) при обнаружении пустых ячеек в обрабатываемом диапазоне.
Третий аргумент определяет текст, который объединяется. Это может быть просто текстовая строка или массив строк, скажем, диапазон ячеек. Функция ОБЪЕДИНИТЬ() требует, чтобы в этом аргументе было задано не менее одного значения или ссылки на ячейку.

#текстовыефункции #ОБЪЕДИНИТЬ #TEXTJOIN

Извлечение отдельных частей даты.

Хотя это может показаться тривиальным, часто оказывается полезным воспользоваться отдельной частью некоторой даты. Например, может быть поставлена задача отфильтровать все записи, в которых дата заказа относится к определенному месяцу.
В подобных ситуациях потребуется извлечь из отформатированных значений дат значение месяца. Для решения подобных задач Excel предоставляет набор простых функций, позволяющих выделять в отформатированных значениях дат их отдельные составные элементы. К этим функциям относятся следующие:
ГОД()  извлекает из заданной даты значение года;
МЕСЯЦ() извлекает из заданной даты значение месяца;
ДЕНЬ() извлекает из заданной даты значение дня месяца;
ДЕНЬНЕД() определяет значение дня недели для заданной даты;
НОМНЕДЕЛИ() для заданной даты возвращает номер недели в году.

#дата #частидаты #ГОД #МЕСЯЦ #ДЕНЬ #ДЕНЬНЕД #НОМНЕДЕЛИ

Прибавление к значению времени часов, минут и секунд. Функция ВРЕМЯ(TIME).

Поскольку значение времени представляет собой не более чем расширение десятичными дробными разрядами целочисленного значения, представляющего дату, можно просто сложить два значения времени, чтобы получить суммарное значение времени, которое будет правильно интерпретироваться программой. Однако в некоторых случаях может потребоваться увеличить значение времени на определенное количество часов и/или минут, заданное десятичным числом. В этой ситуации проще всего воспользоваться функцией ВРЕМЯ(). Например, есть исходное время, которое нужно увеличить на 5 часов 30 минут, для этого воспользуемся следующей формулой:

=A2+ВРЕМЯ(5;30;0)

#время #прибавлениевремени #ВРЕМЯ #TIME

Вычисление прошедшей и оставшейся частей года в процентах. Функция ДОЛЯГОДА (YEARFRAC)

При построении в Excel отчетов и графических панелей итоговых данных часто может быть выгодно указать в процентах, какая часть года уже прошла или еще осталась. Эти процентные величины затем могут использоваться в других вычислениях или служить обычным напоминанием для тех, кто будет работать с данным документом.
Функция ДОЛЯГОДА() имеет два обязательных аргумента: начальную дату и конечную дату. На основании этих двух значений вычисляется часть полного года, определяемая заданными начальной и конечной датами.

=ДОЛЯГОДА(A2;B2)

Чтобы получить процентное представление оставшейся части года, следует просто вычесть из единицы возвращаемое функцией ДОЛЯГОДА() значение.

=1-ДОЛЯГОДА(A2;B2)

#дата #ДОЛЯГОДА #YEARFRAC

Проверка выполнения простого условия. Функция ЕСЛИ (IF).

Функция ЕСЛИ() - это фундаментальная функция проверки условий в Excel. Она имеет три аргумента:
- условие;
- действие, когда условие выполняется;
- действие, когда условие не выполняется.

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

=ЕСЛИ(B2>СРЗНАЧ(B$2:B$6);"Высокая";"Низкая")

#условие #простоеусловие #ЕСЛИ #IF

Суммирование значений, которые отвечают определенному условию. Функция СУММЕСЛИ (SUMIF).

Отвечаем на вопрос нашего подписчика. Для того чтобы просуммировать значения по условию, например все положительные или все отрицательные значения, необходимо воспользоваться функцией СУММЕСЛИ(), тогда формула будет иметь следующий вид:

=СУММЕСЛИ(A1:A8;">0")

#суммирование #сумма #суммированиезначений #СУММЕСЛИ #SUMIF

Ввод значений, не превышающих суммы.

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

=СУММ($B$2:$B$6)<=$F$1

#проверкаданных #сумма

Выделение ячеек на основании дат. Условное форматирование.

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

=ИЛИ(ДЕНЬНЕД(A1)=1;ДЕНЬНЕД(A1)=7)

Англоязычный вариант формулы:

=OR(WEEKDAY(A1)=1;WEEKDAY(A1)=7

)

#условноеформатирование #выделениедат #выделениевыходных #ДЕНЬНЕД #ИЛИ

Выделение дней между двумя датами. Условное форматирование. Функция И (AND).

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

=И(D1>=$A$2;D1<=$B$2)

Англоязычный вариант формулы:

=AND(D1>=$A$2;D1<=$B$2)

#условноеформатирование #выделениедат #И #AND

Создание отношений между таблицами в модели данных Power Pivot.

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

#PowerPivot #отношения #отношениятаблиц #связи

Сравнение двух диапазонов на отличия и совпадения. Условное форматирование.

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

#сравнение #совпадения #сравнениедиапазонов #условноеформатирование