Всяка транзакция се извършва в определен момент или период и след това се обвързва с определена дата. В Excel датите се преобразуват в цели числа. Тоест всяка дата има свое цяло число, например 01/01/1900 е числото 1, а 01/02/1900 е числото 2 и т.н. Дефинирането на години, месеци и дни не е нищо повече от подходящия тип форматиране за следващите числови стойности. Поради тази причина дори най-простите операции с дати, извършвани в Excel (например сортиране), се оказват много проблематични.

Сортиране в Excel по дата и месец

Използвайки примера на таблицата, показана на фигурата по-долу, ще покажем как да я подготвим за удобно сортиране на дати. Нашият списък с транзакции с дати обхваща диапазона от клетки B1:C15.

Първо, нека създадем колона, в която номерираме всички транзакции. Считайте това действие за добра форма за всеки тип сортиране. Наистина, в такива случаи, ако данните са неправилно разбъркани, винаги имаме възможност да се върнем към първоначалния вид на таблицата. За това:

В резултат на това колоната ще бъде автоматично попълнена с поредица от номера на транзакции от 1 до 14.

Полезен съвет! В Excel повечето проблеми имат множество решения. За да нормализирате автоматично колони в Excel, можете да използвате десния бутон на мишката. За да направите това, просто преместете курсора върху маркера на курсора на клавиатурата (в клетка A2) и като държите само десния бутон на мишката, плъзнете маркера по колоната. След като отпуснете десния бутон на мишката, контекстно менюот който трябва да изберете опцията „Попълване“. И колоната ще бъде автоматично попълнена с поредица от числа, подобно на първия метод за автоматично попълване.

Сега е време да създадете помощни колони, които значително ще помогнат за ефективното сортиране на списъка с транзакции. Трябва да следвате няколко прости стъпки:

1. Попълнете клетките D1, E1, F1 с имената на заглавията: „Година“, „Месец“, „Ден“.
2. Според всяка колона въведете съответните функции под заглавията и ги копирайте във всяка колона:

• D1: =ГОДИНА(B2);
• E1: = МЕСЕЦ (B2);
• F1: = ДЕН (B2).

В резултат на това трябва да получим следния резултат:

Нашата таблица е готова и дава възможност за извършване на многостранно сортиране на транзакции по дата. Нека първо направим тестово сортиране, за да се уверим, че всичко работи.

Да кажем, че искаме да сортираме датите на транзакциите по месеци. В този случай редът на дните и годините няма значение. За да направите това, просто отидете до която и да е клетка в колоната „Месец“ (E) и изберете инструмента: „ДАННИ“ - „Сортиране и филтриране“ - „Сортиране във възходящ ред“.

Сега, за да нулирате сортирането и да върнете данните в таблицата в оригиналната им форма, отидете до която и да е клетка на колоната „Не“ (A) и отново изберете същия инструмент „Сортиране по възходящ ред“.



Как да сортирате дати по множество условия в Excel

Сега можете да започнете комплексно сортиране на дати според няколко условия. Задачата е следната: транзакциите трябва да бъдат сортирани в следния ред:

1. Години във възходящ ред.
2. Месеците през определени години са в низходящ ред.
3. Дните през определени месеци са в низходящ ред.

Метод за изпълнение на задачата:

В резултат на това извършихме сложно сортиране на дати според няколко условия:

За да сортирате стойностите на таблицата във формат за дата, Excel предоставя опции за падащ списък като Старо към Ново и Ново към Старо. Но на практика при работа с големи обеми данни резултатът не винаги оправдава очакванията. Тъй като за Excel програмидатите са цели числа; по-безопасно и по-ефективно е да ги сортирате, като използвате метода, описан в тази статия.

Всеки ред е отделна транзакция. Трябва да направите отчет за месеца. Колко продукта са продадени всеки месец. Има различни начини за решаване на този проблем, но нека приемем, че нашата задача е да извлечем месеца от датата, така че по-късно да е лесно да обобщим данните за тях.

