Planificateur automatique de décalage de calendrier dans Excel

¡Hola a todos! En uno de los videos anteriores,hablé sobre cómo puede crear un calendario mensual completamente dinámicoen Excel usando fórmulas simples y formato condicional. En estevideo, daremos un paso más y crearemos uncalendario universal para todo el año basado en este calendario, que le permite programar ymostrar automáticamente los turnos de trabajo, teniendo en cuenta los fines de semana, los días festivos y cualquierotro usuario . días especiales definidos . Para crear uncalendario de este tipo en el video tutorial de hoy, seguiremos el siguienteplan: en primer lugar, prepararemos todas las tablas inteligentes auxiliares parael cálculo automático de los turnos de trabajo y para ingresarfechas especiales individuales en el calendario. En segundo lugar, extenderemos el calendario actual por otros 11meses para terminar con un calendario completo para el año. Tercero, crearemosuna lista principal de fechas. Mostrará automáticamente los datos de cadadía individual del año seleccionado, en base a una lógica especial y a lastablas auxiliares preparadas en el primer paso . Bueno, en elúltimo y cuarto paso, configuraremos todas las reglas de formato condicional que necesitamos, para que Excel pueda pintar visualmente los días enel calendario de acuerdo con sus características, indicadas principalmente porlistas de fechas. Bien, este es el plan que tenemos frente a nosotros y pasemos a suimplementación. Comenzaremos a trabajar al mismo tiempo desde el punto donde nos detuvimos en elúltimo video: este es este calendario. El primer paso, como se dijo,debemos preparar todas las mesas inteligentes auxiliares. Habrá cuatro de ellos, y el primerode ellos es establecer el formato de semana en el calendario. En otras palabras, la configuración de enqué día debe comenzar la semana. Por el momento, esta configuración, digamos, estácosida directamente en la primera fórmula del calendario, aquí mismo. El deuce dice que lasemana debe comenzar el lunes. Si reemplazamos los dos conuno, entonces el primer día de la semana en el calendario, como se indica en la lista,se convierte en domingo. El principio de funcionamiento de este calendario, y en particular estafórmula, lo analizamos en detalle en el último video, aquí, por lo que si aúnno ha visto este video, definitivamente le aconsejo que lo vea: será muchomás fácil de entender . lo que estamos haciendo el video de hoy. Entonces, dado que en el futurocrearemos 11 copias más de este rango para mostrar el calendario detodo el año, sería bueno mover la configuración de formato de semana definida estáticamentefuera de la fórmula para que pueda cambiarse convenientemente en un lugar en una vez por todos los 12 meses delcalendario futuro . Eso es lo que vamos a hacer ahora. Selecciono la celda J2 e ingreso el siguiente encabezado: "Primer día de la semana". Enla única celda de esta tabla, como se dijo, almacenaremos la configuración delprimer día de la semana en el calendario. Para comenzar, pongamos solo uno en la celda de abajo. está bien. Ahora seleccionamos ambas celdas y necesitamos traducirlas a unformato de tabla inteligente. Puede hacer esto aquí, en la pestaña "Inicio", "Formatear como tabla"y seleccionar uno de los estilos, o puede usar el atajo de teclado.Prefiero usar los atajos de teclado de tal manera que uso el atajo Control + T,en la ventana que se abre, debe asegurarse de que la casilla estémarcada aquí y luego puede hacer clic en "Aceptar". El rango se convierte con éxito alformato de tabla inteligente. Cambiemos inmediatamente el nombre de esta tabla . Al seleccionar una de las celdas de estatabla inteligente, en la pestaña "Diseñador de tablas" en este campo, cambiamos el nombre.Que sea, por ejemplo, UT, es decir, "Mesa inteligente" y a través de los espacios inferiores"Configuración del formato de la semana". Ingresar. ¡Listo! También alineemos el contenido de las celdasde esta tabla a la izquierda y al centro verticalmente. ¡Multa! La primera tabla auxiliarestá lista y, como recordará, la creamos para traerla configuración del formato de semana de la fórmula de esta celda a la hoja de trabajo; lo haremos en elsiguiente paso. La forma más fácil de hacerlo es esta. Seleccionando la celda con el primerlunes del calendario, en la barra de fórmulas, justo aquí, necesitamos encontrar la función DÍA DE LA SEMANA,aquí está. Simplemente hacemos clic en el nombre de esta función yse muestra a continuación una información sobre herramientas con sus parámetros. Hacemos clic en el parámetro "Tipo" y Excelpreselecciona el valor de la configuración deseada para nosotros. Ahora, a su vez, solonecesitamos eliminar el valor actual ingresado estáticamente: uno.Para hacer esto, presiono la tecla Eliminar y luego debemos hacerclic izquierdo en el valor único de la tabla auxiliar preparada, como resultado de lo cual se inserta dicho enlace estructurado. ¡Súper!Presiona Enter y listo. Ahora, gracias a la corrección realizada, la eleccióndel primer día de la semana se hace en este rango externo, y no directamente enla fórmula del calendario, por lo que, si ahora introduzcoaquí como ajuste un dos, como era al principio, y presione Entrar, luego elcalendario se mostrará inmediatamente cambia a lo que estamos acostumbrados. El primer día de la semana es ahoralunes. Así que dejamos esta configuración . ¡Súper! Hemos preparado completamente laprimera mesa auxiliar y estamos empezando a crear la segunda. En él leindicaremos a nuestro calendario cuántos y por cuánto tiempo necesitamos losturnos de trabajo planificados. Empecemos de nuevo escribiendo los encabezados de las columnas de la tabla. La primera columna se llamará "Número de turnos" y la segunda sellamará "Duración del turno". Como ejemplo, primero configuremos nuestro calendario para mostrar tresturnos de dos días . Inmediatamente, de nuevo, traducimos esterango a un formato de tabla inteligente. Lo selecciono , Control + T, me aseguro de que la casilla de verificación esté marcaday presiono Enter. Inmediatamente cambie el nombre de la nueva tabla inteligente.Llamémoslo "UT_Setting_Change". Ingrese en la pestaña "Inicio",corrija la alineación y otra tabla está lista. Probaremoscómo funciona esta tabla más adelante, así que por ahora avancemos y comencemos acrear la tercera tabla auxiliar. Aquí es donde se pone más interesante. En élprogramaremos los principales tipos de días. Primero mostrémoslo y luegoexplicaré lo que hará esta tabla. Así, constará de cuatrocolumnas: "Número", "Día de la semana", "Tipo de día (Principal)", y también "Contabilidad de la jornada laboral".En la columna de números, debemos ingresar números del uno al siete, y en la columna “Día de la semana”,respectivamente, escribimos el nombre de los días. Ahora tambiéntraducimos este boceto de la mesa de la forma que ya conocemos al formato de una mesa inteligente. Cambiemosla alineación del contenido y establezcamos el nombre: será "Ut_Basic_Types_of_Days". ¡Excelente! Ahora analicemos para quése usará esta tabla. En él, almacenaremos indicaciones del tipo principal deldía de la semana. Se refiere a la indicación de una determinada característica del día, quese le asignará a la fecha en función del día de la semanaen que se encuentre. En el ejemplo de hoy, y probablemente en la mayoría de loscasos prácticos de uso de este calendario, los principales tipos de días obligatoriosserán los laborables y los fines de semana. Como primer ejemplo, designemos losdías laborables de lunes a viernes,y sábado y domingo como días de descanso. ¡Súper! Y solo tenemosla última cuarta columna de esta tabla en blanco. Como sabe, en lospróximos pasos crearemos un sistema que distribuirá automáticamente losturnos de trabajo por día. Para ello, será necesario que este sistema sea capaz deintroducir un cómputo acumulativo de días laborables, y precisamente para esta tarea hemospreparado una cuarta columna denominada "Conteo de días laborables". En él, designaremos por defecto los días de la semana que se cuentan por unidad, y los que no secuentan por cero. está bien. Es muy posible que aún no comprenda bienpara qué está destinada esta columna, y no se preocupe, esto es bastante normal.En tan solo un par de minutos verás claramente qué papel juega y cómo le vamosa dar uso, pero de momento pasemos a preparar la últimamesa auxiliar que queda. Entonces, coloquemos la cuarta mesa auxiliartambién debajo, comenzando desde aquí. Los encabezados serán: "Descripción","Fecha", "Tipo de día (Opcional)" y similar al cuadro anterior"Contabilidad de la jornada". En esta tabla, ingresaremos manualmente días individualesde un tipo adicional, como días festivos. Como primer ejemplo, ingresemosel año nuevo. Descripción "Año nuevo", fecha 1 de enero de 2022, escriba "Vacaciones" y teniendo en cuentael día hábil, ingrese 0. También convertiremos inmediatamente este rango en unformato de tabla inteligente, le cambiaremos el nombre y corregiremos la alineación del contenido. está bien. Yahora esta tabla nos permitirá agregardías especiales en el calendario de reflexión en el futuro e indicar cómo el sistema debe tener en cuenta estos días al calcular los turnos .Tomemos un ejemplo. Aquí está marzo completando tresturnos de dos días sin mostrar días festivos. Si, gracias ala última tabla auxiliar añadida , añadimos el 8 de marzo a la lista de días especialescomo festivo y ponemos 0 en la columna "Contabilización del día laborable", entonces el calendarioentenderá que ese día no es laborable, por lo tanto lo marcará como feriado,y también se hará un turno publicando un día antes de todoslos turnos de trabajo afectados. Y este es solo un ejemplo del uso de estamesa auxiliar. Otros ejemplos incluyen la visualización dereemplazos de turnos en él, por ejemplo, cuando en un día determinado, bajo ciertascircunstancias, un turno debe reemplazar a otro. También puede nombrar,por ejemplo, el uso de esta tabla para indicar vacaciones corporativas, etc. En general, esta tabla permite agregarciertos eventos necesarios al calendario, incrustándolos convenientemente en todo el sistema. Nuevamente,esta fue solo una breve explicación para que comprenda lo que estamos haciendo ydefinitivamente le daremos más ejemplos ilustrativos tan pronto como nuestro calendariotome su forma final, así que continuemos ypasemos al siguiente paso: agregar 11 más meses en el calendario. Para ello , primero, vamos a mover todas lasmesas auxiliares a la derecha. Selecciono toda lacolumna Me gusta esto y empiezo a agregar. Una columna para el espacio entre los futurosenero y febrero utilizando la combinación de teclas Control++, luego 7 columnas máspara febrero y, en consecuencia, todos los meses por debajo. Nuevamente, unacolumna adicional para el espacio con una tercera fila de meses y 7columnas finales para el mes de marzo y similares. En total, agregué 16 columnas nuevas, yen solo un par de minutos las llenaremos con los meses que faltan, copiandoeste rango y haciendo pequeñas correcciones en sus fórmulas. Sin embargo, antes de eso, debemosrealizar un par de acciones más, a saber, debemos corregir las reglas deformato condicional de este rango que quedaron del último video tutorial, debemoscambiar una fórmula en él, reemplazar la combinación de encabezado celdascon alineación central, y también necesitamos agregar un encabezado general que indiqueel año para todo el calendario. Comencemos por deshacer esta combinación de celdas. Después de seleccionarel título como este, haga clic en la pestaña "Inicio" en el botón "Combinar y centrar".Después de eso, todos con el mismo rango seleccionado, abra laventana de formato de celda con la combinación de teclas CTRL + 1, y aquí en la pestaña "Alineación", enel elemento "Horizontal", seleccione el valor "En el centro de la selección". Bien, ahora tenemos el encabezado delmes perfectamente centrado así . El siguiente paso es agregar unencabezado general que indique el año para todo el calendario futuro. Seleccionamos la celda B2,es en ella donde ingresaremos las indicaciones del año requerido como una de las fechas, eingresaremos, por ejemplo, el 1 de enero de 2022. Ingresar. Tampoco hay suficiente espacio para la fecha ingresada en la celda, por lo que una vez más alinearemos la ubicación del contenido en esta celda conel centro de la selección. Seleccionamos el rango con el ancho del calendario futuro de esta manera: estas soncolumnas de B a X, use la combinación de teclas CTRL + 1 y en lapestaña "Alineación" seleccione nuevamente la configuración de alineación "Selección central".Ingrese y ahora tenemos el valor de la celda alineado así. En el siguiente paso, en lugar de mostrar la fecha aquí, configuraremos la visualización de solo el año.Nuevamente, mantenga presionada la tecla Control, presione uno e ingrese en la ventana que se abre en lapestaña "Número" en el elemento "Todos los formatos" un formato de número personalizado: 4 letras"G". Ingrese y ahora tenemos solo el año en el título de todo el calendario. Muy bien.Hagamos que la indicación de este año sea más grande y audaz.¡Multa! Ahora corregimos el rango de este primer mes, de momentoautomáticamente es febrero, porque posteriormente tendremos que copiarlo paramostrar los meses restantes. Primero, eliminemos o corrijamoslas reglas de formato condicional aplicadas al rango. Seleccione una de las celdas deeste primer mes y en la pestaña "Inicio", haga clic en elgrupo de comandos "Formato condicional". En la lista desplegable de comandos, seleccione la opción "Administrar reglas".Como puede ver, tenemos estas dos reglas deformato aquí. Eliminaremos por completo la primera regla para resaltar el día actual ycorregiremos el formato de la segunda regla. Haga doble clic en él con el botón izquierdo del mouse y enla ventana que se abre, haga clic en el botón "Formatear". Actualmente, esta reglahace que la cantidad de días que no están relacionados con el mes que se muestra sea tantransparente. Sin embargo, para todo el calendario anual, propongo una enmiendapor la que el número de esos días se escriba en letra blanca, para que semezcle con el fondo blanco. Para implementar esto, en la pestaña “Fuente”, donde estamoseligiendo, el color es blanco, confirmamos todos estos cambios haciendo clicen Aceptar y ahora vemos el resultado deseado. Además, dado que el año ahora semuestra en el encabezado general, puede eliminarlo con seguridad del encabezado del mes .Seleccionamos el título de este primer mes, se ingresa aquí, usamosla combinación de teclas CTRL + 1, cambiamos el formato de número de las celdas de estaopción a solo cuatro letras grandes "M". Haga clic en Aceptar y ahorasolo se indica el mes en el encabezado del mes. ¡Súper! ¡Multa! Como enmienda final, ahora,de hecho, cambiaremos la fórmula escrita en el encabezado del mes. Ahora tenemos unafunción HOY aquí. Necesitamos cambiarlo, por lo que al seleccionar una celda,ingresamos un signo igual e ingresamos a la función DATAMES. Con él, nos será más fácilmostrar posteriormente los meses restantes. Abra los corchetes yespecifique la celda B2 en el primer parámetro de la función, e inmediatamente arreglaremos el enlace en su totalidad. Parahacer esto, presione la tecla F4. Después de eso, coloque un punto y coma e ingrese 0. Cierreel paréntesis, ingrese y, como puede ver, ahora nuestro primer mes es enero,independientemente de la fecha actual. Esto se debe al hecho de que nos deshicimos deesa función HOY. ¡Súper! Para la posteriorcopia más conveniente de este rango, todo está preparado de tal manera que ahora copiamosel rango de enero así en su totalidad junto con el título y, seleccionando la celda J3,pegamos una copia del rango copiado. Control+V. Ahora haga clic en Escape.Ahora, en el rango copiado, seleccione la celda J3 por separado y presioneF2 para ingresar al modo de edición para el contenido de esta celda. Aquí cambiamosel enlace de la celda B2 a la B3, es decir, al encabezado del mes anterior. Al mismo tiempo,esta vez necesitamos un enlace compensado, por lo que no necesitamos símbolos de dólar. Necesitamosun enlace tan limpio B3. Bueno, al final, también reemplazamos 0 con uno. Todo,todo está listo, entonces presionamos Enter y obtenemos este resultado. En realidad, está bien. Por el momento, los días de febrero no se muestran porque,lamentablemente, las reglas de formato condicional al copiar no saben cómocambiar los enlaces absolutos, en nuestro caso, un enlace a un nuevo encabezado, por loque ahora, y más adelante, al añadir otros meses, tendremos quehacer esta corrección manualmente. Esto se hace seleccionando uno de los días delmes deseado, por ejemplo, en relación con febrero, en algún punto intermedio,abra la lista de reglas de formato y haga doble clic en esta regla con elbotón izquierdo del mouse. Aquí en la fórmula cambiamos el primer enlace de B3 a J3, es decir, alencabezado de febrero, Enter, Enter y ahora todo parece simple. ¡Multa! A continuación, repetimos el proceso para marzo, es decir, copiamos el rango de febrero y lo pegamosaquí en la celda R3. El enlace en la fórmula del encabezado se hadesplazado automáticamente de la forma correcta, por lo que todo lo que tenemos que hacer es corregir lasreglas de formato condicional. Voy a "Formato condicional","Administrar reglas", hago doble clic en la única regla disponible aquí yreemplazo el primer enlace de la celda J3 a R3. Los próximos meses ya se puedencopiar en una fila completa. Seleccionamos tres meses de la primera fila así a la vez ylos insertamos directamente debajo de los primeros tres. La fórmula ahora debe cambiarse solo paraeste bloque. Cambiamos el enlace al encabezado de marzo, eliminamos los símbolos de dólar con latecla F4 y reemplazamos 0 con uno. Ingrese y ahora, como ya lo hemos hecho, corregimoslas reglas de formato condicional , solo que esta vez necesitamoscambiar el número de línea en el primer enlace al número 11 para cada regla.¡Súper! Окей, ну и аналогичным образом, собственно, завершаем календарьдобавлением еще двух рядов месяцев, поправляя лишь правила условногоформатирования. В правилах условного форматирования тертьего ряда нужно в первойссылке менять число с 11 на 19, а в четвёртом ряду в свою очередь нужно впервой ссылке сменить номер строки 19 на строку 27. Супер! Что ж, в качествепоследней поправки я, пожалуй, еще немного увеличил бы горизонтальный пробел междумесяцами. Для этого вот так выбираем зажав Control все строки с заголовкамимесяцев и сделаем их повыше. Щелкаю для этого по номеру одной из выбранных строкправой кнопкой мыши и выбираю настройку «Высота строки». Выставлю значение, например,25 и подтверждаю изменение. Супер! Календарь полностью отображен и готов кпоследующим рабочим шагом, а следующим шагом у нас, как Вы помните, являетсясоздание основного списка дат. Он у нас будет расположен правее вспомогательныхтаблиц, вот здесь. Давайте приступим к его созданию. Начнем опять-таки с заголовков.Они будут такими: «Номер», «Дата», «Тип дня», «Учёт дня», «Накопительный счет», а также«Смена / Тип дня». В этой таблице, как и было сказано, будут отображаться все днигода, поэтому следующим шагом зададим ей нужный размер. В году у нас обычно 365дней, тем не менее, также нужно не забывать и високосных годах, в которых наодин день больше, именно поэтому вносим в этой ячейке АЕ3 значение 1 и, выбравего, во вкладке «Главная» щелкаем по группе команд «Заполнить». В открывшемся спискевыбираем команду «Прогрессия». Нам нужно возрастающее заполнение ячеек выбранногостолбца вплоть до значения 366, поэтому в расположении выбираем опцию «По столбцам»и предельное значение вносим 366. Ok. И мы только что таким образом задалиправильный размер нашей умной таблице, так, что теперь давайте выберем весьдиапазон, выбираем для этого ячейку АЕ2, вот эту, зажимаем клавиши Control, Shiftи нажимаем клавишу со стрелкой вправо. После этого все также держа зажатымиControl и Shift нажимаем стрелку вниз. В результате вот так целиком выбираетсядиапазон будущей умной таблицы, и мы с помощью уже знакомой нам комбинацииклавиш CTRL+T переводим этот диапазон в формат умной таблицы. Названиеей зададим такое: умная таблица, список дней. Ну и поправим сразу выравнивание. Супер!Таким образом мы наконец подошли к, пожалуй, самой интересной частисегодняшнего видеоурока - к построению логики отметки дней в календаре и кзаполнению этой основной таблицы. Начнем давайте с того, что заполним столбец «Дата».Делать мы это будем естественно в автоматизированном формате с помощьюформулы. Для этого нам нужно, во-первых, прописать такую функцию, которая всегдавозвращала бы, значение первого числа выбранного года. Как Вы уже знаете,выбранный год у нас прописан вот в этой ячейке B2 в формате даты, поэтому мыможем сейчас сделать следующее: выбираем первую ячейку значений столбца «Дата»,ставим знак равно и вписываем функцию ДАТА. Открываем скобку и в качествепервого аргумента прописываем функцию ГОД, в рамках которой, открыв скобки,вставляем ссылку на ячейку B2. При протягивании формулы вниз данная ссылкане должна смещаться, то есть нам нужно её закрепить, для этого используем клавишуF4. Супер! Закрываем скобку, ставим точку с запятой и в оставшихся двух параметрахфункции нужно просто внести единички. Вот таким образом. Снова закрываем скобки ипопробуем для начала ввести эту формулу, чтобы посмотреть на промежуточныйрезультат. Жму Enter, в рамках умной таблицы формулы протягиваютсяавтоматически и в итоге у нас весь столбец дат заполняется первым числомвыбранного года. Окей. Половину функционала этой формулы мы прописали ипоэтому продолжаем написание всей формулы. Выбрав первую ячейку, нажимаю наклавишу F2, чтобы войти внутрь ячейки для редактирования её содержимого. Этуформулу мы сейчас допишем так: вписываем в конце символ плюса и вводим ссылку науказанную слева ячейку с возрастающим номером дня. Если мы нажмём на Enterсейчас, то, как видим, у нас список дней будет начинаться со второго числа,поэтому все, что остается сделать — это отнять в конце формулы единицу. Снова спомощью F2 входим в режим редактирования содержимого первой ячейки и вписываем наконце формулы -1. Enter и сейчас результат выглядит намного лучше. Теперь в этомстолбце перечислены все даты выбранного года. Единственным пунктом, который бы яеще поправил, является следующий, смотрите. Если выбранный год не високосный, то вкачестве 366 дня в самом конце таблицы будет указан первый день следующего года.Это не критично, но, чтобы все было совсем красиво поправим и этот пункт. Сновавыберем первую ячейку столбца, F2, курсор у нас сейчас расположен в конце формулы,так что зажимаем Shift и щелкаем по клавиши Home, в результате чеговыбирается весь текст вплоть до самого начала содержимого ячейки. Теперь всетакже держа зажатой клавишу Shift щелкаем по стрелке вправо, чтобы убратьиз выбора символ равенства. Копируем выделенную формулу и щелкнем стрелкувлево, чтобы поставить курсор в начале формулы. Здесь теперь пишем следующее:во-первых, носим функцию ЕСЛИ, открываем скобки и вписываем формулу ГОД. Сноваоткрываем скобки далее нажимаем на клавишу End, чтобы перейти в конец строкиформулы. Здесь вводим сперва одну закрывающую скобку. Получается вся этачасть формулы будет возвращать год выводимой в самой этой ячейке дат.Именно поэтому для проверки года этой даты ставим в конце знак равно ипрописываем опять функцию ГОД, только в этот раз в качестве входного аргументав скобках указываем ячейку B2. Сразу закрепляем эту ссылку нажатием клавишиF4 и закрываем скобку. Получается сейчас мы сказали: если год даты самой этойячейки равен году вот этой даты, то, ставим точку с запятой, и используемкомбинацию клавиш CTRL+V, в результате чего вставляется скопированная тогдачасть формулы. Далее опять вводим точку запятой и в последнем параметре функциипросто в кавычках введем символ минуса. Готово. Закрываем последнюю пару скобок,Enter, и теперь столбец будет у нас всегда содержать даты лишь выбранногогода. Вот смотрим: в триста шестьдесят шестой строке дата не отображена ивнесён минус. Если же мы выберем теперь отображение високосного года, следующийтаковой год — это 2024, то сразу, как видите, у нас поправляется, во-первых, самкалендарь, а во-вторых, столбец «Дата» в списке дней все так же правильнозаполняется, не забывая и особый 366 день года. Супер! Качестве рассматриваемого дляпримера года давайте я, однако, пока верну на место 2022 год. Что ж, супер! Второйстолбец мы здесь заполнили так, что переходим к следующему. В нём должнывыводиться типы дней, указанных слева дат. При этом смотрите, логика здесь будетследующая. Во-первых, в таблице должна происходить проверка, а не перечислены ли указанныеслева даты в таблице особых дней, вот здесь. Если дата там и вправдуперечислена, как в случае с первым январем, собственно, то должен выводитьсяуказаны для даты в этой же таблице дополнительной тип дня. Если жепроверяемая даты в этом списке отсутствует, то пусть выводится основнойеё тип в соответствии с находящейся выше таблицей. В нашем текущем примере — этоуказание того, является ли день рабочим или выходным. Формулу с подобнымфункционалом можно прописать по-разному, например, с помощью функции ВПР,комбинации функций ИНДЕКС и ПОИСКПОЗ, или же с помощью новой функции ПРОСМОТРX. Ябуду в последующем в рамках этого видео урока везде использовать именно функциюПРОСМОТРX, так как она позволяет быстрее прописывать формулы с нужнымфункционалом. При этом я понимаю, что, поскольку эта функция стала доступнойвсем лишь с версии Microsoft Office 2021 года, вероятно у многих зрителейиспользовать её не получится, именно поэтому по написании формул, содержащих всебе функцию ПРОСМОТРX, я буду также выводить на экране их аналог сиспользованием универсальной комбинации ИНДЕКС и ПОИСКПОЗ. Ok. Давайте приступим!Ставлю знак равно, и начинаем писать наименование функции ПРОСМОТРX, кактолько она выводится в предлагаемых функциях, предвыбираю её клавишей сострелкой вниз и жму на Tab. Excel автоматически правильно дописываетнаименование этой функции. В первом параметре функции указываем, что мы будемискать — это вот эта дата. Во втором параметры говорим, где мы эту дату будемискать — это столбец со списком особых дней, вот так его выделяем, и в третьемпараметре говорим, значение из какого диапазона должно возвращаться. Этостолбец с указанием дополнительного типа дня. Супер! Снова ставим точку с запятой ив следующем необязательном параметре функции мы можем сказать, что должновыводиться, если искомая дата в этом списке не была найдена. И как Вы думаете,что мы здесь введем? Правильно! Опять функцию ПРОСМОТРX. В этот раз мы однакобудем искать не саму указанную слева дату, а номер дня недели, для этогопишем ДЕНЬНЕД, вот эта функция, TAB, в ней указываем дату и через точку с запятойговорим, какой нам нужен вариант нумерации дней. Нам нужен второй вариант,закрываем скобку и вносим точку с запятой, ну и после этого говорим: номер днянедели этой даты мы будем искать вот здесь. Возвращаться должно указаниеосновного типа дня. Дважды закрываем скобки, жмем на Enter и смотрите, какойотличный результат мы видим. Теперь особые дни перечисленные, вот в этойтаблице, отмечаются соответствующим дополнительным типом дня вот, например, 1января и 8 марта отмечены, как праздники, а все остальные обычные дни помечаютсялибо как рабочие, либо как выходные в соответствии с их днем недели. Супер!Таким образом еще один столбец готов и мы переходим к подготовке следующего.Для него кстати формулу мы сейчас пропишем очень быстро, ведь онафактически будет аналогичной вот этой уже прописанной слева формуле. Давайте еёкак раз целиком скопируем и вставим в первой ячейке столбца «Учет дня». ЗажимаюCTRL и Shift, нажимаю клавишу Home, выделяется вот так вся формула, CTRL+C,выделяю первую ячейку столбца «Учет дня», нажимаю F2 и здесь вставляюскопированный текст формулы. Окей и сейчас нам остается внести лишь тримельчайшие поправки. Во-первых, ищем мы здесь все так же дату, указанную левее встолбце дат особых дней, вот только возвращать нам нужно не типдополнительного дня, а столбец со статусом учета рабочего дня, так чтовыбираем вот так название ненужного столбца, во-первых «Тип меня дополнительный»и удаляем его, а после начинаем писать слово «Учет». После первыхдвух букв Excel сразу предлагает нам к выбору вот такой столбец, так чтонажимаем на Tab и наименование предложенного столбца автоматическицеликом дописывается за нас. Отлично! Во вторых, вот здесь заменим столбец «Тип дня основной»на также столбец «Учет рабочего дня». Все. Enter и видим, если день рабочий,то у нас здесь вписывается единичка, если выходной, то нолик. При этом 8 марта у насэто вторник, основной тип этого дня недели рабочий, но, поскольку 8 мартасогласно данной таблице - день праздничный, в основном списке дней, в столбце «Учет рабочих дней»выводится 0. Просто отлично! Что ж, остаются всего два столбца.Продолжаем. Формула следующего столбца будет крайне простой. Смотрите, что мы вней напишем. Ставим знак равно и вносим функцию ЕСЛИ. В ней, открыв скобки, пишем:если значение ячейки левее равно нулю, то, точка запятой, то пусть ничего невыводится, открываем и закрываем кавычки. В противном случае, точка запятой, суммируем,пишем СУММ, открываем скобки и здесь указываем следующий диапазон. Щелкаем позаголовку столбца учета дней, затем вносим двоеточие, при этом Excel, как у менясейчас это и произошло, иногда не дает сразу вставить двоеточие при введении ссылокв формулах. Вот попробую еще раз. Зажимаю Shift и нажимаем на цифру 6 для введениядвоеточия, но ввод не срабатывает. Если у Вас такая же ситуация, то просто внесите сперва,например, символ процента, сотрите его и затем снова попробуйте вставить двоеточие. Вот:Shift 6, и видим: обычно так сразу все срабатывает. Ok. И вот теперь мы наконецвведя двоеточие щелкаем по соседней ячейке слева, то есть данная формула, притягиваясь внизбудет протягивать за собой и указанный в рамках столбца «Учет дня» диапазон. Ok,собственно, формула уже целиком прописана, так что закрываем одну пару скобок, сразуже вторую и жмем на Enter. В итоге у нас протягивается вниз счет лишь рабочихдней. При этом смотрите: опять-таки будет интересно посмотреть на 8 марта. ЕгоExcel, как праздник, в нумерации вот так перескакивает и продолжает возрастающейсчет лишь после него. Просто супер! Что ж, нам остается заполнить лишь последнийстолбец и в нём мы сейчас пропишем просто отличнейшую и очень интересную посвоему функционалу формулу. Итак, смотрите. В этом столбце мы должныпрописывать смену указанной длительности, если день рабочий, а еслиже день является выходным, праздником или днем любого другого дополнительного типаиз данной таблицы, то должен выводиться соответствующий дополнительный тип. Дняподтягивание дополнительных типов дней при этом вовсе не является сложностью.По-настоящему интересной является реализация метода вычисления сменправильной длительности, и ключом к разгадке этой задачи являются двефункции ОСТАТ и ЧАСТНОЕ. Давайте пошагово разберемся, и спервапротестируем. Для начала испробуем функцию ЧАСТНОЕ. Эта функция позволяетполучить целую часть результата деления с остатком. На слух это достаточно сложнопонять, поэтому давайте я приведу наглядный пример. Ставлю знак равно, пишуЧАСТНОЕ, открываю скобку и лишь в рамках примера для большей наглядности вкачестве первого параметра давайте введём вот это пошагово возрастающеезначение номера дня. Ставим точку с запятой и в качестве второго аргументавведем для начала, например, двойку. Закрываем скобку, нажимаем на Enter и унас выходит вот такой результат. Фактически эта функция нам сейчасговорит, сколько целых делителей, в нашем случае двоек, вмещается в делимое число,то есть смотрите, например, в случае первого числа - единицы, ни одна двойка целиком неможет в ней уместиться. Поэтому мы получаем результате вычислений 0. В двойкеже как раз ровно 1 целая двойка и умещается, поэтому получаем единицу. Втройке, то же самое, в ней умещается лишь одна целая двойка, соответственно, ирезультат единица. В четверке же умещаются уже две целых двойки, и такдалее. Окей, так мы считай разобрали принцип работы функции ЧАСТНОЕ. При этомдля нас сейчас важно то, что с помощью этой функции мы фактически получаемпостоянно возрастающее значение с заданным количеством повторений каждогоотдельного. Числа вот две единицы, две двойки, две тройки и так далее. Еслив функции делитель двойку мы заменим тройкой, то выходит аналогичная прогрессия,только содержащая уже по три повторения каждого числа. Вот три единицы, три двойки,три тройки и так далее. Кстати, Вы вероятно заметили: у нас всегда нехватает одного повторения нуля. Исправить это можно крайне просто. Надо лишь внестивот здесь, после ссылки на значение номера строки, минус 1. Enter и вот все работаетровно как нам нужно. Теперь абсолютно все встречаемые здесь числа повторяютсяодинаковое количество раз, включая и ноль. Супер! Смотрите, чтобы еще разудостовериться внесем четверку, и сразу чередуются четверки чисел. Внесем обратнодвойку, идут пары и так далее. Таким образом получается мы только что с Ваминашли метод для программирования длительность будущих рабочих смен. Пока,однако, наша система с помощью лишь функции ЧАСТНОЕ создает бесконечноеколичество смен, то есть вот нулевая смена длиной в два дня, первая смена длиной в 2дня, вторая, третья, четвертая и так далее. Нам же нужно, чтобы система чередовала заданное количествосмен определенной длины, то есть, например, 1 смена, 2 смена, 3 и снова первая, вторая, третья и такдалее. Давайте решим эту задачу. Я уже успел назвать Вам функцию ОСТАТ. Сейчасименно она нам и понадобится. Смотрите в чем заключается принцип её работы.Функции ОСТАТ возвращает остаток от деления. Вот введу эту функцию, в качествепервого аргумента для примера указываю все также поочередно возрастающее вот вэтом столбце значение, в качестве делителя для начала давайте зададимтройку, закрываем скобку, Enter, и видим вот такой результат. Уже выглядит вполненеплохо. Давайте. Теперь пошагово произведем вычисления, производимыефункцией ОСТАТ вот здесь правее, чтобы лучше понимать принцип её работы. Итак,функция ОСТАТ, во-первых, берет указанное число и делит его на заданныйделитель, в нашем случае — это была тройка. Давайте я сразу протяну эту формулу вниз,чтобы было сразу множество примеров. Ok. Далее отбросим часть результата деленияпосле запятой. Мы это можем проделать с помощью функции ОКРУГЛВНИЗ, задавв качестве целевого числа разрядов 0. Если обновим формулы в столбце, то смотрите,что мы получаем. Ничего не напоминает? Верно, мы только, что с Вами вручнуювоспроизвели те вычисления, которые производит уже разобранная нами функция,ЧАСТНОЕ, то есть на данном этапе мы фактически с Вами опять получили ответна вопрос, а сколько целых делителей умещаются в каждом из вот этих чисел. Тоесть, например, в единице может уместиться ноль троек, все верно, в двойке все также0, а вот в тройке как раз вмещается ровно один целый делитель. В четверке уменьшаетсятакже лишь один целый делитель, как собственные и в пятерке, а вот в шестеркемогут уместиться сразу две целых тройки. И так далее этот принцип нам, собственно,уже знаком из разбора функции ЧАСТНОЕ. В качестве же новой информации мы здеськак раз можем понять, как работает функция ОСТАТ. То есть смотрите, если мысейчас умножим вот это полученное значение на делитель, то у нас выходятвот такие суммы. И вот если мы теперь От делимого эти суммы и отнимем, тосмотрите: мы как раз получаем результат вычислений функция ОСТАТ. То есть функцияОСТАТ говорит, какая часть делимого остается незатронутой при наибольшемвозможном целочисленном делении. В итоге имеется ввиду вот эта часть. Вот и всё, мысейчас с Вами вручную целиком воспроизвели весь процесс вычисленияфункций ОСТАТ. При этом этот доскональный разбор мы делали лишь длятого, чтобы подробнее вникнуть в саму материю происходящих вычислений, а всухом же остатке нас интересует совсем другое. В сухом остатке нас интересуетвариант применения функции ОСТАТ для циклированного использования лишьзаданного количества рабочих смен, и смотрите, если мы взглянем сейчас нарезультат работы функции ОСТАТ внимательнее, то понимаем, что это как разто, что нам нужно. Если внутри функции мы отнимем у первого аргумента единичку, и наконце формулы единичку наоборот прибавим, то получаем вот такое чередованиефактически заданного количества однодневных смен. Смотрите, первая смена, вторая, третья.Первая, вторая, третья, и так далее. Если мы теперь укажем в качестве делителя четверку, то ожидаемымобразом получаем цикл из уже четырёх однодневных смен. Просто супер! Получаетсяфактически с помощью функции ОСТАТ мы получаем возможность программироватьколичество смен, а с помощью функции ЧАСТНОЕ их длительность. Таким образомключ к решению нашей задачи находится всего в шаге от нас, и запрятан он вприменении комбинацией этих двух функций. Так что давайте эту комбинацию,собственно, следующим шагом и пропишем. При этом не забываем: в последнем столбце унас должны обозначаться не только номера смен, но также праздничные и любые другиедополнительные типы дней, поэтому формулу мы пишем такую. Во-первых, смены мы будемвычислять лишь для рабочих дней так, что первым шагом вносим функцию ЕСЛИ иговорим: если значение этой ячейки равно слову «Рабочий» то, точка с запятой, и пока давайте внесемСлово-заполнитель PLACEHOLDER. В противном случае, точка запятой, пусть тут,собственно, и выводится указанный левее тип дня. Закрою скобку, нажму на Enter имы уже видим вполне неплохой промежуточный результат. Те ячейки, вкоторых прописано слово «Placeholder» в будущем будут содержать в себе указаниерабочей смены, а остальные дни уже отмечаются как праздники или выходные. Мына верном пути так, что продолжаем. Входим снова в содержимое первой ячейки и удаляемслово Placeholder. На его месте мы теперь пошагово пропишем правильныйалгоритм вычисления смены. Начнем с программирования длительности смены.Пишем ЧАСТНОЕ и открываем скобки. В скобках теперь в качестве числителя указываемнакопительный счет рабочих дней, а в качестве знаменателя указанную вот здесьдлительность одной смены. Закрываем скобки, и при этом обратите внимание.Чтобы формула протягивалась правильно, в этой структурированной ссылке нужноудалить символ собачки, иначе ссылка всегда будет происходить в рамкахтекущей строки. Окей, нажму Enter, и мы получаем вот такой результат сзапрограммированной длительностью смен. Единственное, как Вы помните, мы отнимали,у числителя единицу, чтобы, самая первая смена также имела правильнуюдлительность. Так что заходим снова в формулу и после ссылки на накопительныйсчет, вот здесь, вносим минус один. Enter и теперь длительность всех смен правильнопрописана, начиная с самой первой смены. Поскольку мы, однако, до сих пор невстроили в формулу функцию ОСТАТ, нумерация смен бесконечно возрастает, а нам нужно,как видим вот здесь, чередование лишь трёх смен. Так что продолжаем написаниеформулы. Входим опять в содержимое ячейки, щёлкнем по функции ЕСЛИ и выберем весьвторой параметр, вот так просто нажимаем на него в подсказках. В итоге выделяетсявот эта часть формулы, её целиком нам нужно обернуть в функцию ОСТАТ длябудущего программирования количества смен, так что ставим курсор вот здесь, вначале второго аргумента функции ЕСЛИ, пишем ОСТАТ и открываем скобки. Теперьперемещаемся в конец второго параметра функции, вот сюда перед точкой с запятой, ивносим еще одну точку с запятой. Здесь нам теперь нужно указать требуемоеколичество смен, оно у нас прописано в специальной вспомогательной таблицы, такчто вставляем ссылку на данную ячейку, закрываем скобку и также не забываемудалить символ собачки во внесенной структурированной ссылке. Посмотрим напромежуточный результат. Enter, и всё. Мы буквально в шаге от завершения написанияформулы этого столбца. Что нас сейчас не устраивает, так это то, что сменынумеруются, начиная с 0. Давайте это поправим. Решение при этом мы уже видели.Входим содержимое первой ячейки и после указания количества смен, вот здесь, вконце второго параметра вносим + 1. Enter и все готово. Смотрите третьего числа у наспервый рабочий день и с ним начинает работу первая смена. Она длится два дня ипосле нее идет вторая смена. Далее начинается 3 смена и она так скажемразрывается выходными, продолжаясь уже после воскресения. После чего опять идетпервая смена, вторая и так далее. Стоит нам сейчас внести изменения в настройки, вот здесь, икак всё моментально поправится. Давайте покажу. Например, внесу вместо трех смен, четыресмены. Enter, и видим. Теперь у нас после третьей смены, идёт 4 и лишь после неёснова первая. Также можно посмотреть, как будет выглядеть картина со все также четырьмясменами, но длительностью по три дня. Enter. Просто супер! Таким образом мы сВами только, что полностью подготовили основную таблицу, и теперь пораотобразить все выводимые в её последнем столбце типы дней в календаре. Длябольшей наглядности, однако, перед этим давайте зададим сменам длительность вдва дня, и вернем планирование лишь трёх смен. Окей, вводить теперь указанныездесь типы дней и смены для каждой даты в календаре мы будем с помощью правилаусловного форматирования. Это будет последним шагом по созданиюнашего календаря, после чего мы приведем пару примеров его применения и,собственно, закончим сегодняшний неимоверно длинный, но и крайненасыщенный урок. Итак, давайте приступим! Во-первых, следует понимать, какой логикемы сейчас будем следовать. В правилах условного форматирования мы будем братькаждую отдельную дату и смотреть, что для нее внесено в последнем столбце нашейосновной таблицы. На данный момент есть его несколько вариантов того, что можетбыть в этой таблице отображено — это одна из смен, указание, что день выходной или жечто день праздничный. И вот для этих значений нам нужно прописать правилаформатирования. При этом каждый раз мы фактически будем использовать почтиодинаковую формулу, у которой нужно лишь в конце заменить сравниваемое значение,именно поэтому, чтобы упростить процесс написания формулы предлагаю сперва еёпрописать где-нибудь на рабочем столе и в дальнейшем просто копировать её текст,и вставлять его в нужном поле в окне создания правила условногоформатирования. Так что ставим курсор где-нибудь вот здесь, вводим символ равнои пишем название функции ПРОСМОТРX. Она у нас выводится в предложениях, вот здесь,так что я опять клавишей со стрелкой вниз предвыбираю её и щелкаю поклавише Tab. Сразу функция дописывается, открываются скобки и здесь мы теперьвводим ссылку на ячейку B3. Окей. Точка с запятой и искать значение указанной датымы будем в столбце дат, вот здесь. Проще всего выбрать диапазон значений этогостолбца умной таблицы будет так. Выбираем одну из ячеек нужного столбца ииспользуем комбинацию клавиш CTRL+ ПРОБЕЛ. Excel выбирает всю областьзначений этого столбца. Теперь смотрите, важный момент: в дальнейшем прописываемуюсейчас формулу мы будем использовать в строке формул правила условногоформатирования, а там вот такие структурированные ссылкинапрямую, к сожалению, применяться не могут, именно поэтому этуструктурированную ссылку нам сейчас нужно обернуть в функцию ДВССЫЛ. Ставлю дляэтого курсор вот сюда, перед ссылкой, пишу ДВССЫЛ, выводится предлагаемая функциявот оно, это верно так, что просто нажимаю на Tab и у нас сразу функциядописывается, и открывается её скобка. Теперь мы не забываем, что функция ДВССЫЛвсегда требует в качестве входного аргумента текстовое значение, так чтооткрываем кавычки, после этого переходим к концу ссылки, вот сюда, можно для этогопросто нажать клавишу End, и здесь закрываем кавычки, ну и сразу за нимитакже скобки. Отлично! Вносим точку с запятой и аналогичным образом указываемструктурированную ссылку на диапазон значений столбца «Смена / Тип дня». Пишу ДВССЫЛ,дописываю название функции, затем выбираю одну из ячеекнужного столбца, Control+Пробел, и оборачиваю эту ссылку в кавычки. Ставлюкурсор сперва здесь, кавычки. Затем клавиша End, снова кавычки, и закрываемскобки. На пока всё, так что закрываем последнюю пару скобок и нажимаем наEnter. В итоге у нас сейчас подтягивается обозначение праздничного дня. Это так,поскольку в ячейке заголовка января, вот здесь, внесено, как помним, 1 января, и оно,согласно основной таблице, является праздником. Что ж, супер! Вот теперь уже,подготовив формулу, мы можем. Приступить к созданию правил условногоформатирования. Непосредственно перед этим войдем в содержимое ячейки, скопируемцеликом всю эту форму, я использую комбинацию клавиш CTRL Shift Home,чтобы выбрать весь текст и затем CTRL+С. Теперь приступим к созданиюправил условного форматирования. Выбираем для этого целиком областькалендаря вместе с заголовком месяцев, я это сделаю опять-таки с помощьюкомбинации клавиш, нажимаю CTRL Shift щелкаю по клавише вниз, что выбрать всеячейки, заполненные без пробелов вниз, и теперь также держа зажатыми клавишиCTRL и Shift щелкаю один раз вправо, затем еще раз и здесь уже придется вручнуюнесколько раз щелкать по клавише вправо, то есть отпускаем Control, держим всё такжезажатой клавишу Shift, и щелкаем столько раз, сколько нам понадобится длявыбора третьего ряда месяцев. Итак, диапазон всего календарям выбран, и поэтомутеперь во вкладке «Главная» щелкаем по кнопке «Условное форматирование».В открывшемся списке выбираем команду «Создать правило».Следующем окне нам нужен вариант «Использовать формулу для определения форматируемых ячеек».В поле формулы мы вставляем с помощью комбинации клавишCTRL+V скопированный текст подготовленной формулы и для еёзавершения остается лишь на конце вписать сравнение. Как помним, нам нужносоздать правила для всех вот этих вариантов обозначения дней, так чтопросто будем идти по порядку и начнем с выделения первой рабочей смены. Для этогопросто добавляем на конце формулы сравнения равно единице. Все. Проверочнаяформула готова и теперь остается лишь задать форматирование. Щелкаем по кнопке«Формат» и я в качестве примера скажу, что рабочие дни первой смены у меня должны вкалендаре выделяться светло-синей заливкой, вот такой, ну и, пожалуй, дляотделения друг от друга дней я также задал бы во вкладке «Границы» белуювнешнюю границу, то есть выбираю белый цвет, и задаем его в качестве цветавнешних границ. Щелкаем вот сюда. Ok, Ok, и вот результат. Как видим, все дни первойсмены у нас выделены теперь синим цветом. Следующими давайте отобразим накалендаре дни второй рабочей смены. Просто для этого, выбрав опять весь календарь,создаем еще одно пользовательское правило условного форматирования.Здесь в поле формулы снова вставляем из буфера обмена скопированный текстформулы и в конце вписываем в этот раз сравнение «Равно двойке». Опять заходимв «Формат» и в этот раз задаем цвет заливки для второй смены. Пусть это будет воттакой светло-оранжевый цвет. Кроме того, также не забываем задать белый цветвнешним границам ячеек. Ok, еще раз Ok, и вот на календаре выделяются уже дни ивторой смены. Супер! Собственно, таким образом Вам нужно определить цвет длятого количества смен, который в будущем может потребоваться. Я сейчас в рамкахнашего примера задам форматирования для максимум трёх смен, и поэтому добавляю ужепредставленным образом последнее правило для форматирования третьей смены. Супер! Всерабочие смены уже на календаре отмечаются и теперь давайте такжесоздадим правила условного форматирования для праздничных ивыходных дней. Все так же добавляем правило, вставляем уже известную намформулу, и в конце вписываем равно в кавычках «Праздник». Ok, формат будет такой.Красная заливка с белым подчеркнутым шрифтом полужирного написания и белаяграница ячейки. Отлично! Ну и теперь выходные. Давайте их отметим простокрасным цветом шрифта. Добавляю очередное правило условного форматирования, в концевписываю день «Выходной» и лишь изменяю цвет шрифта. Он будет вот такого красногоцвета. Супер! При этом, как видите, кое-что у нас сейчас здесь отображаетсянеправильно, а именно дни, не относящиеся к своим месяцам, то есть вот, например,в феврале из-за заливки виден один день предыдущего месяца, января, а также дниследующего месяца марта. Поправить мы это можем, поменяв порядок примененияправила условного форматирования. Опять выбрав целиком все месяца,открываем список правил условного форматирования. Сделать это можно просто.Щелкаем по кнопке «Условное форматирование», и выбираем опцию «Управление правилами».Сделаем это окно побольше. Просто тянем вот так за край и вот теперь вот эти всеправила форматирования типов дней нужно сместить вниз, то есть выбираем каждоеотдельное правило и щелкая по стрелке вниз, смещаем их в самый конец списка.Кроме того, также у каждого из этих правил без видимого форматирования нужноактивировать настройку «Остановить если ИСТИНА», то есть выставляем вот так галочки.Окей, ну и последняя поправка. Среди всех этих правил подчёркивания типа дня насамую верхнюю позицию следует разместить правила условного форматированияпраздников, также активировав настройку «Остановить если ИСТИНА», чтобы праздники,так сказать, переписывали поверх выходные дни. Всё. Вот теперь нажимаем на Ok, и мыбуквально в миллиметре от готового календаря. Единственное, что нужно ещепоправить, так это убрать форматирование заголовков месяцев. То есть вот, видите,например, первое января, содержащееся в заголовке месяца января, подпадает подправило форматирование праздников, и поэтому мы получаем вот такую краснуюзаливку и белый шрифт слова январь. Заголовок же мая отформатирован каквыходной день, так как 1 мая — это воскресенье. А заголовки большинстваостальных месяцев отформатированы, как рабочие смены. Так что поправим это. Зажавклавишу Control выбираем все заголовки месяцев. При этом можно особо нецеремониться и выбрать целиком строки вот таким образом. Теперь снова щелкаемпо списку команд условного форматирования, и нажимаем на «Создать правило».Выбираем уже знакомый нам вариант для написания пользовательскойформулы и здесь в поле формулы можно внести, например, что-то подобное. Вставляюзнак равно, выбираю первую ячейку выделенного диапазона, то есть ячейкус заголовком января, удаляю символы долларов из ссылки. Теперь указываюобозначение логического сравнения «Не равно», то есть вот так вносим символменьше и сразу символ больше, и указываем пустые кавычки, то есть простооткрываем и закрываем их. Таким образом это правило звучит сейчас так: еслиячейка не пустая, то форматируй её следующим образом: и форматирование мыкак раз сейчас и зададим. Это будет просто белая заливка с черным полужирнымшрифтом. Окей, окей, и пока ничего не меняется. Если Вы наблюдаете одинаковуюкартину, то не удивляйтесь. Смотрите, если мы откроем список правил условногоформатирования, то видим, что в недавно прописанной формуле ссылка с B3почему-то сместилась на вот такую ссылку. Я полагаю, что это баг в экселе, внекоторых ситуациях происходит именно так. Пишешь правило, подтверждаешь егоприменение, и почему-то ссылки в нём вот так считай рандомно изменяются, из-зачего приходится заново заходить в это правило и поправлять не желаемоеизменение ссылок. В общем, как бы там ни было, дважды щелкаем по нашему правилу ивозвращаем на место ссылку B3, вписав её вот так вручную. Окей, и передтем, как окончательно подтвердить изменения, ставим также галочку в поле«Остановить если ИСТИНА» у нашего нового правила. Окончательно подтверждаемизменения и всё, мы с Вами только что целиком построили автоматизированныйуниверсальный планировщик смен в Excel. Чтобы аккуратно отображался лишькалендарь, можно вот так выделить во-первых, столбцы в области его настроек,использовать комбинацию клавиш Alt+Shift+Стрелка вправо, чтобы сгруппировать этотдиапазон и скрыть его. И то же самое, конечно, можно также повторить и сосновной таблицей. Идеально. Что ж, давайте насладимся этим творением и рассмотримего возможности. Начнем с самого основного, с настроек календаря. Для того, чтобыпоменять отображаемый год просто вносим в ячейке B2 дату первого месяцанужного года. Вот, например, 2023 год, вот високосный 2024, и так далее. Верну покаобратно 2022 год. Идем дальше. Откроем этот диапазон с настройками. Кстати вотэту формулу можно удалить, она больше не нужна. В этом диапазоне настроек мы можемпоменять формат недели, то есть вот смотрите. Вношу единичку, Enter и теперьпервый день недели — это воскресенье. Если внесем число 16, то первым днем неделистановится суббота, в некоторых странах используется такой вариант форматанедели. Ok. Давайте, однако, вернем привычный нам вариант. Вношу двойку, инеделя в нашем календаре опять начинается с понедельника. Отлично! Идемдалее. Вот в этой таблице, как Вы уже знаете, можно менять настройки смен. Вот,например, настроим планирование двух смен по три дня. При этом обратите внимание. Втекущем примере настройки календаря я прописал правило форматирования лишьмаксимум для трех смен, именно поэтому, если настроить отображение четырех смен, то четвертаясмена, как и все последующие, пока никак визуально не выделяется. Это я просто такрешил напомнить, что, если Вам требуется больше трех смен, то нужно добавитьдополнительные правила условного форматирования в календарь. Отлично! Этуумную таблицу мы также обсудили и давайте я опять верну настройкупланирования трёх смен по два дня. Ok, переходим к следующей таблице. В ней мыможем менять выходные и рабочие дни. Вот, например, сделаем четверг выходным, асубботу наоборот рабочим днем. Планирование смен сразу, как видим,отличным образом поправляется согласно текущим новым настройкам недели. Ok. Этоснова был лишь пример, так что верну опять-таки обратном привычный вариант,когда суббота и воскресенье — это единственный выходные дни. Супер! Ну икроме того, как Вы уже знаете, в списке ниже можно вносить особые дни в календарь.Примеры с первым январем и восьмым мартом нам уже хорошо известны в рамкахэтого видео, так что добавим для наглядности еще и день труда. Вотсмотрите, сейчас эта даты в календаре отмечена как обычный выходной день, такчто пишем вот здесь описание «День труда». Дата 1 мая. Тип «Праздник». И подсчет 0. Супер!Сразу видим, как 1 мая у нас теперь отмечается, как праздничный день поверхего статуса обычного воскресного выходного дня. Представим, что из-завыпадения праздника 1 мая на воскресенье, праздничным выходным днем должен статьследующий день, то есть понедельник. В таком случае вписываем в описании,например, «Перенос праздничного дня», дата - второе мая, при этом обратите вниманиея сейчас нажимаю на Tab, и моментально на день сдвигаются запланированные смены. Нуи дальше пишем тип дня «Праздник» и учет рабочих дней также 0. Супер! Этим примеромсо вторым мая я лишь хотел показать, как смещаются запланированные смены всоответствии с особыми заданными пользователем днями. Что ж, отлично! Крометого, давайте я Вам также представлю, как данную таблицу специальных дней можноиспользовать для внесения замен в план смен. Предположим, например, что поопределенной причине во вторник 4 января работники 3 третьей смены, зеленый цвет, должныподменить работников первой смены. В данном случае просто вносим в таблицуспециальных дней такую строку. Описание, например, «Замена». Дата - 4 января. Сразувидим смены в календаре сместились, и после в типе указываем заменяющую смену.Это третья смена. Сразу так же внесем в учете дня более эксплицитно 0. Вот теперьу нас третья смена работает вместо первой четвертого числа. При этом, как мы сейчас видим,второй рабочий день первой смены сдвинулся на следующее пятое число, то есть еслиодна смена просто должна подменить другую без изменения последующегозапланированного распределения смен, то в строке замены смен нужно в столбце учетрабочих дней внести единицу. Вот, выбираю эту ячейку, единица, Enter и теперь, каквидите, третья смена подменяет вторую, а после идет изначальный план, то естьгрубо говоря один рабочий день первой смены был отдан третье безвозмездно, так сказать. Чтож, супер! Пожалуй, мы обсудили весь самый важный функционалэтого календаря. Как можно заметить, календарь выходит достаточноуниверсальным. Он способен принимать любые дополнительные типы дней. Учитыватьручные поправки, вносимые в план распределения смен. Он может отображатьсамые разные настройки рабочей недели и так далее. Кроме того, посколькухарактеристики всех дней года также правильным образом у нас автоматическипрописываются в основной таблице дней, можно легко выводить определеннуюстатистику. Вот, например, перечислю все типы дней – первая, вторая и третья смены, а также выходные ипраздничные дни, и теперь подсчитаем количество каждого типа с помощьюфункции СЧЕТЕСЛИ. Вот сразу видим - у нас в году восемьдесят пять рабочих днейпервой смены 86 второй, и 87 третьей, 103 выходных дня и 4 праздника. Отменимвот эту замену смен 4 января и у нас сразу поправляется указание рабочих днейотдельных смен - каждая смена теперь работает по 86 дней. Просто красота! Чтож, я рассказал, пожалуй, все самое интересное об этом календаре, тем неменее, поскольку он построен достаточно универсальным образом, в него можновстраивать и другой дорабатываемый функционал, а также выдумывать другиеварианты его применения. Простейший пример можно привести такой. Например, вэтом календаре можно планировать тренировки по определенному графику взависимости от дней недели, который также активно бы учитывал вносимой вручнуюпоправки в списке специальных дней. И так далее, в общем здесь реально открываетсябольшое поле для креативных решений, так что, если у Вас будут какие-нибудьинтересные предложения по применению или же расширению функционала этогокалендаря, можете смело делиться ими в комментариях. Ok. Кроме того, я специальноне делился готовым файлом этого календаря до самого завершения видео. Вконце концов важно, чтобы Вы не просто брали готовое решение, но и понималипринцип его построения и работы. Да и, что уж и говорить, одно лишь следованиеуказаниям из этого видео и повторение представленных действий однозначно можетпринести огромную пользу и помочь закрепить важные навыки работы в Excel. Влюбом случае, для самых ответственных зрителей, для тех, кто целиком просмотрелэто видео и возможно даже также проделал все представленные шаги по созданиюкалендаря, я делюсь ссылкой для скачивания готового файла, который в этомвидеоуроке был создан. Эта ссылка запрятана в файле для начала работы свидеоуроком. То есть вот в этом изначальном файле, с которого мы началивидеоурок, и запрятана эта ссылка на скачивание готового календаря. Смотрите,где её можно найти. Открыв файл для начала урока заходим в редактор VBA,используем для этого комбинацию клавиш Alt+F11. В открывшемся окне щелкаемлевой кнопкой мышки по наименованию, сюрприз, второго спрятанного в рабочей книгелиста и в окне Properties изменяем значение настройки Visible с двойки на-1. Если у Вас это окно Properties вот так скрыто, то вывести его можно, щелкнувпо вкладке «View» и выбрав вот здесь опцию «Properties Window». Ok. Поменяв настройкуVisible, закрываем окно редактора VBA, и сразу видим в рабочей книге второйлист. Открыв его видим две ссылки для скачивания готового календаря в двухвариантах. В первом варианте прописана формула, которую мы писали в этомвидеоуроке, то есть с функцией ПРОСМОТРX, а во втором варианте использованылишь доступные для всех функции. Если Вы работаете в версии Microsoft Office 2021или Office 365, и естественно в более новых версиях Microsoft Office, тоскачивайте вот эту версию. Если у Вас версия Microsoft Office более старая тоиспользуйте вторую ссылку. Что ж, супер! Вот мы закончили этот просто огромныйвидеоурок. Надеюсь, он Вам понравился и предоставил очередную возможностьпоупражняться в Excel. Кроме того, конечно надеюсь также, что созданный в рамкахэтого урока календарь-планировщик поможет Вам в решении Ваших задач. Если этовидео и созданный календарь Вам понравились, то ставьте лайк,подписывайтесь на канал, и мы увидимся с Вами в следующем видео. Пока!