Excel

发表评论

根据索引（位置）截取字符串

MID(字符串, 开始位置, 提取长度)
函数：MID

=MID(字符串, 开始位置, 提取长度)

=MID("获得", 5, 4)
– A B
1 身份证 出生年
2 110110201201158888 2012
3 110110201501158888 2015
4 110110201601158888 2016

选中 B2 单元格 输入函数

=MID(A2, 7, 4)

结果：2012

将多个文本串联起来

函数： CONCATENATE

=CONCATENATE(字符串1, [字符串2, …])

=CONCATENATE("您好", "再见")

可以结果 MID 函数综合使用一下

– A B
1 身份证 出生年月日
2 110110201201158888 2012-09-03
3 110110201501158888 2015-01-15
4 110110201601158888 2016-01-15

选中 B2 单元格 输入函数

=CONCATENATE(MID(A2, 7, 4), "-", MID(A2, 11, 2), "-", MID(A2, 13, 2))

结果： 2012-09-03

根据日期返回星期

=CHOOSE(WEEKDAY(B3, 2), "一", "二", "三", "四", "五", "六", "日")
– A B
1 2021-10-01 五
2 2021-10-02 六
3 2021-10-03 日
4 2021-10-04 一
6 2021-10-05 二
7 2021-10-06 三
8 2021-10-07 四

根据关键词设定设定进度

函数： IF

=IF(A1="待开发","0",IF(A1="静态开发中","0.15",IF(A1="静态完成","0.30",IF(A1="后管静态开发中","0.55",IF(A1="后管静态完成","0.70",IF(A1="动态联调中","0.85","1"))))))

IF(A1="待开发",
    "0",
    IF(A1="静态开发中",
        "0.15",
        IF(A1="静态完成",
            "0.30",
            IF(A1="后管静态开发中",
                "0.55",
                IF(A1="后管静态完成",
                    "0.70",
                    IF(A1="动态联调中",
                        "0.85",
                        "1"
                    )
                )
            )
        )
    )
)
– A B
1 待开发 0
2 静态开发中 0.15
3 静态完成 0.30
4 后管静态开发中 0.55
4 后管静态完成 0.70
5 动态联调中 0.85
5 完成 1

将日期转化为星期

函数： TEXT

=TEXT(A1, "AAAA")
– A B
1 2022/6/1 星期三
2 2022/6/18 星期六

常用示例

公式 说明
=TEXT(1234.567, "$#,##0.00") 货币带有 1 个千位分隔符和 2 个小数，如 $1,234.57。 请注意，Excel 将该值四舍五入到小数点后两位。
=TEXT(TODAY()，"MM/DD/YY") 目前日期采用 YY/MM/DD 格式，如 12/03/14
=TEXT(TODAY(), "DDDD") 一周中的当天，如周日
=TEXT(NOW(), "H:MM AM/PM") 当前时间，如下午 1:29
=TEXT(0.285, "0.0%") 百分比，如 28.5%
=TEXT(4.34 , "# ?/?") 分数，如 4 1/3
=TRIM(TEXT(0.34, "# ?/?")) 分数，如 1/3。 注意，这将使用 TRIM 函数删除带十进制值的前导空格。
=TEXT(12200000, "0.00E+00") 科学记数法，如 1.22E+07
=TEXT(1234567898, "[<=9999999]###-####;(###) ###-####") 特殊（电话号码），如 (123) 456-7898
=TEXT(1234, "0000000") 添加前导零 (0)，如 0001234
=TEXT(123456, "##0° 00' 00''") 自定义 – 纬度/经度

注意: 虽然可使用 TEXT 函数更改格式，但这不是唯一的方法。 你可以通过按 CTRL+1 （或在 Mac 上按 MAC Command 按钮图标的图像+1）来更改不带公式的格式，然后在设置单元格 > 数字对话框中选择所需的格式。

世界语言 20211年10月19日

发表评论

大纲

世界语言 20211年10月19日

Abkhaz

  • Abkhaz

Acehnese

  • Acehnese

Acoli

  • Acoli

Adyghe

  • Adyghe

Afar (Djibouti)

  • Afar (Djibouti)

Afrikaans

  • Afrikaans
  • Afrikaans (South Africa)

Akan

  • Akan

Albanian

  • Albanian
  • Albanian (Albania)
  • Albanian (Gheg)

Amharic

  • Amharic
  • Amharic (Ethiopia)

Ancient Greek

  • Ancient Greek