Метод 1. Вземете месеца от датата с помощта на функцията MONTH в Excel

Пишем формулата:

МЕСЕЦ (A2)

Разтягаме формулата и получаваме месеца от датата под формата на число. 5 е месец май, 8 е август и т.н.

Има подобни функции: ГОДИНА, МЕСЕЦ, СЕДМИЦА, ДЕН, ЧАС, МИНУТИ, СЕКУНДИ, които работят по подобен начин. Например, ако в нашия пример напишем =YEAR(A2), тогава резултатът ще бъде 2013 и т.н.

Понякога трябва да получите месеца от дата в текстов формат: "Януари, февруари, март..." в този случай ще използваме друга функция.

Метод 2. Вземете месец от датата с помощта на функцията TEXT в Excel

Синтаксисът ще бъде както следва

ТЕКСТ(стойност, формат)

Стойността е препратка към клетка с дата
Формат - за да получите месеци, които трябва да използвате Главна буква"М". Освен това форматът на показване ще зависи от количеството му (първа буква на месеца, пълно име на месеца, кратко име, под формата на двойна цифра и една цифра)
Това може да се види ясно на екранната снимка.

Датите често са важна част от анализа на данните. Често задавате въпроси като: кога е закупен продуктът, колко време ще отнеме изпълнението на задача в даден проект или какъв е средният приход за фискалното тримесечие? Правилното въвеждане на дати е от съществено значение за гарантиране на точни резултати. Въпреки това, за да разберете правилно тези резултати, трябва да форматирате датите, за да ги направите по-лесни за разбиране.

Важно:Тъй като правилата, управляващи как датите се интерпретират от която и да е програма за изчисление, са сложни, трябва да сте възможно най-специфични за датата с всеки запис. Това ще гарантира, че изчисленията на датата са възможно най-точни.

Относно изчисленията и форматите на датите

Excel съхранява датите като последователни числа, наречени последователни стойности. Например в Excel за Windows 1 януари 1900 г. е пореден номер 1, а 1 януари 2008 г. е пореден номер 39448, защото е 39 448 дни след 1 януари 1900 г.

Excel съхранява времевите стойности като десетични знаци, тъй като времето се третира като част от деня. Десетично числое стойност в диапазона от 0 (нула) до 0,99999999, представляваща времето от 0:00:00 (12:00:00 сутринта) до 23:59:59 (23:59:59).

Тъй като стойностите за дата и час са стойности, те могат да се добавят, изваждат и включват в други изчисления. Можете да видите дата като порядъчна стойност и час като десетична, като промените формата на клетката, съдържаща датата или часа, в общ формат.

Разбиране на двете системи за дата

Excel за Mac и Excel за Windows поддържат системите за дата 1900 и 1904. Системата за дата по подразбиране за Excel за Windows е 1900; и системата за дата по подразбиране за Excel за Mac е 1904.

Първоначално Excel за Windows е базиран на системата за дати от 1900 г., тъй като подобрява съвместимостта с други програми за електронни таблици, предназначени за операционна система MS-DOS и Microsoft Windowsи така стана тя стандартна системадата Първоначално Excel за Mac се основаваше на системата за дати от 1904 г., тъй като подобряваше съвместимостта с ранните компютри Macintosh, които не поддържаха дати преди 2 януари 1904 г., така че стана системата за дати по подразбиране.

Таблицата по-долу показва първата дата и датата на последния период за всяка система от дати и последователното значение, свързано с всяка дата.

Тъй като двете системи за дати използват различни начални дни, една и съща дата се представя с различни серийни номера във всяка система за дати. Например 5 юли 2007 г. може да има две различни последователни значения в зависимост от използваната система за дати.

Разликата между двете датни системи е 1462 дни; Това означава, че поредният номер на дата в системата за дати от 1900 г. винаги е с 1462 дни по-малък от серийната стойност на същата дата в системата за дати от 1904 г. Обратно, поредният номер на дата в системата за дати от 1904 г. винаги е 1462 дни по-малко от серийната стойност на същите дати в системата за дати от 1900 г. 1462 дни се равняват на 4 години и един ден (включително един високосен ден).

