posts - 786,  articles - 8,  comments - 29

<2008年7月>
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

最新随笔

随笔分类

随笔档案

文章档案

相册

  •  

最新评论

阅读排行榜

1、复习纲要(六大模块)
2、指导书练习
3、补充练习
4、案例及策划专门复习
5、英语复习穿叉其中
6、劳动合同法、上海市工资支付办法、社保基数等
posted @ 2008-07-18 15:27 Shally编辑 收藏

 

中午一个同事请教我有关EXCLE自动计算年龄的方法,当时告诉她应该有一堆公式但是一时没有谁能记得清楚,答应他回来以后上网查查。
到网上一搜,大失所望。几乎没有一种方法是精确的。
网上搜到的共式大概有这么几种:
1、计算出生日期到某一指定日期(一般选用某年的最后一天入2006年12月31日)的的天数,然后除以360 ,得到一个数值,然后用 int()函数取整,得出需要的年龄。一般使用的公式如下:
=IF(C12="","",INT(DAYS360(C12,"2006-12-31")/360))
聪明一点的人知道使用这个公式,
=IF(C12="","",INT(DAYS360(C12,TODAY())/360))
这个方法,这个公式的弊端在于,一、将每个月默认为30天去计算两个日期之间的天数,二、将每年默认为360天去计算年龄。这种方法显然不精确。
2、年份直接相减
计算周岁
=YEAR(NOW())-YEAR(C12)
计算虚岁
=YEAR(NOW())-YEAR(C12)+1
这种算法的精确程度显而易见,粗略估算还算可以。
3、使用DATEDIF函数
这种方法与第一种方法采用了相同的思路,但是其的精确程度显然比第一种方法要高,这取决于DATEDIF函数本身的精确性。
=IF(C12="","",INT(DATEDIF(C12,"1983-3-20","D")/365))
或者,
=IF(C12="","",INT(DATEDIF(C12,now(),"D")/365))
但是这种方法强行将一年固定为365天,我们知道通常情况每个四年就有一年是366天所以这种算法也不是很精确。
通过认真分析,我觉得只有结合我们计算年龄的实际方法,才能编制出准确无误的公式。
首先分析人们计算年龄的方法。
例如笔者系1983年3月20日生人,如果要在2007年3月23日这天计算他的年龄,通常采用这样的方法。
首先,人们会用2007减去1983得出的年龄为24岁,然后再看看他“满没满”24岁,就是看看出生的月份和日期比今天早还是晚,如果出生日期晚于今天则表示没有满,那么他的年龄就应该是2007-1983-1=23岁。如果出生日期早于今天或者就是今天,就说明他已经满了24岁或者正好满24岁,则他的年龄就是2007-1983=24岁。
分析清楚了计算年龄的过程我们再根据这个过程编写公式就很容易了。
综上,我编写了如下公式,在实际应用中将公式中所有的C12替换为,你的所使用的出生日期所在的表格行号列号组合即可。如(A1,B2等等)
=IF(MONTH(NOW())<MONTH(C12),INT(YEAR(NOW())-YEAR(C12))-1,IF(MONTH(NOW())>MONTH(C12),YEAR(NOW())-YEAR(C12),IF(DAY(NOW())>=DAY(C12),YEAR(NOW())-YEAR(C12),YEAR(NOW())-YEAR(C12)-1)))
 