Arabic

  • Arabic
  • Arabic (Algeria)
  • Arabic (Egypt)
  • Arabic (Iraq)
  • Arabic (Jordan)
  • Arabic (Lebanon)
  • Arabic (Saudi Arabia)
  • Arabic (Sudan)
  • Arabic (Syria)
  • Arabic (U.A.E.)
  • Arabic (Unitag)

Aragonese

  • Aragonese

Aramaic

  • Aramaic

Aranese

  • Aranese

Armenian

  • Armenian
  • Armenian (Armenia)
  • Armenian (Russia)

Assamese

  • Assamese
  • Assamese (India)

Asturian

  • Asturian
  • Asturian (Spain)

Aymara

  • Aymara

Azerbaijani

  • Azerbaijani
  • Azerbaijani (Arabic)
  • Azerbaijani (Azerbaijan)
  • Azerbaijani (Iran)
  • Azerbaijani (Latin)

Balochi

  • Balochi

Bashkir

  • Bashkir

Basque

  • Basque
  • Basque (Spain)

Bavarian

  • Bavarian

Belarusian

  • Belarusian
  • Belarusian (Belarus)
  • Belarusian (Tarask)

Bemba

  • Bemba
  • Bemba (Zambia)

Bench

  • Bench

Bengali

  • Bengali
  • Bengali (Bangladesh)
  • Bengali (India)

Berber

  • Berber

Bislama

  • Bislama

Bodo

  • Bodo

Bosnian

  • Bosnian
  • Bosnian (Bosnia and Herzegovina)
  • Bosnian (Republika Srpska)

Breton

  • Breton
  • Breton (France)

Bulgarian

  • Bulgarian
  • Bulgarian (Bulgaria)
  • Bulgarian (Cyrillic)
  • Bulgarian (United States)

Burmese

  • Burmese
  • Burmese (Myanmar)

Buryat

  • Buryat

Cantonese

  • Cantonese

Catalan

  • Catalan
  • Catalan (Spain)
  • Catalan (Valencian)

Cebuano

  • Cebuano

Central Atlas Tamazight

  • Central Atlas Tamazight

Central Kurdish

  • Central Kurdish

Chamorro

  • Chamorro

Chechen

  • Chechen

Cherokee

  • Cherokee

Chhattisgarhi

  • Chhattisgarhi

Chiga

  • Chiga

Chinese

  • Chinese
  • Chinese (China)
  • Chinese (China) (GB2312)
  • Chinese (Gan)
  • Chinese (Hakka)
  • Chinese (Hong Kong)
  • Chinese (Huizhou)
  • Chinese (Jinyu)
  • Chinese (Literary)
  • Chinese (Mandarin)
  • Chinese (Min Bei)
  • Chinese (Min Dong)
  • Chinese (Min Nan)
  • Chinese (Min Zhong)
  • Chinese (Pu-Xian)
  • Chinese (Singapore)
  • Chinese (Taiwan)
  • Chinese (Taiwan) (Big5)
  • Chinese (Wu)
  • Chinese (Xiang)
  • Chinese Simplified
  • Chinese Traditional

Chuukese

  • Chuukese

Chuvash

  • Chuvash

Classical Armenian

  • Classical Armenian

Colognian

  • Colognian

Cornish

  • Cornish

Corsican

  • Corsican

Crimean Turkish

  • Crimean Turkish

Croatian

  • Croatian
  • Croatian (Bosnia and Herzegovina)
  • Croatian (Croatia)
  • Croatian (Republika Srpska)

Czech

  • Czech
  • Czech (Czech Republic)

Danish

  • Danish
  • Danish (Denmark)

Divehi

  • Divehi

Dogri

  • Dogri

Dutch

  • Dutch
  • Dutch (Belgium)
  • Dutch (Netherlands)

Dzongkha

  • Dzongkha
  • Dzongkha (Bhutan)

Eastern Armenian

  • Eastern Armenian
  • Eastern Armenian (Russia)

Eastern Mari (Russia)

  • Eastern Mari (Russia)

