Query whether the constellation uses the lunar calendar or the solar calendar, and Excel can tell you.

Query whether the constellation uses the lunar calendar or the solar calendar.

":You know her birthday, and you want to know her horoscope and zodiac sign. : You can query the search engine! But you have a set of their birthdays How can we know their horoscope and zodiac sign? : this? Actually, you can use Excel!

If you know the birthdays of a group of people, how can you know their horoscopes and zodiac signs?

Come to think of it, the constellation and the zodiac are based on different parts of birthday information:

Constellation: Looking at the moon and day, it will be divided into uneven 12 parts every year.

Zodiac: according to the lunar calendar, it is repeated every 12 years.

In this case, we focus on how to realize the above two expressions with formulas.

The solution is to find the constellation.

Do you feel a little dizzy after reading this formula? Let Rocket Jun explain it to you in detail.

-text (B2, "mdd")

Extract the information of the month and day in cell B2, where the information of the month can be 1 or 2 digits, and the information of the day must be displayed as 2 digits, and then be converted into 3 digits or 4 digits through the burden reduction operation (two consecutive minus signs).

{10 1, "Capricorn"; 120, "water bottle"; ...； 1222, "Capricorn"

As shown in the above figure, an array of 2* 13 is constructed, corresponding to the start date and constellation name of the constellation respectively.

Find (..., ...)

Query the constellation name corresponding to the month and day information of B2 in the array through the lookup function. Among them, because the search function is a sequential query, and Capricorn spans two months at the end of the first year (65438+February 22 ~ 65438+1October 19), the information of Capricorn must be divided into two parts when creating the array.

Find the zodiac map

Text (B2, "[$- 130000]e")

Convert the Gregorian calendar of B2 into the lunar calendar, and extract the year information. "[$- 130000] ..." You can convert the Gregorian calendar into the lunar calendar, and at the same time you can set different expressions, such as "$- 130000]e" as the lunar year, "[$- 130000]yyyy-mm-dd.

MOD(...-4, 12)+ 1

Since the date in Excel starts from 1900 and the remainder of 1900/ 12 is 4, you need to subtract 4 from the year information and divide it by 12 to get the remainder. At the same time, because the starting bit of the string is 1. Therefore, by adding 1 to the remainder, we can get the actual position in the string of "mouse, cow, tiger, rabbit, dragon, snake, horse, sheep, monkey, chicken, dog and pig".

For example,

BTW, the method mentioned above is definitely not the only way to find constellations and zodiac signs. For example, will you find the zodiac by looking for constellations?

▲ Can this treat cervical spondylosis?

At the same time, you are also welcome to leave a message in the comment area at the end of the article, and talk about your new idea of finding a constellation to find a zodiac formula!

If you are worried about how to find Y and some DummyData for operation and learning, please click on DummyData Generator brought by Rocket Jun in related articles!

Is the constellation based on the solar calendar or the lunar calendar?

The calculation of the twelve constellations in the Gregorian calendar is due to the slow introduction of the West into China, which belongs to western culture and is calculated by the Gregorian calendar (internationally recognized). If it is according to the lunar calendar, it is the "twelve stars" of China. Babylonians made a long-term prediction of these constellations, determined the ecliptic through observation, and divided it into 12 equal parts, becoming the zodiac. "These zodiac signs are named after the 12 constellation. Starting from the vernal equinox, counterclockwise is Aries (Capricorn), Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio, Sagittarius (Sagittarius), Capricorn Aquarius (Aquarius) and Pisces. The twelve constellations were originally used for divination, and each pronoun represents a certain direction of the universe. Later, people used to put twelve stars on behalf of twelve personalities. The birth time of each person is counted as the position where each star falls into the ecliptic, which implies a person's innate character. The constellation of twelve stars can reflect Renee's psychological characteristics and symbolically reflect a person's life behavior. The constellation originated in Babylon, one of the four ancient civilizations. At that time, they divided the sky into many areas, and called these areas "constellations", while the birthdays that westerners usually said were all in the solar calendar (new calendar).

Does the constellation look at the lunar calendar or the solar calendar?

According to the solar calendar

Constellations are divided according to the solar calendar. The horoscope refers to the date of the Gregorian calendar, that is, the Gregorian calendar, which is generally a numerical date written on the calendar. The dates used in daily life are all Gregorian calendar, so you can choose the constellation according to different dates. The ecliptic is divided into 12 constellations, starting from spring, that is, sorting begins around March 20 every year when the sun runs to the vernal equinox. They are Aries, Taurus, Gemini, Cancer, Leo, Virgo, Libra, Scorpio, Sagittarius, Capricorn, Aquarius and Pisces.