公式说明
=IF ( MONTH(NOW())<MONTH(C12) , INT(YEAR(NOW())-YEAR(C12))-1 ,
//如果当前日期的月份小于所需计算日期的月份,则表示今年没有过生日,年龄数为YEAR(NOW())-YEAR(C12)-1,如果不是这种情况,进入下面的判断
IF ( MONTH (NOW())>MONTH(C12) , YEAR(NOW())-YEAR(C12) ,
//如果当前日期的月份大于所需计算日期的月份,则表示今年已经过生日,年龄数为YEAR(NOW())-YEAR(C12),如果也不是这种情况,则表示这两个月份相等,进入下面的判断过程去判断,日期的情况。
IF ( DAY(NOW())>=DAY(C12) , YEAR(NOW())-YEAR(C12) ,
//如果今天的日期大于或者等于出生年月日中的日期,则表示已经过了生日,或者正在过生日,则年龄数为YEAR(NOW())-YEAR(C12),否则表示今年没有过生日,年龄数应该选择下面的公式,大小为YEAR(NOW())-YEAR(C12)-1
YEAR(NOW())-YEAR(C12)-1 )
)
 
参考知识:
 

IF

请参阅

执行真假值判断,根据逻辑计算的真假值,返回不同结果。

可以使用函数 IF 对数值和公式进行条件检测。

语法

IF(logical_test,value_if_true,value_if_false)

Logical_test    表示计算结果为 TRUE 或 FALSE 的任意值或表达式。例如,A10=100 就是一个逻辑表达式,如果单元格 A10 中的值等于 100,表达式即为 TRUE,否则为 FALSE。本参数可使用任何比较运算符

Value_if_true    logical_test 为 TRUE 时返回的值。例如,如果本参数为文本字符串“预算内”而且 logical_test 参数值为 TRUE,则 IF 函数将显示文本“预算内”。如果 logical_test 为 TRUE 而 value_if_true 为空,则本参数返回 0(零)。如果要显示 TRUE,则请为本参数使用逻辑值 TRUE。Value_if_true 也可以是其他公式。

Value_if_false    logical_test 为 FALSE 时返回的值。例如,如果本参数为文本字符串“超出预算”而且 logical_test 参数值为 FALSE,则 IF 函数将显示文本“超出预算”。如果 logical_test 为 FALSE 且忽略了 Value_if_false(即 value_if_true 后没有逗号),则会返回逻辑值 FALSE。如果 logical_test 为 FALSE 且 Value_if_false 为空(即 value_if_true 后有逗号,并紧跟着右括号),则本参数返回 0(零)。Value_if_false 也可以是其他公式。

说明

  • 函数 IF 可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。请参阅下面最后一个示例。
  • 在计算参数 value_if_true 和 value_if_false 后,函数 IF 返回相应语句执行后的返回值。
  • 如果函数 IF 的参数包含数组,则在执行 IF 语句时,数组中的每一个元素都将计算。
  • Microsoft Excel 还提供了其他一些函数,可依据条件来分析数据。例如,如果要计算单元格区域中某个文本字符串或数字出现的次数,则可使用 COUNTIF 工作表函数。如果要根据单元格区域中的某一文本字符串或数字求和,则可使用 SUMIF 工作表函数。请了解关于根据条件计算值

DATEDIF  
  参阅: 计算两个日期之间的天数、月数或年数。提供此函数是为了与   Lotus   1-2-3   兼容。  
   
  语法: DATEDIF(start_date,end_date,unit)  
   
  Start_date       为一个日期,它代表时间段内的第一个日期或起始日期。日期有多种输入方法:带引号的文本串(例如   "2001/1/30")、系列数(例如,如果使用   1900   日期系统则   36921   代表   2001   年   1   月   30   日)或其他公式或函数的结果(例如,DATEVALUE("2001/1/30"))。有关日期系列数的详细信息,请参阅   NOW。  
   
  End_date       为一个日期,它代表时间段内的最后一个日期或结束日期。  
   
  Unit       为所需信息的返回类型。  
   
  Unit   返回    
  "Y"   时间段中的整年数。    
  "M"   时间段中的整月数。    
  "D"   时间段中的天数。    
  "MD"   start_date   与   end_date   日期中天数的差。忽略日期中的月和年。    
  "YM"   start_date   与   end_date   日期中月数的差。忽略日期中的日和年。    
  "YD"   start_date   与   end_date   日期中天数的差。忽略日期中的年。     

  说明:
  Microsoft   Excel   按顺序的系列数保存日期,这样就可以对其进行计算。如果工作簿使用   1900   日期系统,则   Excel   会将   1900   年   1   月   1   日保存为系列数   1。而如果工作簿使用   1904   日期系统,则   Excel   会将   1904   年   1   月   1   日保存为系列数   0,(而将   1904   年   1   月   2   日保存为系列数   1)。例如,在   1900   日期系统中   Excel   将   1998   年   1   月   1   日保存为系列数   35796,因为该日期距离   1900   年   1   月   1   日为   35795   天。请查阅   Microsoft   Excel   如何存储日期和时间。   
  Excel   for   Windows   和   Excel   for   Macintosh   使用不同的默认日期系统。有关详细信息,请参阅   NOW。    
  示例:
  DATEDIF("2001/1/1","2003/1/1","Y")   等于   2,即时间段中有两个整年。   
  DATEDIF("2001/6/1","2002/8/15","D")   等于   440,即在   2001   年   6   月   1   日和   2002   年   8   月   15   日之间有   440   天。   
  DATEDIF("2001/6/1","2002/8/15","YD")   等于   75,即在   6   月   1   日与   8   月   15   日之间有   75   天,忽略日期中的年。   
posted @ 2008-07-09 16:30 Shally编辑 收藏
posted @ 2007-11-03 12:42 Shally 阅读(17) | 评论 (0)编辑 收藏

跟读版

自由发挥版

 

The House on Mango Street (Excerpt 2)
芒果街上的小屋 (节选2)

    But the house on Mango Street is not the way they told it at all. It's small and red with tight steps in front and windows so small you'd think they were holding their breath. Bricks are crumbling in places, and the front door is so swollen you have to push hard to get in. There is no front yard, only four little elms the city planted by the curb. Out back is a small garage for the car we don't own yet and a small yard that looks smaller between the two buildings on either side. There are stairs in our house, but they're ordinary hallway stairs, and the house has only one washroom. Everybody has to share a bedroom-Mama and Papa, Carlos and Kiki, me and Nenny.
    Once when we were living on Loomis, a nun from my school passed by and saw me playing out front. The laundromat downstairs had been boarded up because it had been robbed two days before and the owner had painted on the wood YES WE'RE OPEN so as not to lose business.
    "Where do you live?" She asked.
    "There," I said pointing up to the third floor.
    "You live there?"
    There. I had to look to where she pointed-the third floor, the paint peeling, wooden bars Papa had nailed on the windows so we wouldn't fall out. "You live there?" The way she said it made me feel like nothing. "There, I lived there." I nodded.
    I knew then I had to have a house. A real house. One I could point to. This isn't it. The house on Mango street isn't it. "For the time being," Mama says. "Temporary," Says Papa. But I know how those things go.

    可是芒果街上的小屋全然不是他们讲的那样。它很小,是红色的,门前一方窄台阶,窗户小得让你觉得它们像是在屏住呼吸。几处墙砖蚀成了粉。(受潮的)前门那么鼓,你要用力推才进得来。这里没有前院,只有四棵市政栽在路边的小榆树。屋后有个小车库,是用来停我们还没买下的那辆小汽车的,还有个小院子,夹在两边的楼中间,越发显得小了。我们的房子里有楼梯,可那只是普通的门厅台阶,而且房子里只有一个卫生间。每个人都要和别人合用一间卧房--妈妈和爸爸,卡洛斯和奇奇,我和内妮。
    我们住在鲁米斯时,有一回学校的修女经过那里,看到我在屋前玩。楼下的自助洗衣店被木板封了起来,因为两天前刚被洗劫过。为了不走掉生意,店主在木板上涂写了几个字:“是的,我们在营业”。
    “你住在哪里呀?”她问。
    “那里。”我说,指了指三楼。
    “你住在哪里?”
    那里。我不得不朝她指的地方看去--三楼,那里墙皮斑驳,窗上横着几根木条,是爸爸钉上去的,那样我们就不会掉下来。“你住在那里?”她说话的样子让我觉得自己什么都不是。“那里。我住在那里”我点头。
    于是我明白,我得有一所房子。一所真正的大屋。一所可以指给别人看的房子。可这里不是。芒果街上的小屋不是。“眼下先这样,”妈妈说。“这是暂时的,”爸爸说。可是我知道事情是怎样的。

posted @ 2007-08-27 20:03 Shally 阅读(63) | 评论 (0)编辑 收藏

The House on Mango Street (Excerpt 1)

芒果街上的小屋 (摘选1)

By the time we got to Mango Street we were six-Mama, Papa, Carlos, Kiki, my sister Nenny and me.

The house on Mango Street is ours, and we don't have to pay rent to anybody, or share the yard with the people downstairs, or be careful not to make too much noise, and there isn't a landlord banging on the ceiling with a broom. But even so, it's not the house we'd thought we get.

We had to leave the flat on Loomis quick. The water pipes broke and the landlord wouldn't fix them because the house was too old. We had to leave fast. We were using the washroom next door and carrying water over in empty milk gallons. That's why Mama and Papa looked for a house, and that's why we moved into the house on Mango Street, far away, on the other side of town.

They always told us that one day we would move into a house, a real house that would be ours for always so we wouldn't have to move each year. And our house would have running water and pipes that worked, and inside it would have real stairs, not hallway stairs, but stairs inside like the houses on TV. And we'd have a basement and at least three washrooms so when we took a bath we wouldn't have to tell everybody. Our house would be white with trees around it, a great big yard and grass growing without a fence. This was the house Papa talked about when he held up a lottery ticket and this was the house Mama dreamed up in the stories she told us before we went to bed.

搬到芒果街时,我们家有六个人了——妈妈,爸爸,卡洛斯,奇奇,妹妹内妮和我。

芒果街上的小屋是我们的,我们不用交房租给任何人,或者和楼下的人合用一个院子,或者小心翼翼别弄出太多的声响,这里也没有拿扫把猛敲天花板的房东。可就算这样,它也不是我们原以为自己可以得到的那样的房子。

我们得赶紧搬出鲁米斯的公寓。水管破了,房东不愿来修理,因为房子太老。我们得快快离开。我们借用着邻居的卫生间,用空的牛奶壶取水。这就是为什么爸妈找房子,然后我们搬进了芒果街的小屋,远远地,从城市的另一边。

他们一直对我们说,有一天,我们会搬进一所房子,一所真正的大屋,永远属于我们,那样我们就不用每年搬家了。我们的房子会有自来水和运作正常的水管。里面还有真正的楼梯,不是门厅台阶,而是像电视上的房子里那样的楼梯。我们会有一个地下室和至少三个卫生间,那样洗澡的时候就不用告诉每个人。我们的房子会是白色的,四周有树木,还有一个很大的院子,草儿生长着,没有篱笆把它们圈起来。这是爸爸手握彩票时提到的房子,这是妈妈在给我们的睡前故事里幻想着的房子。

posted @ 2007-08-17 16:30 Shally 阅读(61) | 评论 (0)编辑 收藏

I am sorry



I always think that I know everything
But you insist I know nothing
I always fight to have very last word
But your thoughts were never heard

I′m sorry, so sorry
Didn′t mean to hurt you
I′ll borrow your sorrow
Don′t tell me it′s through

I wish I could take back some things I said
I wish I could make things better
I wish I could take back some things I did
Everyday I′m feeling sadder

I′m sorry, so sorry
Didn′t mean to hurt you
I′ll borrow your sorrow
It′s my turn now to cry

Never fear I′ll be here
Say a prayer
We can take on the hands of time
And I, I know we can make it
If we just stay together
And trust each other on this one

I′m sorry, so sorry
Didn′t mean to hurt you
I′ll borrow your sorrow
Don′t tell me it′s through, don′t tell me it′s through
Said I′m sorry, I′m so sorry
I′ve never meant to, never meant to hurt you
I′ll borrow, I will borrow your sorrow
It′s my turn now to cry

She


She hangs out every day nearby the beach
Havin' a Heineken fallin' asleep
She looks so sexy when she's walking the sand
Nobody ever put a ring on her hand

Swim to the oceanshore fish in the sea
She is the story the story is she
She sings to the moon and the stars in the sky
Shining from high above you shouldn't ask why

She is the one that you never forget
She is the heaven-sent angel you met
Oh, she must be the reason why God made a girl
She is so pretty all over the world

She puts the rhythm, the beat in the drum
She comes in the morning, in the evening she's gone
Every little hour every second you live
Trust in eternity that's what she gives

She looks like Marilyn, walks likeSuzanne
She talks like Monica and Marianne
She wins in everything that she might do
And she will respect you forever just you

She is the one that you never forget
She is the heaven-sent angel you met
Oh, she must be the reason why God made a girl
She is so pretty all over the world

She is so pretty all over the world

She is so pretty
She is like you and me
Like them like we
She is in you and me

She is the one that you never forget
She is the heaven-sent angel you met
Oh, she must be the reason why God made a girl
She is so pretty all over the world

(She is the one) She is the one
(That you never forget) That you never forget
She is the heaven-sent angel you met
She's the reason (oh she must be the reason) why God made a girl
She is so pretty all over the world (oh...)

Na na na na na ….

posted @ 2007-07-23 19:34 Shally 阅读(96) | 评论 (0)编辑 收藏



【材料文本】
Love            [ 节选自《生而为赢》— What I have lived for?]

 

I have sought love, first, because it brings ecstasy---ecstasy so great that I would often have sacrificed all the rest of my life for a few hours for this joy. I have sought it, next, because it relieves loneliness---that terrible loneliness in which one shivering consciousness looks over the rim of the world into the cold unfathomable lifeless abyss. I have sought it, finally, because in the union of love I have seen, in a mystic miniature, the prefiguring vision of the heaven that saints and poets have imagined. This is what I sought, and though it might seem too good for human life, this is what---at last---I have found.

 

Ecstasy [ 5ekstEsi ] n. 入迷

Consciousness [ 5kCnFEsnis ] n.

Unfathomable  [ Qn5fATEmEbl ] adj. 深不可测的 , 无底的 , 难解的

Abyss [ E5bis ] n. 深渊

Miniature  [ 5minjEtFE ] n. 缩小的模型 , 缩图 , 缩影 adj. 微型的 , 缩小的

Prefigure [ 5pri:5fi^E ] v. 预示

我寻求爱,首先因为它使我心为之着迷,这种难以名状的美妙迷醉使我愿意用所有的余生去换取哪怕几个小时这样的幸福。我寻求爱,还因为它能缓解我心理上的孤独中,我感觉心灵的战栗,仿如站在世界的边缘而面前是冰冷,无底的死亡深渊。我寻求爱,因为在我所目睹的结合中,我仿佛看到了圣贤与诗人们所向往的天堂之景。这就是我所寻找的,虽然对人的一生而言似乎有些遥不可及,但至少是我用尽一生所领悟到的。

posted @ 2007-03-24 14:11 Shally 阅读(173) | 评论 (0)编辑 收藏


【材料文本】  Control your Life  [节选自《生而为赢》- What is your recovery rate?
]

Remember: Rome wasn’t built in a day. Reflect on your recovery rate each day. Every day before you go to bed, look at your progress. Don’t lie in bed saying to you, “I did that wrong.” “I should have done better there.” No. look at your day and note when you made an effort to place a full stop after an incident. This is a success. You are taking control of your life.

记住,罗马不是一日建成的。每天都反思自己的恢复速率;每天上床睡觉前,都看看自己的进步;不要躺在床上对自己说:“我那个做错了。”“我应该做到更好。”不要那样做.

回想自己的一天,并注意努力给某个事件画上句号的时刻。这就是一个成功,你在控制自己的生活。

posted @ 2007-03-11 11:35 Shally 阅读(194) | 评论 (0)编辑 收藏



【材料文本】  You are an Actor        [节选自《生而为赢》- What is your recovery rate?]

Imagine yourself to be an actor in a play on the stage. Your aim is to play your part to the best of your ability. You have been given a script and at the end of each sentence is a ful stop.
Each time you get to the end of the sentence you start a new one and although the next sentence is related to the last it is not affected by it. Your job is to deliver each sentence to the best of your ability.
想象自己是一位站在舞台上的戏剧家。你的目标是尽全力扮演好你的角色。你已经拿到了剧本,而剧本中的每句话都以句号结尾。
每次你念到一个句子的末尾,你就会开始一个新的句子。尽管下一句和上一句有关联,但并不受它的影响。你的工作是尽力说好每句台词。

posted @ 2007-03-06 18:34 Shally 阅读(157) | 评论 (0)编辑 收藏


【材料文本】
    Youth              [节选自《生而为赢》-Youth ]

 

Youth means a temperamental predominance of courage over timidity, of the appetite for adventure over the love of ease. This often exists in a man of 60 more than a boy of 20. Nobody grows old merely by a number of years. We grow old by deserting our ideals.

 

Years may wrinkle the skin, but to give up enthusiasm wrinkles the soul. Worry, fear, self-distrust bows the heart and turns the spirit back to dust.

Temperamental [7tempErE5mentl] adj.由气质引起的, 心情变化快的, 喜怒无常的

Predominance [prI`dCmInEns] n.优势

Enthusiasm [in5Wju:ziAzEm] n.狂热, 热心, 积极性, 激发热情的事物

 

 

青春气贯长虹,勇锐盖过怯弱,进取压倒苟安。如此锐气,二十后生而有之,六旬男子则更多见。年岁有加,并非垂老,理想丢弃,方堕暮年。 
岁月悠悠,衰微只及肌肤;热忱抛却,颓废必致灵魂。忧烦,惶恐,丧失自信,定使心灵扭曲,意气如灰。

posted @ 2007-02-04 11:24 Shally 阅读(228) | 评论 (0)编辑 收藏