English

  • English
  • English (Albania)
  • English (Australia)
  • English (Austria)
  • English (Bangladesh)
  • English (Belgium)
  • English (Bosnia and Herzegovina)
  • English (Brazil)
  • English (Bulgaria)
  • English (Canada)
  • English (Chile)
  • English (Croatia)
  • English (Cyprus)
  • English (Czech Republic)
  • English (Denmark)
  • English (Egypt)
  • English (Estonia)
  • English (Finland)
  • English (France)
  • English (Germany)
  • English (Ghana)
  • English (Greece)
  • English (Hong Kong)
  • English (Hungary)
  • English (India)
  • English (Ireland)
  • English (Italy)
  • English (Japan)
  • English (Latvia)
  • English (Lithuania)
  • English (Malaysia)
  • English (Netherlands)
  • English (New Zealand)
  • English (Nigeria)
  • English (Norway)
  • English (Pakistan)
  • English (Philippines)
  • English (Poland)
  • English (Portugal)
  • English (Puerto Rico)
  • English (Republika Srpska)
  • English (Romania)
  • English (Serbia)
  • English (Singapore)
  • English (Slovakia)
  • English (Slovenia)
  • English (South Africa)
  • English (Spain)
  • English (Sri Lanka)
  • English (Sweden)
  • English (Switzerland)
  • English (Trinidad & Tobago)
  • English (Uganda)
  • English (United Arab Emirates)
  • English (United Kingdom)
  • English (United States)
  • English (Zambia)

Erzya

  • Erzya

Esperanto

  • Esperanto

Estonian

  • Estonian
  • Estonian (Estonia)

Ewe

  • Ewe

Faroese

  • Faroese
  • Faroese (Faroe Islands)

Filipino

  • Filipino
  • Filipino (Philippines)

Finnish

  • Finnish
  • Finnish (Finland)

Franco-Provençal (Arpitan)

  • Franco-Provençal (Arpitan)

French

  • French
  • French (Belgium)
  • French (Cameroon)
  • French (Canada)
  • French (Côte d’Ivoire)
  • French (France)
  • French (Gabon)
  • French (Switzerland)

Friulian

  • Friulian

Fulah

  • Fulah
  • Fulah (Senegal)

Gaelic, Scottish

  • Gaelic, Scottish

Galician

  • Galician
  • Galician (Spain)

Ganda (Uganda)

  • Ganda (Uganda)

Garo

  • Garo

Georgian

  • Georgian
  • Georgian (Georgia)

German

  • German
  • German (Austria)
  • German (Germany)
  • German (Switzerland)

Greek

  • Greek
  • Greek (Cyprus)
  • Greek (Germany)
  • Greek (Greece)

Greenlandic

  • Greenlandic

Gronings

  • Gronings
  • Guarani (Paraguay)

Gujarati

  • Gujarati
  • Gujarati (India)

Gun

  • Gun

Haitian (Haitian Creole)

  • Haitian (Haitian Creole)
  • Haitian (Haitian Creole) (Haiti)

Hausa

  • Hausa

Hawaiian

  • Hawaiian

Hebrew

  • Hebrew
  • Hebrew (Israel)

Hindi

  • Hindi
  • Hindi (India)

Hungarian

  • Hungarian
  • Hungarian (Hungary)
  • Hungarian (Romanian)
  • Hungarian (Slovakia)

Hunsrik

  • Hunsrik

Icelandic

  • Icelandic
  • Icelandic (Iceland)

Ido

  • Ido

Igbo

  • Igbo

Iloko

  • Iloko

Indonesian

  • Indonesian
  • Indonesian (Indonesia)

Interlingua

  • Interlingua

Interlingue

  • Interlingue

Inuktitut

  • Inuktitut

Irish

  • Irish
  • Irish (Ireland)

Italian

  • Italian
  • Italian (Italy)
  • Italian (Switzerland)

Jamaican Patois

  • Jamaican Patois

Japanese

  • Japanese
  • Japanese (Hiragana)
  • Japanese (Japan)

Javanese

  • Javanese

K’iche’

  • K’iche’

Kabardian

  • Kabardian

Kabyle

  • Kabyle

Kadazandusun

  • Kadazandusun

Kannada

  • Kannada
  • Kannada (India)

Kaonde

  • Kaonde

Kapampangan

  • Kapampangan

Kaqchikel

  • Kaqchikel
  • Karachay-Balkar

Karelian

  • Karelian

Kashmiri

  • Kashmiri
  • Kashmiri (India)

Kashubian

  • Kashubian

Kazakh

  • Kazakh
  • Kazakh (Arabic)
  • Kazakh (Cyrillic)
  • Kazakh (Kazakhstan)
  • Kazakh (Latin)

Khasi

  • Khasi

Khmer

  • Khmer
  • Khmer (Cambodia)

Kikuyu

  • Kikuyu

Kinyarwanda

  • Kinyarwanda

Kirundi

  • Kirundi

Klingon

  • Klingon

Konkani

  • Konkani

Korean

  • Korean
  • Korean (Korea)

Kurdish

  • Kurdish
  • Kurdish (Iraq)

Kurmanji

  • Kurmanji

Kyrgyz

  • Kyrgyz
  • Kyrgyz (Arabic)

LOLCAT English

  • LOLCAT English

Ladino

  • Ladino