Промяна на начина, по който се тълкува двуцифрена година

Важно:За да сте сигурни, че стойностите на годината се интерпретират правилно, въведете четири цифри (например 2001 вместо 01). Ако въведете четирицифрени години, Excel няма да интерпретира вековете.

Ако въведете двуцифрена годишна дата в текстова клетка или текстов аргумент във функция, като например "=година" ("1/1/31"), Excel интерпретира годината, както следва:

00 – 29 се интерпретира като години от 2000 до 2029. Например, когато въвеждате дата 5/28/19 Excel смята, че датата може да е 28 май 2019 г.

от 30 до 99интерпретирани като години от 1930 до 1999. Например, когато въвеждате дата 5/28/98 Excel смята, че датата може да е 28 май 1998 г.

В Microsoft Windows можете да промените начина, по който двете цифри от годината се интерпретират за всички инсталирани програми Windows.

Windows 10

Контролен панели след това изберете Контролен панел.

В глава часовник, език и регионщракнете върху

Щракнете върху иконата за език и регионални стандарти.

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В полето

Щракнете върху бутона Добре.

Windows 8

Търсене Търсене), въведете в полето за търсене Елемент на контролния панел Контролен панел.

В глава часовник, език и регионщракнете върху

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В полето след като въведете две цифри от годината, я интерпретирайте като година между тяхрамки, смяна на горната граница за клепача.

Когато горната граница се промени, годината автоматично се променя към долната граница.

Щракнете върху бутона Добре.

Windows 7

Щракнете върху бутона Започнетеи изберете Контролен панел.

Избери предмет.

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В полето след като въведете две цифри от годината, я интерпретирайте като година между тяхрамки, смяна на горната граница за клепача.

Когато горната граница се промени, годината автоматично се променя към долната граница.

Щракнете върху бутона Добре.

Променете формата на датата по подразбиране, за да покажете годината като четири цифри

По подразбиране, когато въвеждате дати в работната книга, датите се форматират, за да показват двуцифрени години. Когато промените формата на датата по подразбиране в друг формат с помощта на тази процедура, показването на датите, въведени преди това в работната книга, ще се промени на нов формат, все още неформатиран в диалоговия прозорец " Формат на клетка" "(на раздела У домав група номерщракнете върху бутона извикайте диалоговия прозорец).

Windows 10

В полето за търсене на лентата на задачите въведете Контролен панели след това изберете Контролен панел.

В глава часовник, език и регионщракнете промяна на формата за дата, час и числа .

Щракнете върху иконата за език и регионални стандарти.

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В краткия списък Формат на датата

Щракнете върху бутона Добре.

Windows 8

Плъзнете от десния край на екрана, докоснете Търсене(или ако използвате мишка, преместете мишката в горния десен ъгъл на екрана, преместете я надолу и щракнете Търсене), въведете в полето за търсене Елемент на контролния панели след това натиснете или Избор Контролен панел.

В глава часовник, език и регионщракнете промяна на формата за дата, час или числа.

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В краткия списък Формат на дататаизберете формат, който използва четири цифри за годината (гггг).

Щракнете върху бутона Добре.

Windows 7

Щракнете върху бутона Започнетеи изберете Контролен панел.

Изберете предмет езикови и регионални стандарти.

В диалоговия прозорец регионщракнете върху бутона Допълнителни опции.

Отворете раздела дата .

В краткия списък Формат на дататаизберете формат, който използва четири цифри за годината (гггг).

Щракнете върху бутона Добре.

Промяна на системата за дати в Excel

Системата за дати се променя автоматично, когато отворите документ, създаден на друга платформа. Например, ако работите в Excel и отворите документ, създаден в Excel за Mac, " система за дата 1904 г“ се инсталира автоматично.