Lakota

  • Lakota

Lao

  • Lao
  • Lao (Laos)

Latgalian

  • Latgalian

Latin

  • Latin

Latvian

  • Latvian
  • Latvian (Latvia)

Lezghian

  • Lezghian

Libyan Arabic

  • Libyan Arabic

Ligurian

  • Ligurian

Limburgian

  • Limburgian

Lingala

  • Lingala

Lingua Franca Nova

  • Lingua Franca Nova

Lithuanian

  • Lithuanian
  • Lithuanian (Lithuania)

Lojban

  • Lojban

Lombard

  • Lombard

Low German

  • Low German

Lower Sorbian

  • Lower Sorbian
  • Lozi (Zambia)

Luganda

  • Luganda

Luvale

  • Luvale

Luxembourgish

  • Luxembourgish

Macedonian

  • Macedonian
  • Macedonian (Macedonia)

Maithili

  • Maithili

Malagasy

  • Malagasy

Malay

  • Malay
  • Malay (Arabic)
  • Malay (Brunei)
  • Malay (Malaysia)

Malayalam

  • Malayalam
  • Malayalam (India)

Maltese

  • Maltese
  • Maltese (Malta)

Manipuri

  • Manipuri

Maori

  • Maori

Mapudungun

  • Mapudungun

Marathi

  • Marathi
  • Marathi (India)

Marshallese

  • Marshallese

Meadow Mari

  • Meadow Mari

Minangkabau

  • Minangkabau

Mirandese

  • Mirandese

Mizo

  • Mizo

Mongolian

  • Mongolian
  • Mongolian (Mongolia)

Moroccan Arabic

  • Moroccan Arabic

Muscogee

  • Muscogee

N’ko

  • N’ko

Nahuatl

  • Nahuatl

Navajo

  • Navajo

Ndebele, North

  • Ndebele, North

Ndebele, South

  • Ndebele, South

Neapolitan

  • Neapolitan

Nepali

  • Nepali
  • Nepali (Nepal)

Nias

  • Nias

Nigerian Pidgin

  • Nigerian Pidgin

Northern Sami

  • Northern Sami

Northern Sotho

  • Northern Sotho

Norwegian

  • Norwegian
  • Norwegian (Norway)

Norwegian Bokmål

  • Norwegian Bokmål
  • Norwegian Bokmål (Norway)

Norwegian Nynorsk

  • Norwegian Nynorsk
  • Norwegian Nynorsk (Norway)

Nyanja

  • Nyanja

Occitan (post 1500)

  • Occitan (post 1500)

Odia

  • Odia
  • Odia (India)

Old English

  • Old English

Oromo

  • Oromo

Ossetic

  • Ossetic

Palatinate German

  • Palatinate German

Panjabi (Pakistan)

  • Panjabi (Pakistan)
  • Panjabi (Punjabi)
  • Panjabi (Punjabi) (India)

Papiamento

  • Papiamento

Pashto

  • Pashto
  • Pashto (Afghanistan)

Persian

  • Persian
  • Persian (Afghanistan)
  • Persian (Iran)

Piemontese

  • Piemontese

Pirate English

  • Pirate English

Polish

  • Polish
  • Polish (Poland)

Portuguese

  • Portuguese
  • Portuguese (Angola)
  • Portuguese (Brazil)
  • Portuguese (Mozambique)
  • Portuguese (Portugal)

Quechua

  • Quechua
  • Quechua (Ecuador)

Rapa Nui

  • Rapa Nui

Romanian

  • Romanian
  • Romanian (Moldova)
  • Romanian (Romania)

Romansh

  • Romansh

Russian

  • Russian
  • Russian (Estonia)
  • Russian (Latvia)
  • Russian (Lithuania)
  • Russian (Russia)
  • Russian (Ukraine)

Russian Petrine orthography

  • Russian Petrine orthography

Rusyn

  • Rusyn

Sakha (Yakut)

  • Sakha (Yakut)

Samburu

  • Samburu

Samoan

  • Samoan

Sango

  • Sango

Sanskrit

  • Sanskrit

Santali

  • Santali

Sardinian

  • Sardinian

Scots

  • Scots

Serbian

  • Serbian
  • Serbian (Cyrillic)
  • Serbian (Ijekavian Latin)
  • Serbian (Ijekavian)
  • Serbian (Latin)
  • Serbian (Latin) (Bosnia and Herzegovina)
  • Serbian (Latin) (Montenegro)
  • Serbian (Latin) (Serbia)
  • Serbian (Montenegro)
  • Serbian (Republika Srpska)
  • Serbian (Serbia)

Shona

  • Shona

Sicilian

  • Sicilian

Sidama

  • Sidama

Silesian

  • Silesian

Sindhi

  • Sindhi

Sinhala

  • Sinhala
  • Sinhala (Sri Lanka)

Slovak

  • Slovak
  • Slovak (Slovakia)

Slovenian

  • Slovenian
  • Slovenian (Slovenia)

Somali

  • Somali

Songhay

  • Songhay

Sotho, Southern

  • Sotho, Southern
  • Sotho, Southern (South Africa)

South Bolivian Quechua

  • South Bolivian Quechua

Southern Sami

  • Southern Sami

Spanish

  • Spanish
  • Spanish (Argentina)
  • Spanish (Bolivia)
  • Spanish (Chile)
  • Spanish (Colombia)
  • Spanish (Costa Rica)
  • Spanish (Cuba)
  • Spanish (Dominican Republic)
  • Spanish (Ecuador)
  • Spanish (El Salvador)
  • Spanish (Guatemala)
  • Spanish (Honduras)
  • Spanish (Latin America)
  • Spanish (Mexico)
  • Spanish (Nicaragua)
  • Spanish (Panama)
  • Spanish (Paraguay)
  • Spanish (Peru)
  • Spanish (Puerto Rico)
  • Spanish (Saudi Arabia)
  • Spanish (Spain)
  • Spanish (United States)
  • Spanish (Uruguay)
  • Spanish (Venezuela)

Standard Moroccan Tamazight

  • Standard Moroccan Tamazight

Sundanese

  • Sundanese

Swabian

  • Swabian

Swahili

  • Swahili
  • Swahili (Democratic Republic of the Congo)
  • Swahili (Kenya)
  • Swahili (Tanzania)
  • Swahili (Uganda)

Swati

  • Swati

Swedish

  • Swedish
  • Swedish (Finland)
  • Swedish (Sweden)

Swiss German

  • Swiss German

Tachawit

  • Tachawit

Tachelhit

  • Tachelhit

Tagalog

  • Tagalog
  • Tagalog (Philippines)

Tajik

  • Tajik
  • Tajik (Tajikistan)

Talossan

  • Talossan

Tamil

  • Tamil
  • Tamil (India)
  • Tamil (Sri-Lanka)

Tarifit

  • Tarifit

Tatar

  • Tatar

Telugu

  • Telugu
  • Telugu (India)

Tetum (Tetun)

  • Tetum (Tetun)

Thai

  • Thai
  • Thai (Thailand)

Tibetan

  • Tibetan
  • Tibetan (China)

Tigrinya

  • Tigrinya

Tlahuitoltepec Mixe

  • Tlahuitoltepec Mixe

Tok Pisin

  • Tok Pisin

Tongan

  • Tongan

Tsimshian

  • Tsimshian

Tsonga

  • Tsonga

Tswana

  • Tswana

Tumbuka

  • Tumbuka

Turkana

  • Turkana

Turkish

  • Turkish
  • Turkish (Cyprus)
  • Turkish (Germany)
  • Turkish (Turkey)

Turkmen

  • Turkmen
  • Turkmen (Turkmenistan)

Tweants

  • Tweants

Tz’utujil

  • Tz’utujil

Udmurt

  • Udmurt

Uighur (Arabic)

  • Uighur (Arabic)
  • Uighur (Cyrillic)
  • Uighur (Latin)

Ukrainian

  • Ukrainian
  • Ukrainian (Ukraine)

Upper Franconian

  • Upper Franconian

Upper Sorbian

  • Upper Sorbian

Urdu

  • Urdu
  • Urdu (Pakistan)

Uyghur

  • Uyghur

Uzbek

  • Uzbek
  • Uzbek (Arabic)
  • Uzbek (Cyrillic)
  • Uzbek (Latin)
  • Uzbek (Uzbekistan)

Venda

  • Venda

Venetian

  • Venetian

Vepsian

  • Vepsian

Vietnamese

  • Vietnamese
  • Vietnamese (Viet Nam)

Vlaams

  • Vlaams

Volapük

  • Volapük

Walloon

  • Walloon

Welsh

  • Welsh
  • Welsh (United Kingdom)

Western Frisian

  • Western Frisian
  • Western Frisian (Netherlands)

Wolof

  • Wolof
  • Wolof (Senegal)

Wáray-Wáray

  • Wáray-Wáray

Xhosa

  • Xhosa

Yeshivish English

  • Yeshivish English

Yiddish

  • Yiddish

Yoruba

  • Yoruba

Zaza

  • Zaza

Zulu

  • Zulu
  • Zulu (South Africa)