Можете да промените системата за дати, като следвате стъпките по-долу.

Изберете Файл > Настроики > Допълнително.

В глава при изчисляването на тази книгаизберете книгата, която искате, и след това поставете или премахнете отметката от квадратчето използвайте система за дата 1904 .

Проблем: Има проблеми с датите между книги, които използват различни системи за дати

Може да срещнете проблеми при копиране и поставяне на дати и при създаване на външни връзки между книги въз основа на две различни системидата Датите могат да бъдат изведени четири години по-рано или по-късно от очакваната дата. Тези проблеми могат да възникнат, ако използвате Excel за Windows, Excel за Mac или и двете.

Например, ако копирате датата 5 юли 2007 г. от работна книга, която използва системата за дати 1900, и след това я поставите в работна книга, която използва системата за дати 1904, датата се появява като 5 юли 2011 г., което е 1462 дни по късно. Освен това, ако копирате датата 5 юли 2007 г. от работна книга, която използва системата за дати 1904, и след това я поставите в работна книга, която използва системата за дати 1900, датата ще бъде 4 юли 2003 г., което е 1462 дни по-рано. Главна информацияможете да намерите в секцията Системи за дати в Excel.

Коригиране на грешка при копиране и поставяне

За да зададете датата на четири години и един ден по-късно, добавете към нея 1462. Пример

Лист1!$A$1+1462

За да зададете датата на четири или повече години ранна версия, извадете 1462 от него. Пример

Лист1!$A$1-1462

Допълнителна информация

Винаги можете да зададете въпрос на специалист от Excel Tech Community, да поискате помощ в общността на Answers и също да предложите нова функцияили подобрение на уебсайта на Excel User Voice

Най-простият и бърз начинЗа да въведете текущата дата или час в клетка, трябва да натиснете комбинацията от клавишни комбинации CTRL+“;” (текуща дата) и CTRL+SHIFT+“;” (текущо време).

Много по-ефективно е да използвате функцията TODAY(). В крайна сметка, той не само задава, но и автоматично актуализира стойността на клетката всеки ден без намеса на потребителя.

Как да поставите текущата дата в Excel

За да вмъкнете текущата дата в Excel, използвайте функцията TODAY(). За да направите това, изберете инструмента „Формули“ - „Дата и час“ - „ДНЕС“. Тази функцияняма аргументи, така че можете просто да въведете в клетката: "=TODAY()" и да натиснете ENTER.

Текуща дата в клетка:

Ако е необходимо клетката автоматично да актуализира стойността не само на текущата дата, но и на часа, тогава е по-добре да използвате функцията „=TDATE()“.

Текущата дата и час в клетката.



Как да зададете текущата дата в Excel на горните и долните колонтитули

Вмъкването на текущата дата в Excel се осъществява по няколко начина:

1. Чрез задаване на опции за горен и долен колонтитул. Предимството на този метод е, че текущата дата и час се отпечатват на всички страници едновременно.
2. Използване на функцията TODAY().
3. Използване на комбинацията от клавишни комбинации CTRL+; – за настройка на текущата дата и CTRL+SHIFT+; – за настройка на текущия час. Недостатък - при този метод стойността на клетката няма да се актуализира автоматично до текущите индикатори при отваряне на документа. Но в някои случаи липсата на данни е предимство.
4. Използване на VBA макроси с помощта на функции в програмния код: Date();Time();Now() .

Горните и долните колонтитули ви позволяват да зададете текущата дата и час в горната или долната част на страниците на документа, който ще бъде изведен на принтера. Освен това долният колонтитул ни позволява да номерираме всички страници на документа.

За да направите текущата дата в Excel и номерирането на страници с помощта на горни и долни колонтитули, направете следното:

Горните и долните колонтитули ни позволяват да правим повече от просто задаване на дати и номера на страници. Можете също така да добавите място за подпис на лицето, отговорно за доклада. Например, нека сега редактираме долната лява част на страницата в областта на горния и долния колонтитул.